If you need to get data from one data range into another, most people rely on the VLOOKUP function. But did you know there is another alternative with the combination of the MATCH and INDEX functions? This blog will cover both options, explain the differences, and when you might want to use one vs. the other.
MATCH and INDEX
The lookup function of INDEX will return the value in a given column of a given row within an array of data. The MATCH function looks up a value in a cell in a lookup_array and returns the row number that contains that value. Whenever the INDEX function is used, MATCH is almost always used with it – and it’s typically nested inside the INDEX function.
In this example, MATCH looks up the value in B1 (1003) in the lookup_array of all Employee ID’s (A10:A59) and returns 3 for the row ID.
The INDEX function will then return the value in a given cell in a given row.
In this example, to get the Employee Name, the INDEX function uses the row ID returned by the MATCH function and goes into the array (A10:F59) to return the value in the 2nd column of that row.
As mentioned earlier, users of these functions usually nest the MATCH in the INDEX function because the only reason to use the MATCH function is to provide the row # to the INDEX function. It eliminates the need for the “Row ID” to be stored in a cell.
The more common lookup function is VLOOKUP. Similar to MATCH and INDEX, it also looks up a cell value and returns the value in a column of the corresponding row. VLOOKUP does this in one function, rather than 2, which is why most people learn this function.
In this example, VLOOKUP will lookup the value in B1 (1003) in the table_array of A10:F59 and return the 3rd column of the corresponding row, which is the Department name. The 4th argument of “0” requires that an exact match be found on the lookup value.
Should You Use MATCH/INDEX or VLOOKUP?
As you can see, both of these lookup options do very similar things, but there is one major difference. Since INDEX goes to a row, it can return ANY column. VLOOKUP has a restriction. If our data range was setup so that the EMPLOYEE ID was not in the first column…
…the combination of MATCH/INDEX would not change, except that the lookup_array that MATCH was using to lookup would be in Column C rather than in Column A:
If we want to use VLOOKUP to get the Department Name, we could not because of the restriction concerning the table_array argument for VLOOKUP.
The first column of the table_array argument for VLOOKUP must be the column that contains the value that you are looking up. In other words, using this same layout, the table_array must begin with Column C. Any columns to the left are not accessible by VLOOKUP.
There are ways around this. You could copy or move the Employee ID column to be in Column A, but this may not be possible, especially if this file is used for other purposes. In this case, the combination of INDEX/MATCH can be used.
Let's Take This One Step Further
Though the details won’t be discussed here, if you would rather provide the ability for the Employee ID to be selected from a “Drop-down” box within the cell, this is possible, but different for INDEX and VLOOKUP. If you are using the INDEX function, creating a Combo-Box form control will replace the need for the MATCH function. This Combo-Box actually returns the Row number! Form controls require the Developer tab to be turned on within the Excel options.
If you are using the VLOOKUP function, applying Data Validation based on a list can provide this ability.
Hopefully, this has provided you additional insight into performing lookups using different tools and techniques.