Combine INDEX and MATCH to unlock classic leftward, resilient, and 2D matrix lookups across every Excel version.
Concept:
Combining INDEX(return_col, MATCH(key, lookup_col, 0)) works across ALL Excel versions, looks left or right cleanly, and stays immune to inserted columns.
Key Rule:
Notice return_range and lookup_range are separate aligned vectors (e.g. A2:A6 and C2:C6).
Pro Tip:
You can nest TWO MATCH calls inside 2D INDEX: =INDEX(grid, MATCH(rowKey, rowHdrs, 0), MATCH(colKey, colHdrs, 0))!
Mistake to Avoid:
Mismatching row bounds like INDEX(A2:A6, MATCH(key, C1:C6, 0)) shifts returned answers by 1 row.