INDEX MATCH – VLOOKUP on Steroids

INDEX MATCH

INDEX MATCH is a set of functions nested together that essentially performs the same task that VLOOKUP does, except it does it a little differently (and more efficiently). This formula is a popular among the most elite Excel users because it offers some advantages over the VLOOKUP that we’ll get into in this awesome video tutorial.

This formula is a combination of the INDEX and MATCH functions. Since we’ve already covered MATCH in an earlier chapter I won’t go into details about how it works. Lets instead jump into how INDEX works.

The INDEX Function

INDEX is a lookup function that returns a value within a column array based on the row number you specify.

=INDEX(array, row number)

Array – The column where you’re looking for your data

Row number – The row number where your data exists

As you can see from our example, INDEX returns a cell value based on the row number you give it. Nice, huh? What happens when we’re dealing with data and we don’t know the row number for the value we’re looking for? There is one function we can use to get that row number automatically, the MATCH function.

NESTING INDEX with MATCH: How it works

When we nest MATCH with INDEX, it looks like this:

=INDEX(array, MATCH Function)

Lets try this with another example

You’re probably wondering, “What’s the big deal here? I can just do that with VLOOKUP; why is INDEX MATCH such a popular formula?” Well, for starters, the INDEX MATCH formula is much more efficient than VLOOKUP because it only has to analyze the two columns of relevant data, not an entire table like VLOOKUP does.

This can make a huge difference in the performance of your workbook when you’re dealing with thousands of cells.

One of the biggest advantages this formula has over the VLOOKUP is that you don’t have to have the first column as the index column, so you can essentially look things up no matter which column its in. Lets use another example to illustrate this:

The INDEX MATCH is actually not that bad once you understand it and get used to using it. It can be one of the most versatile formulas in your arsenal once you start building advanced reports and dashboards.