It's VLOOKUP Week, apparently. So let's get to it.
Ways to Improve VLOOKUP
Improve Lookup_value
I always recommend a cell reference over hardcoding. Put your lookup value into a cell and reference it in this argument. This way, when you need to change the value, you just change the cell rather than having to edit the formula and risk breaking it. Also, this allows you to fill the formula down or across easily.
Your cell reference can be textually manipulated using any available text function. For example, if your cell reference has hidden spaces, instead of spending time removing those spaces you could write
Absolute References
Using absolute references sparingly can improve your turnaround time. A lot of times I find myself looking up values across several columns, using the value in a single column for each VLOOKUP formula. For example, I have a worksheet with names, DOB and SSN. On another worksheet, I have a list of names only and want to return DOB and SSN for each name. For each name, I'll need two VLOOKUP formulas to return each value.
When filling VLOOKUP formulas to the right, use an absolute column reference to stop the formula from adjusting the lookup value. Ex:
=VLOOKUP($A1, Sheet1!$A$1:$G$100, 2, False)When I fill this formula to the right, it will always refer to my lookup value for the current row, which is A1. However, the row will change when I fill the formula down, because I want VLOOKUP to use the value in the current row (whatever that row is).
Improve Table_array
In Table array hardcoding I demonstrated a method for using actual values as your source table. I still haven't figured out how this technique might be useful, but perhaps for a single VLOOKUP formula with a small range of values, it might work. Otherwise I recommend a range (sorted) with lookup values in the leftmost column.
Furthermore, I recommend you use absolute column and row references for the lookup table. This is because in most cases the lookup table doesn't move.
=VLOOKUP($A1, $H$1:$M$100,Improve Col_index_num
I find that most people hardcode this value, but did you know you can also use a function like COLUMN to create a value that changes when you fill VLOOKUP formulas to the right?
In my example above, I wanted to use two VLOOKUP formulas to return DOB and SSN. I know that DOB is the second column, and SSN is the third column, so my first VLOOKUP formula will look like this:
=VLOOKUP($A1, Sheet1!$A$1:$C$200, 2, False)and my second VLOOKUP formula will look like this:
=VLOOKUP($A1, Sheet1!$A$1:$C$200, 3, False)But instead of using "2" in the first formula, I could use COLUMN(B1) as the third argument. COLUMN(B1) returns 2, and when I fill the formula to the right, it will auto-adjust the relative cell reference. Now I can fill the formula to the right without having to manually adjust the third argument.
=VLOOKUP($A1, Sheet1!$A$1:$C$200, COLUMN(B1), False)This formula can be filled down and to the right without needing to be updated at all. It will always refer to the lookup value in column A of the current row and automatically adjust the return column depending on what column it is in.
Improve Range_lookup
In Excel, 1 is
true
and 0 is
false
. Instead of using
true
or
false
, use 1 or 0 to shorten the formula. Even better, use a cell reference which can be referred to from multiple cells and changed easier than editing every single VLOOKUP formula. If your VLOOKUP is dependent on a formula or other calculation, you can also use a formula to return either 1 or 0 depending on the type of lookup you want.
=VLOOKUP($A1, Sheet1!$A$1:$C$200, COLUMN(B1), $A2)Or just don't use it
VLOOKUP assumes that your lookup table is organized with the lookup values found in the leftmost column. This is optimistic. The lookup formula then retrieves the value from the matching row in the table in the specified column. You can only "go right" — if your data is organized differently, you either need to (temporarily) reorganize it, or use a different function.
Instead of VLOOKUP, I use INDEX/MATCH formulas to do lookups. It is more flexible because I can look up values anywhere on the sheet. It is not dependent on my data being in a specific layout (other than maybe sorted, which it usually is anyway).
Use COUNTIF or MATCH to speed up calculation
As many others have pointed out, VLOOKUP returns
#N/A
if the lookup value is not found. Instead of using a double VLOOKUP formula use COUNTIF to see how many times your lookup value appears in the list, or MATCH to see if a lookup returns an error. Only do the VLOOKUP when necessary (if COUNTIF > 1 or NOT(ISNA(MATCH))=true).
About JP
Follow Me