Do you still get your music from Napster? How many friends do you chat with on AIM these days? You’ve moved on to sneakier ways of pirating music and chats with your friends that disappear. So why are you still writing VLOOKUPs?
I know those analogies are unfair. Downloading music from Napster was illegal and AIM is only beneficial to you if your friends are using it. Neither of these conditions preclude you from writing VLOOKUPs. And while society wi
Here’s why you should stop writing VLOOKUPs: they take up way too much memory. This isn’t an issue if you only have a few VLOOKUPs in a workbook or if your tables are small. But every Excel user eventually creates that monstrosity, multiple worksheets with thousands of rows of data in each. When you start writing VLOOKUPs in this bad boy, you’re workbook will start to slow way down.
This happens because of the structure of a VLOOKUP. The second argument, your lookup range, is the memory killer. You probably place your row key/primary key in the leftmost column of your table and use VLOOKUPs to access the the data in the numerous columns across the table. If the data you want resides in the 20th column and your table is huge, you are selecting that entire massive block into memory with your formula. There is a better way.
The second argument, the lookup range, is superfluous. You only really need the column that contains your lookup value and the column that contains the data you want to return. What if we could just use those two? We’d save ourselves the numerous columns that might exist between them.
As you may have guessed, you can just use those two columns. Interestingly, you will need to use two formulas: INDEX & MATCH. INDEX will take as it’s first argument the column that contains the data you want to return. It’s second argument will be the MATCH formula, which will take the column containing your lookup value, and your lookup value itself. The output of the MATCH formula will be an index position of your lookup value within the lookup column (e.g., row 36 or whatever). When this feeds into the INDEX formula, it will tell INDEX to retrieve the row we just found with MATCH in the column we specified in INDEX. Voila
If this sounds confusing, it might be because the INDEX(MATCH()) approach is a reverse VLOOKUP in terms of order.
VLOOKUP(Lookup Value, Lookup Range, Column I want, FALSE)
INDEX(Column I want, MATCH(Lookup Value, Lookup Range, FALSE))
Although you are using two formulas, the fact that you aren’t reading a massive range into memory will help save you the dreaded Excel slowness that seems to corrupt even the most thoughtful workbook carpenters.