XLOOKUP to the left, with a default
Search one column, return from another on either side, and say what to show when nothing matches.
Task:
The supplier list has the account code in column A and the supplier name in column B. Accounts sent you names; in E2:E5 return each one's code, or Unknown when the supplier is not on the list.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of XLOOKUP
0/3The same formula in the other shapes it takes at work.
- XLOOKUP Function
The lookup that handles a missing value without a second function wrapped around it.
CoreAdvanced - XLOOKUP to the left, with a default
Search one column, return from another on either side, and say what to show when nothing matches.
Intermediate - XLOOKUP with 0 as the fallback
A missing item should cost nothing in the total, not break it.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Supplier | Name | Code | |
| 2 | SUP-01 | Arcadia Paper | Brindle Inks | ||
| 3 | SUP-02 | Brindle Inks | Arcadia Paper | ||
| 4 | SUP-03 | Calder Office | Dorset Print | ||
| 5 | SUP-04 | Delph Toner | Delph Toner |
What this exercise teachesMay contain the answer
XLOOKUP takes the search column and the return column separately, so left-hand lookups need no INDEX/MATCH, and its built-in fallback replaces the IFERROR wrapper. Dorset Print is not a supplier yet — Unknown says so plainly.