Index and Match – Part 1

How to use INDEX and MATCH

Apart from VLOOKUP, INDEX and MATCH is the most widely used tool in Excel for performing lookups. The INDEX and MATCH combo is potent and flexible, and you’ll see it used in all kinds of formulas, from basic to very advanced.

However, while VLOOKUP allows you to perform lookups with a single function, INDEX and MATCH requires two functions, one nested inside another. Many users find this confusing, because they aren’t used to combining functions in Excel, so they avoid INDEX and MATCH.

INDEX – get value at known position

The INDEX function in Excel is fantastically flexible and powerful, and you’ll find it in a huge number of Excel formulas, especially advanced formulas. But what does INDEX actually do? In a nutshell, INDEX retrieves values at a given location in a list or table. For example, let’s say you have a table of planets in our solar system (see below), and you want to get the name of the 4th planet, Mars, with a formula.

You can do it with this simple formula based on the INDEX function:


INDEX locates the 4th cell in B3:B11, B6, and returns the value at that address.

I will be adding more examples of Index and Match as this is extremely powerful.