VLOOKUP formula with a combination of
MATCH. There are only upsides to it – apart from the minor learning effort ;-)!
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.
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.
VLOOKUP has some severe limitations, mainly:
- 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)
- 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)
- 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
VLOOKUP is actually doing a two step process in one formula:
- It finds the key (first parameter) in the first column of the table provided as the second parameter
- 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).
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
In the example, the formula to retrieve the price for apples is
To do the “finding” part, use the MATCH function:
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
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
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
This addresses all three issues above quite nicely:
- Inefficiency: The searching part is where Excel is doing the heavy-lifting in the
MATCHby itself will take just as long in the first place. However, if you place it in a separate cell and refer all the
INDEXformulas 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!
- Restricted to the right: Nothing to stop you from applying the
INDEXformula 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$1contains the result from the
- Error-prone: as
INDEXboth refer to single columns only, the formulas will still work, when other columns are deleted or inserted!
So all in all, there’s simply no reason to use
VLOOKUP instead of
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.)
Trackback from your site.