# Use INDEX and MATCH instead of VLOOKUP

## 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:

However, `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 `INDEX`

and `MATCH`

!

`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).

`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`

:

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:

**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!**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)**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.)

Trackback from your site.