Blog

NH Learning Solutions Blog

VLOOKUP vs. MATCH/INDEX in Microsoft Excel

Posted: Apr 30, 2019
Comments:
0

VLOOKUP vs. MATCH/INDEX in Microsoft Excel

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.

1_vlookup

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.

2_vlookup

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.

3_vlookup

VLOOKUP

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.

4_vlookup

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…

5_vlookup

…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:

6_vlookup

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.

7_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.


Download the Free eBook

Before You Go - Don't Forget to Subscribe to Our Blog

Join our community and subscribe to our blog to receive great content surrounding the IT industry delivered right to your inbox every week!

Subscribe to Our Blog Now

Subscribe to Our Blog