Index Match in Excel: A Powerful Lookup Technique

If you want to perform advanced lookups in Excel, you may have heard of the INDEX MATCH formula. This is a combination of two functions that can handle more complex scenarios than the popular VLOOKUP function. In this article, you will learn what INDEX MATCH is, how it works, and how to use it for various lookup tasks.

What is INDEX MATCH?

INDEX MATCH is a formula that consists of two nested functions: INDEX and MATCH. The INDEX function returns a value from a specific cell in a range or table, based on the row and column number. The MATCH function returns the position of a value in a row or column. By combining these two functions, you can look up a value in a range or table based on both the row and column criteria.

The basic syntax of the INDEX MATCH formula is:

=INDEX(data_array, MATCH(vlookup_value, lookup_column_range, 0), MATCH(hlookup_value, lookup_row_range, 0))

Where:

data_array is the range or table that contains the value you want to return.
vlookup_value is the value you want to look up in the first column of the data array.
lookup_column_range is the range that contains the vlookup value.
hlookup_value is the value you want to look up in the first row of the data array.
lookup_row_range is the range that contains the hlookup value.
0 is the match type that indicates an exact match.

How does INDEX MATCH work?

To understand how INDEX MATCH works, let’s look at an example. Suppose you have a table of sales data for different products and regions, as shown below:

You want to look up the sales amount for Product B in Region 2. You can use the following INDEX MATCH formula:

=INDEX(B3:F7,MATCH("Product B",A3:A7,0),MATCH("Region 2",B2:F2,0))

Here’s how the formula works:

The MATCH function in the second argument returns the position of “Product B” in the range A3:A7, which is 2.
The MATCH function in the third argument returns the position of “Region 2” in the range B2:F2, which is 3.
The INDEX function returns the value from the data array B3:F7, at the intersection of row 2 and column 3, which is 120.

The result is 120, as shown below:

Why use INDEX MATCH instead of VLOOKUP?

You may be wondering why you should use INDEX MATCH instead of VLOOKUP, which is a simpler and more widely used function. The answer is that INDEX MATCH has some advantages over VLOOKUP that make it more powerful and flexible for advanced lookups. Here are some of them:

INDEX MATCH can look up values in any column, not just the first one. VLOOKUP can only return values from columns to the right of the lookup column. This means that if you want to look up a value based on a column that is not the first one, you have to rearrange your data or use helper columns. With INDEX MATCH, you can specify any column as the lookup column and return values from any other column.
INDEX MATCH can look up values in any row, not just the first one. VLOOKUP can only return values from rows below the lookup row. This means that if you want to look up a value based on a row that is not the first one, you have to transpose your data or use helper rows. With INDEX MATCH, you can specify any row as the lookup row and return values from any other row.
INDEX MATCH can perform left lookups, meaning it can return values from columns to the left of the lookup column. VLOOKUP cannot do this, as it always looks to the right. This means that if you want to return values from columns to the left of your lookup column, you have to use another function like HLOOKUP or LOOKUP. With INDEX MATCH, you can simply swap the order of the arguments and perform a left lookup.
INDEX MATCH can perform case-sensitive lookups, meaning it can distinguish between uppercase and lowercase letters. VLOOKUP cannot do this, as it always performs case-insensitive lookups. This means that if you want to match values based on their exact case, you have to use another function like EXACT or FIND. With INDEX MATCH, you can simply wrap your lookup values with the EXACT function and perform a case-sensitive lookup.
INDEX MATCH can perform multiple criteria lookups, meaning it can match values based on more than one condition. VLOOKUP cannot do this, as it only accepts one lookup value. This means that if you want to match values based on multiple criteria, you have to use another function like SUMIFS or COUNTIFS. With INDEX MATCH, you can simply use an array formula and combine your lookup values with logical operators like AND or OR.

How to use INDEX MATCH for various lookup tasks

Now that you know what INDEX MATCH is and why it is better than VLOOKUP, let’s see how to use it for various lookup tasks. Here are some examples of common scenarios where INDEX MATCH can be useful:

Two-way lookup

A two-way lookup is when you want to look up a value based on both the row and column criteria. This is the basic use case of INDEX MATCH, as we have seen in the previous example. You can use the following formula:

=INDEX(data_array, MATCH(vlookup_value, lookup_column_range, 0), MATCH(hlookup_value, lookup_row_range, 0))

For example, to look up the sales amount for Product C in Region 4, you can use:

=INDEX(B3:F7,MATCH("Product C",A3:A7,0),MATCH("Region 4",B2:F2,0))

The result is 140.

Left lookup

A left lookup is when you want to look up a value in a column to the left of the lookup column. This is something that VLOOKUP cannot do, but INDEX MATCH can. You can use the following formula:

=INDEX(lookup_column_range, MATCH(lookup_value, return_column_range, 0))

For example, to look up the product name based on the sales amount in Region 3, you can use:

=INDEX(A3:A7,MATCH(150,C3:C7,0))

The result is Product D.

Case-sensitive lookup

A case-sensitive lookup is when you want to look up a value based on its exact case. This is something that VLOOKUP cannot do, but INDEX MATCH can. You can use the following formula:

=INDEX(return_column_range, MATCH(EXACT(lookup_value, lookup_column_range), TRUE))

For example, to look up the sales amount for product b in Region 1 (note the lowercase b), you can use:

=INDEX(B3:B7,MATCH(EXACT("product b",A3:A7),TRUE))

The result is 90.

Closest match

A closest match is when you want to look up a value that is closest to your lookup value, either smaller or larger. This is something that VLOOKUP can do with an approximate match type (1 or -1), but INDEX MATCH can also do it with more flexibility. You can use the following formula:

=INDEX(return_column_range,MATCH(lookup_value,lookup_column_range,-1))

For example, to look up the product name that has the closest sales amount to 125 in Region 2 (either smaller or larger), you can use:

=INDEX(A3:A7,MATCH(125,D3:D7,-1))

The result is Product C.

Multiple criteria lookup

A multiple criteria lookup is when you want to look up a value based on more than one condition. This is something that VLOOKUP cannot do, but INDEX MATCH can. You can use the following formula as an array formula (press Ctrl + Shift + Enter):

=INDEX(return_column_range,MATCH(1,(condition1)*(condition2)*(condition3),0))

For example, to look up the sales amount for Product A in Region 1 that is greater than 100, you can use:

=INDEX(B3:F7,MATCH(1,(A3:A7="Product A")*(B2:F2="Region 1")*(B3:F7>100),0))

The result is 110.

Conclusion

INDEX MATCH is a powerful technique for performing advanced lookups in Excel. It can handle more complex scenarios than VLOOKUP and has some advantages over it. By learning how to use INDEX MATCH for various lookup tasks, you can improve your Excel skills and impress your boss.

If you want to learn more about INDEX MATCH and other Excel functions, check out these resources:

Index Match in Excel: A Powerful Lookup Technique