Lookups: XLOOKUP & INDEX/MATCH

Looking a value up in another table is the most common task in Excel: find a product's price from its code, a customer's city from their id, a GST rate from an HSN code. Three techniques cover every case.

XLOOKUP, the modern default

=XLOOKUP(E2, Products[Code], Products[Price], "Not found")
=INDEX(Products[Price], MATCH(E2, Products[Code], 0))

XLOOKUP (Excel 365 and 2021) takes the value to find, the column to search in, and the column to return. It matches exactly by default, can look to the left, and its fourth argument replaces the clumsy IFERROR(VLOOKUP(...)) wrapper.

=XLOOKUP(E2, Products[Code], Products[Price], "Code not in master", 0)
=XLOOKUP(E2, Products[Code], Products[[Name]:[GST %]])      returns several columns at once
=XLOOKUP(E2, Products[Code], Products[Price], , 0, -1)      search from the bottom up

The arguments are: lookup value, lookup array, return array, if-not-found, match mode, search mode.

INDEX/MATCH, which works everywhere

On older Excel versions (and in files shared with colleagues who have them), INDEX and MATCH do the same job. MATCH finds the row number, INDEX returns the value in that position.

=INDEX(Products[Price], MATCH(E2, Products[Code], 0))
=INDEX(Sales!$C$2:$H$500, MATCH($A2, Sales!$B$2:$B$500, 0), MATCH(B$1, Sales!$C$1:$H$1, 0))

The second form is a two-way lookup: one MATCH for the row, another for the column.

Comparison

	VLOOKUP	INDEX/MATCH	XLOOKUP
Looks left	No	Yes	Yes
Breaks if a column is inserted	Yes	No	No
Default match	Approximate	Exact (with 0)	Exact
Built-in not-found message	No	No	Yes
Available in Excel 2016	Yes	Yes	No

Approximate match for slabs

Set XLOOKUP's match mode to -1 (next smaller item) for commission or discount slabs. The slab table must be sorted ascending.

Slab table:   0 -> 0%,  50000 -> 2%,  200000 -> 4%,  500000 -> 6%
=XLOOKUP(E2, Slabs[MinSales], Slabs[Rate], 0, -1)

The old equivalent is =VLOOKUP(E2, Slabs, 2, TRUE), where TRUE means approximate and silently returns nonsense if the table is not sorted.

Wildcards and multiple criteria

=XLOOKUP("*saree*", Products[Name], Products[Price], "", 2)     wildcard match mode
=XLOOKUP(1, (Sales[City]=H2)*(Sales[Month]=H3), Sales[Amount], "None")

The second pattern multiplies two TRUE/FALSE arrays, so only the row matching both conditions equals 1.

Common mistakes

• Looking up a text code against a numeric one; "1024" never matches 1024. Fix the types, or use =VALUE().
• Trailing spaces from an export; wrap the lookup value in TRIM.
• Forgetting 0/FALSE in MATCH or VLOOKUP, so you get an approximate match on unsorted data.
• Not locking ranges with $ (or better, using a proper Table name) so the range slides as you copy down.