Check out the video below to learn how to use Match and Index to replace Vlookup in Excel.
Video
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 |
|---|---|---|
| 1 | Array | One or more arrays or cell ranges. |
| 2 | Row number | The row of the value we want to return. |
| 3 | Column number(Optional) | The column of the value we want to return. |
| 4 | Area 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 |
|---|---|---|
| 1 | Lookup value | The value to search for. |
| 2 | Lookup array | An array or cell range to search in. |
| 3 | Match 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. |
1 thought on “How to Use Index and Match to Replace Vlookup”