INDEX & MATCH: The Dynamic Duo

Before XLOOKUP, the combination of INDEX and MATCH was the pro’s choice for overcoming VLOOKUP’s limitations. It remains a critical skill for backwards compatibility and understanding array logic.

How It Works

It separates the “position finding” from the “value retrieval”.

1. MATCH: Returns the position (row number) of an item in a list.

   =MATCH("Apple", A:A, 0)
   

2. INDEX: Returns the value at a specific position (row/col) in a range.

   =INDEX(B:B, 5)
   

Combined Formula

=INDEX(Return_Column, MATCH(Lookup_Value, Lookup_Column, 0))

This effectively says: “Look in the Return Column, at the row number where the Lookup Value appears in the Lookup Column.”

INDEX Match causes less performance drag on very large workbooks compared to VLOOKUP because it only references the specific columns needed, not a massive table array.