INDEX MATCH in Excel: Flexible Lookups Explained Simply

INDEX MATCH is the lookup that analysts swore by before XLOOKUP existed. It still matters: it works in every Excel version, looks in any direction and doesn't break when columns move.

The two parts

MATCH finds the position of a value in a list:

=MATCH("Latte",B2:B4,0)   → 2

(Latte is the 2nd item. The 0 means exact match.)

INDEX returns the value at a position:

=INDEX(C2:C4,2)   → the 2nd value in C2:C4

Put them together

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Price of the product in F2:

=INDEX($C$2:$C$4,MATCH(F2,$B$2:$B$4,0))

Code for a product (a left lookup VLOOKUP can't do):

=INDEX($A$2:$A$4,MATCH(F2,$B$2:$B$4,0))

Why people like it

• Looks left or right
• Inserting columns doesn't break it (you point at ranges, not column numbers)
• Works in every Excel version and Google Sheets

Two-way lookup

Find a value at the crossing of a row and a column, e.g. sales for a region (rows) and month (columns):

=INDEX($B$2:$M$5,MATCH(P1,$A$2:$A$5,0),MATCH(P2,$B$1:$M$1,0))

The first MATCH finds the row, the second finds the column.

Don't forget the 0

MATCH(x,range) without the 0 uses approximate match and needs sorted data. For lookups by name or code, always use 0.

INDEX MATCH vs XLOOKUP

If you have XLOOKUP, it does the same job in a shorter formula. INDEX MATCH is still worth knowing for older files and for job tests. See MATCH for more on the position part.

Frequently asked questions

Why use INDEX MATCH instead of VLOOKUP?

It can look left, doesn't break when columns are inserted, and works in every Excel version.

What does the 0 in MATCH mean?

Exact match. Without it, MATCH assumes sorted data and finds approximate matches.

Is INDEX MATCH faster than VLOOKUP?

On very large sheets it can be, but for most files the difference isn't noticeable.

Does INDEX MATCH work in Google Sheets?

Yes, with the same syntax.