Lookups
XLOOKUP function: exact match, left lookup, and missing-key fallback
Look up values in any column direction without counting column numbers, return a clear fallback when a code is missing, and optionally scan bottom-up for the latest entry.
A spare-parts table where the rack code sits left of the SKU
In this workshop list, Rack (column A) sits to the left of SKU (column B), while Unit cost sits to the right (column C). Classic VLOOKUP cannot look left to column A, whereas XLOOKUP takes separate lookup and return ranges.
| Rack (A) | SKU (B) | Unit cost (C) |
|---|---|---|
| R-04 | PT-101 | 22 |
| R-12 | PT-204 | 38 |
| R-07 | PT-315 | 15 |
| R-19 | PT-101 | 25 |
Exact match by default plus built-in fallback
XLOOKUP defaults to an exact match—you do not need a fourth FALSE argument. Passing "Unlisted SKU" as the fourth argument (if_not_found) handles missing keys cleanly without wrapping the formula in IFNA.
=XLOOKUP(E2,B2:B5,A2:A5,"Unlisted SKU")First match vs last match when a code repeats
Notice PT-101 appears in row 2 (cost 22) and again in row 5 after a restock (cost 25). By default XLOOKUP scans top-down and returns 22. Setting the sixth argument (search_mode) to -1 scans bottom-up and returns the latest cost, 25.
In the practice grid, change E2 from PT-204 to PT-999 to confirm the fallback "Unlisted SKU" appears instead of #N/A.
=XLOOKUP("PT-101",B2:B5,C2:C5,"Unlisted SKU",0,-1)Try the example
Use the original values or change them to test your understanding.