Use INDEX and MATCH instead of VLOOKUP

Written by Peter Albert on . Posted in Advanced user, Data handling

Executive summary

Replace your VLOOKUP formula with a combination of INDEX/MATCH. There are only upsides to it – apart from the minor learning effort ;-)!

Quick reference

Replace

=VLOOKUP(YourKey,YourTable,ColumnNumberToReturn,0)

with

=INDEX(ColumnToReturn,MATCH(YourKey,ColumnWithKey,0))

E.g. replace =VLOOKUP(G1,$B$2:$D$5,2,0) with. =INDEX($D$2:$D$5,MATCH(G1,$B2$2:$B$5,0)).

If you need to retrieve multiple columns for one key, extract the MATCH part of the above function to another cell and refer all INDEX functions to this cell.

Detailed description

VLOOKUP is quite a powerful function – and probably one of the first functions you’ll learn from your team members when you build a larger model. It’s a neat function when you need to combine multiple data set.

Typical VLOOKUP usage: Typical VLOOKUP example

However, VLOOKUP has some severe limitations, mainly:

  1. Inefficiency: It’s inefficient if you want to retrieve multiple fields/column for the same key – in the example above, retrieving the quantity needs to search for Pears again)
  2. Restricted to the right: You’ll always need the key to be the leftmost column (in order to get the product type, you need to rearrange the columns)
  3. Error-prone: As the target column is hard-coded (“retrieve the 5th column from the key column”), you’ll run into issues when you insert/delete a column

Instead, consider using INDEX and MATCH!

VLOOKUP is actually doing a two step process in one formula:

  1. It finds the key (first parameter) in the first column of the table provided as the second parameter
  2. Once found, it returns the Nth column (third parameter) from this table in the row that was just found. (I’ll describe the mechanics – and huge optimization potential – of the fourth parameter in another post).

INDEX and MATCH do the same thing – only to split it: MATCH finds the key in a range you provide (i.e. in the first column) – and INDEX returns you the Nth value from a column (or row/table if you want).

Above example with INDEX/MATCH: Above example with INDEX/MATCH

In the example, the formula to retrieve the price for apples is

=VLOOKUP(G1,$B$2:$D$5,2,0). 

To do the “finding” part, use the MATCH function:

=MATCH(G1,$B$2:$B$5,0)

This will return 1, i.e. it finds a match in the first element. The MATCH function looks very similar to the VLOOKUP function – the main difference is that you provide it only with a column to search for (not the whole table!) and you leave out the original third parameter (column number). Important though: as with VLOOKUP you need to provide 0 (or FALSE for those who like to type more ;-) ) as the last parameter! Else, your result will most likely be wrong!

The second part of the VLOOKUP – return the value of another column in the same row – is done with the very simple formula

=INDEX($D$2:$D$5,X)

X means: the Xth element of $D2:$D$5 is to be returned. I.e. the X can either be the full MATCH formula from above. Or even better, leave the MATCH formula in a separate cell, as simply have a reference to this cell instead of the X.

This addresses all three issues above quite nicely:

  1. Inefficiency:  The searching part is where Excel is doing the heavy-lifting in the VLOOKUP. MATCH by itself will take just as long in the first place. However, if you place it in a separate cell and refer all the INDEX formulas to this cell, the heavy-lifting only has to be done once for each key – and not for every field/column you want to retrieve for a key!
  2. Restricted to the right: Nothing to stop you from applying the INDEX formula to a column left of the key column! In the above example, =INDEX($A$2:$A$5,$J$1) will return you the fruit type (assuming the $J$1 contains the result from the MATCH formula)
  3. Error-prone:  as MATCH and INDEX both refer to single columns only, the formulas will still work, when other columns are deleted or inserted!

Summary

So all in all, there’s simply no reason to use VLOOKUP instead of INDEX/MATCH! And once you’ll get the hang of the new function, you’ll find that you can much more things with them (e.g. replace HLOOKUP, use only one INDEX formula for a whole table, dynamically sort tables, etc.)

Tags: , ,

Trackback from your site.

Peter Albert

Worked 5 years as Consultant and Project Manager at McKinsey & Company and Bain & Company. Now builds Excel, other models and web applications for consultancies and other clients - and provides Excel training to consultants at all skill levels.

Leave a comment