Check out the video below to learn how to use Match and Index to replace Vlookup in Excel.
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.
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])
|One or more arrays or cell ranges.
|The row of the value we want to return.
|The column of the value we want to return.
|The array index of the value we want to return.Only useful when you have more than 1 array as the first argument.
Syntax of Match
=MATCH(Lookup value,Lookup array, [Match type])
|The value to search for.
|An array or cell range to search in.
|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.