Level 6 · INDEX/MATCH, right to left

Before XLOOKUP, the flexible lookup was a pair of functions: INDEX and MATCH. You will find it in almost every workbook you inherit, so it is worth knowing cold. Shirley’s catalog is the classic case for it: sorted by name in G1:I21, with the id in the last column. VLOOKUP can’t search it, because the ids aren’t in its first column.

MATCH finds the position

=MATCH(lookup_value, lookup_range, match_type)

MATCH doesn’t return a value from the table. It returns where the value is: its position in the range. =MATCH(B2,$I$2:$I$21,0) looks for 128 in the catalog ids and returns 3, because 128 is the third id in I2:I21. The last argument, 0, means exact match.

INDEX picks the value at a position

=INDEX(range, position)

=INDEX($H$2:$H$21,3) returns the third price in the catalog: 54.99. Put the two together and MATCH tells INDEX which row to read:

=INDEX($H$2:$H$21,MATCH(B2,$I$2:$I$21,0))

The search range and the return range are separate arguments, so the price column can sit left of the ids, as here, or anywhere else. The two ranges only need to start and end on the same rows.

The missing 0

MATCH has a trap in its default. Without the third argument it uses 1, an approximate match: it assumes the ids are sorted from small to large and jumps through them (a binary search) for the largest id that isn’t above yours. Row 4 is product 135, the Ridge Ceramic Chandelier, 159.00. MATCH(B4,$I$2:$I$21) returns 9 instead of 17, and INDEX hands back 349.00, another lamp’s price. No error. Fill that version down and only 3 of the 20 lines come out right, by luck. VLOOKUP without FALSE has exactly the same trap. Write the 0 every time you look up an id, a name or a code.

VLOOKUP, INDEX/MATCH and XLOOKUP side by side

	VLOOKUP	INDEX/MATCH	XLOOKUP
Direction	key must be the first column, returns to the right	any direction	any direction
Return column	a number (4)	a range	a range
A column inserted in the table	the number now points at the wrong column	still right	still right
Default match	approximate: write FALSE	approximate: write 0	exact
Key not found	#N/A, wrap in IFNA	#N/A, wrap in IFNA	if_not_found argument
Where it works	everywhere	everywhere	Excel 2021, Microsoft 365, Google Sheets

The inserted-column row is the quiet one. In level 3, VLOOKUP(…,4,FALSE) returned the price because the price was the 4th column of F:I. Insert a “supplier” column inside the catalog and column 4 becomes the category, with no error. INDEX and MATCH point at the price column itself, so Excel adjusts their ranges when columns move. On this sheet, the XLOOKUP version is =XLOOKUP(B4,$I$2:$I$21,$H$2:$H$21), which also gives 159.00.

Use XLOOKUP when everyone who opens the file has it; INDEX/MATCH when the file must work in older Excel; VLOOKUP with FALSE when the key is already the first column and you want the formula a colleague can read at a glance.

Common mistakes

• No 0 in MATCH. The lookup works on a sorted table and quietly fails on an unsorted one.
• Unlocked ranges. The catalog has to stay put when you fill down: $H$2:$H$21, $I$2:$I$21.
• Ranges that don’t line up. MATCH over I2:I21 with INDEX over H3:H22 returns the price from the row below every match.
• INDEX and MATCH swapped. MATCH gets the id column, INDEX gets the column you want back.