Improve VLOOKUP

vlookup image

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

=VLOOKUP(TRIM(A1),

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

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel