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:
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.
You can try it out for yourself using the Excel file posted below the video!
Syntax of Index
Row number, [
Column number], [
|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.
Syntax of Match
Lookup array, [
|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
0, exact match: only finds the exact
-1, greater than: finds values equal to or greater than the