How VLOOKUP can get you in trouble and how to solve it

An updated version of this post has been published here: http://easy-excel.com/?p=683

 

An Extra Column Means Trouble

If you want to find a value in a table in Excel, a simple VLOOKUP function is usually a good and easy way to do it. But you have to be careful – if you insert a new column in your table, the function might not work anymore, and we have to find another approach. Here’s why:

In the table below we have the Weekdays in Column A, Number of Units in Column B and Total Sales in Column C. To find the Total Sales for a specific weekday, we can use the VLOOKUP function:

=VLOOKUP(B10,A2:C8,3,0)

Explanation: The formula looks up the value in B10 (“Thursday”) in the array A2:C8 and returns the value in the 3rd column (column index number). The zero in the end of the formula means that we want an exact match.

P23_1_VLOOKUP gets you in trouble
But what happens if we decide to insert another column between Units and Total Sales? The array reference will indeed update to A2:D8, but the column index number will still be 3. The formula will return the value in the new column, which is Zero!

 P23_2_VLOOKUP gets you in trouble

Dynamic column reference

We need to make the column reference dynamic, so that it changes whenever we insert a new column in the table. Let’s remove the extra column again and replace the hard-coded 3 with a MATCH function:

MATCH(A11,A1:C1,0)

Explanation: The formula looks up the value in A11 (“Total Sales”) in the array A1:C1 and returns the position of it. “Total Sales” is in the 3rd cell of this array (on the first picture). The zero in the end of the formula means that we want an exact match.

Let’s amend the VLOOKUP formula (assuming we have not yet inserted the new column):

Old formula: =VLOOKUP(B10,A2:C8,3,0)

New formula: =VLOOKUP(B10,A2:C8,MATCH(A11;A1:C1;0),0)

Does it work?

Let’s try to insert the new column again:

P23_3_VLOOKUP gets you in trouble
The value in B11 has not changed – it works!

INDEX & MATCH

Another approach to this problem would be an INDEX & MATCH combo.

=INDEX(B2:C8,MATCH(B10,A2:A8,0),MATCH(A11,B1:C1,0))

MATCH returns the relative positions, and INDEX returns the value in that cell intersection.

Some people prefer the INDEX & MATCH approach, but if you are already familiar with the VLOOKUP function, the VLOOKUP & MATCH approach described above is probably easier.

Note: If the lookup range is to the left of the lookup values, you must use the INDEX function. More on that in a future post!

What do you think? Do you prefer VLOOKUP or INDEX & MATCH? Feel free to comment below!

Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s