How to Use Index and Match to Replace Vlookup

Check out the video below ​to learn how to ​use Match and Index to replace Vlookup in Excel.


Video summary

We’re going to look at replacing Vlookup with the Index and Match functions.

Before we do that though, it is important to note why we would. What is wrong with Vlookup that it needs replacing?

The short answer is that Vlookup ​cannot ​look to the left. Let us explain: ​suppose we look up a value with Vlookup. This value is in column 1. We can then only return a value that is to the right, in column 2, 3, 4, etc. It’s not possible to return a value from column 0, -1, -2 or any other column to the left of the column of the lookup value.

But you can do this with Index and Match (check out their syntax below).

Match can do the searching. It searches a cell range and returns the row of the value it found.

Index can do the returning of the value. ​It returns a value from a cell range given a row number.

​So Match can look up a value in one cell range, get the row index and pass it to the Index function. The Index function can look at a different cell range (with values that we want to return) and return the value at that specific row.

That would look something like this:

=INDEX(​Return cell range​, MATCH(​​Lookup cell range​, 0))

The big upside to this is that the ​Return cell range​ can be anywhere you want. ​Including to the left of the ​Lookup cell range​.

​​Syntax of Index

=INDEX(Array,Row number, [Column number], [Area number])

Argument Argument description
1Array One or more arrays or cell ranges.
2Row numberThe row of the value we want to return.
3Column number(Optional)The column of the value we want to return.
4Area number(Optional)The array index of the value we want to return.Only useful when you have more than 1 array as the first argument.

More information about the Index function here.

Syntax of Match

=MATCH(Lookup value,Lookup array, [Match type])

Argument Argument description
1Lookup valueThe value to search for.
2Lookup arrayAn array or cell range to search in.
3Match type(optional)Either -1, 0 or 1.1, smaller than (default): finds values equal to or smaller than the Lookup value.0, exact match: only finds the exact Lookup value given.-1, greater than: finds values equal to or greater than the Lookup value.

More information about the Match function here.

1 thought on “How to Use Index and Match to Replace Vlookup”

Leave a Comment