Skip to content

Lookups · Medium

How does INDEX-MATCH work, and why was it preferred over VLOOKUP?

Short answer

MATCH finds the position of a value in a range; INDEX returns the value at that position in another range. Combined, they perform a lookup in any direction, survive column insertions and are efficient on large sheets. It was the standard robust lookup before XLOOKUP.

Detailed answer

=INDEX(C:C, MATCH(A2, B:B, 0))

  • MATCH(A2, B:B, 0) → row position of A2 in column B (0 = exact).
  • INDEX(C:C, n) → the nth value in column C.

Two-way lookup:

=INDEX(B2:F10, MATCH(H2, A2:A10, 0), MATCH(H3, B1:F1, 0))