What is the INDEX MATCH Function?

Before diving into the how-to, let's first understand the INDEX MATCH function. INDEX MATCH is a combination of two Excel functions: INDEX and MATCH.

INDEX: This function returns the value of a cell in a specified position in a range. For example, if you have a range of cells from A1 to A5 like this:

A1: Apple

A2: Banana

A3: Cherry

A4: Date

A5: Elderberry

And you use the INDEX function like this: =INDEX(A1:A5, 3), it will return "Cherry" because "Cherry" is in the third position in the range.

MATCH: This function returns the relative position of an item in a range that matches a specified value. For example, if you use the same range of cells from A1 to A5 and use the MATCH function like this: =MATCH("Date", A1:A5, 0), it will return 4 because "Date" is the fourth item in the range.

When combined, INDEX MATCH is a more powerful and flexible tool for looking up data than the traditional VLOOKUP or HLOOKUP. The MATCH function finds the position of your lookup value in a range, and the INDEX function uses that position to return the corresponding value from a different range.

How to Use the INDEX MATCH Function

Here are the steps to use the INDEX MATCH function:

1. Identify the Lookup Value: This is the value you want to find in your Excel sheet. It could be a name, a number, a date, or any other piece of data.

2. Identify the Lookup Array for MATCH: This is the range of cells where you want to find the lookup value.

3. Identify the Return Array for INDEX: This is the range of cells from which you want to retrieve a value.

4. Combine INDEX and MATCH in a Formula: The syntax for the combined function is

=INDEX(return_array, MATCH(lookup_value, lookup_array, match_type)).

The match_type argument is optional and specifies how Excel matches the lookup_value with values in the lookup_array. The default value is 1, which means Excel finds the largest value less than or equal to lookup_value.

Let's illustrate this with an example:

Imagine you have a spreadsheet with employee names in column A and their respective salaries in column B. You want to find out a specific employee's salary, say, "John Doe”.

Here's how you would use the INDEX MATCH function:

=INDEX(B2:B100, MATCH("John Doe", A2:A100, 0))

In this formula, "John Doe" is the lookup_value, A2:A100 is the lookup_array, and B2:B100 is the return_array. The MATCH function finds the row where "John Doe" is located, and the INDEX function returns the salary from that same row.

Advantages of Using INDEX MATCH

The INDEX MATCH function has several advantages over VLOOKUP:

- Left Lookup: Unlike VLOOKUP, which only looks to the right, INDEX MATCH can look in both directions.

- Inserting Columns: If you insert a column in your data set, VLOOKUP will return incorrect results, whereas INDEX MATCH will still work correctly.

- Performance: For large data sets, INDEX MATCH is faster and more efficient than VLOOKUP.

Conclusion

The INDEX MATCH function might seem intimidating at first, but with practice, it can become one of your favorite Excel tools. It offers flexibility, efficiency, and accuracy, making it a valuable function for anyone who regularly works with data in Excel.