INDEX and MATCH Two-Way Lookups in Excel

Build flexible two-way and left lookups using INDEX and MATCH in Excel. Safe from column insertions and faster on large financial models.

Syntax & Formula

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

MATCH finds the position of an item in a list, and INDEX returns the value at that position. Combining them allows lookups in any direction.

Workplace Example

=INDEX($C$2:$C$100, MATCH(E2, $A$2:$A$100, 0))

Common Mistakes to Avoid

Mismatched row ranges (e.g. INDEX from row 1 but MATCH from row 2), shifting all lookup results by one row.

Practise this in the spreadsheet simulator