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))