How to use INDEX and MATCH with a table

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

The lastargumentis FALSE to force an exact match.

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

Forarray, I use the entire table.

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

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

With this information, INDEX correctly returns “Jon”.

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

Thearrayis the ID column of the table.

Andmatch_typeis zero, for exact match.

The row number now works.

To getcolumn_number, I use MATCH again.

This time, thelookup_valuecomes from column M.

Thearrayis the table header.

Andmatch_typeis 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, thelookup_valuedoesn’t need to be the first column.