Use MATCH and INDEX to quickly locate data

Most Excel experts get started with data lookups using the VLOOKUP and HLOOKUP tools (and usually the former).

However, there are two functions that don't have quite the same restrictions these functions have. (For example, the VLOOKUP function can only look up a value from left to right)

You can therefore think of INDEX and MATCH as the older siblings to VLOOKUP and HLOOKUP.

These two allow you to look up values in a table based on other rows and columns. Unlike VLOOKUP, the MATCH and INDEX functions can be used on rows, columns, or both at the same time.

When it's best to use MATCH and INDEX instead of VLOOKUP and HLOOKUP

Arguably, the answer to that is "anytime."

To understand that answer in more context, know that the MATCH function returns the position of a value in a given range.

For example, let's say you wanted to find values associated with Chicago in the table below:

As you can see, we have a small range here where the thing we're searching for, Chicago, isn't in the leftmost column. So, we can't use VLOOKUP.

Instead, we'll use the MATCH function to find Chicago in the range B1:B11.

It's found in row 4.

INDEX then uses that value as the lookup argument, and in this example, will return the population for Chicago

The formula used is shown in cell A14.


Seriously, though: I've got it all recorded in a video so that we can more easily see it in action.

That all for now; carry on.

Oh, and use MATCH and INDEX next time you need to find stuff in a large range of data. That was the whole point!!

Of course, you can learn much more about how to save time with the apps you use every day by getting enrolled in the All Access subscription at