In today’s blog, we’ll cover the INDEX + MATCH formulas. I think that the INDEX + MATCH combination is one of the most versatile, simple, and powerful formulas. Without a doubt, I would say over the course of my career this would be in the top 3 formulas I’ve used in terms of frequency.
The Index Match formula is a flexible data lookup tool. It allows you to search datasets based on various criteria. The INDEX function returns a value in a table based on the row and column numbers you specify. The MATCH function determines the row and column number.
Why Use Index Match vs VLOOKUP?
The index match formula can have a huge impact on the way you analyze and perform calculations over datasets.
Whilst VLOOKUP performs a similar function, it is restricted by the following limitations:
- The resulting cell cannot be traced using F5 > Go To Special function (the biggest one for me)
- If set up incorrectly, it will require a hard-coded lookup row/column which is problematic when inserting and deleting columns and rows.
- The return range must be to the right of the lookup range
Enter the INDEX + MATCH formula!
Interesting read – What Is Data Validation In Excel?
The Index Match Formula
Here’s the syntax in layman terms:
INDEX(column you wish to return, MATCH(value you are searching, column containing search data, 0)).
Note – the hardcoded 0 instructs the MATCH formula to only return exact matches. If you use 1, the MATCH finds the largest value that is less than or equal to the “value you are searching”.
You can find the Microsoft explanation here.
Index Match Example
Here is an example of the formula in use – we want to return the “Sales” value based on the “Country Lookup” field:
The formula here would be as follows:
INDEX(B2:B8 (column you wish to return), MATCH(E3 (value you’re searching for), A2:A8 (column containing search data, 0))
As you can see, the looked-up value is equal to cell B6. If you were to change the country look up to another country, it will automatically pick up the corresponding Sales value.
You can build in additional flexibility by incorporating other functions in the MATCH – for example, a MAX or MIN function to allow for dynamic searching of certain figures.
Hope you found this tip helpful and that it saves you time!