INDEX and MATCH

INDEX and MATCH perform a lookup as two steps. MATCH searches a column for a key and returns its position; INDEX reads the value at a given position in a column you name. Used together, the position MATCH found becomes the position INDEX reads, and the result is the same value a single lookup function would return.

In a hypothetical table, A2:A4 contains Ada, Ben, and Cy, while B2:B4 contains 10, 20, and 30. =MATCH("Ben",A2:A4,0) returns 2, Ben’s position in that range. Passing it to =INDEX(B2:B4,MATCH("Ben",A2:A4,0)) returns the second value in the return range, 20.

The reason to prefer the pair is that neither half counts columns. A VLOOKUP’s return column is an offset inside the searched range, so inserting a column into that table leaves the number pointing at different data without raising an error. With INDEX and MATCH, the lookup column and the return column are each referenced as their own range, and inserting a column moves them both.

The second gain is direction. Microsoft notes that the VLOOKUP function “can only look up a value from left to right”, so the key must sit left of whatever you want back. Because MATCH and INDEX take separate ranges, the value returned can lie on either side of the key — which matters whenever a table you were given puts its identifier in the middle and rearranging someone else’s sheet is not an option. Microsoft’s own guidance is to “use the combination of INDEX and MATCH functions instead” when VLOOKUP’s limits bite.

Two notes on use. MATCH takes a third argument, the match type, and it defaults to 1 when omitted, so write the 0 explicitly. Zero means an exact match, which suits identifiers such as customer IDs or product codes. A 1 returns the position of the largest value less than or equal to the key and requires the lookup column to be sorted ascending, which suits banded values such as tax brackets or grade cutoffs; −1 is the mirror case for a descending sort. VLOOKUP’s final argument makes the same exact-versus-approximate choice. And where XLOOKUP is available it offers these same advantages in one function with exact matching by default; INDEX and MATCH remains the portable answer across older versions and between applications.

References:

• Look up values with VLOOKUP, INDEX, or MATCH
• XLOOKUP function