Quick, clean, and to the point

How to use INDEX and MATCH with a table

In this video, we'll look at how to use INDEX and MATCH with an Excel Table.

Using INDEX and MATCH with an Excel Table is wonderfully straightforward.

To illustrate, I'll build INDEX and MATCH formulas that do the same thing as the VLOOKUP formulas already on this worksheet.

First, to recap, these VLOOKUP formulas currently retrieve information from Table1 based on the ID provided in K4.

Each formula uses the lookup value from K4, the entire table for table array, and the MATCH function to figure out what column number to return, based on column names in column J. The last argument is FALSE to force an exact match.

If I change the ID, the formulas return information for a different employee.

I'll start off by creating a copy of this structure to the right, then I'll remove the formulas.

An simple way to build out an INDEX and MATCH formula is to start with INDEX only and hardcode the row and column numbers.

For array, I use the entire table.

For row number, I hardcode 5, since ID 622 corresponds to row 5 in the table.

For column index, I use 2, since first name is the second column.

With this information, INDEX correctly returns "Jon".

If I copy the formula down and change the column number to 3, I'll get Jon's last name.

Now all I need to do now is replace the hardcoded values with MATCH.

To get the row number, I use MATCH with N4 as the lookup value, I need to lock this because I don't want this to change.

The array is the ID column of the table.

And match type is zero, for exact match.

The row number now works.

To get column number, I use MATCH again.

This time, the lookup value comes from column M.

The array is the table header.

And match type is again zero, for exact match.

And now I have a set of working formulas, that work just like the VLOOKUP version.

However, INDEX and MATCH offers one big advantage.

Unlike VLOOKUP, the lookup value doesn't need to be the first column.

If I move the ID column in the table, the VLOOKUP formulas stop working, but the INDEX and MATCH formulas continue to work just fine.

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.