Look up a scanned SKU that arrived messy (TRIM + UPPER)
The problem
Problem statement
The barcode scanner writes SKUs with padding and lower case, so E1 holds sku-103 while the price table in A2:C6 stores clean upper-case keys. Return the price for that scanned code in the Price cell E2, and "Not found" when the code is not in the table.
Clean the scanned key inside the formula — do not edit E1 and do not add a helper column.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Product | Price | Scanned SKU | sku-103 |
| 2 | SKU-101 | Keyboard | 49 | Price | |
| 3 | SKU-102 | Mouse | 19.5 | ||
| 4 | SKU-103 | Monitor | 24.99 | ||
| 5 | SKU-104 | Dock | 89 | ||
| 6 | SKU-105 | Cable | 12.5 |
Notes
TRIM removes the padding spaces and UPPER fixes the case, so the cleaned key matches the table. The fourth argument of XLOOKUP is the not-found value: supply it instead of wrapping everything in IFERROR. INDEX + MATCH needs IFERROR for the same behaviour.
How to approach
Your answer goes in E2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is E2
- Clean the lookup key first: TRIM(UPPER(E1))
- XLOOKUP's fourth argument is what to return when nothing matches
- Shape: =XLOOKUP(?, ?, ?, ?)
Official solutions
Show 2 solutions — try it yourself first
Approach 1: XLOOKUP with a cleaned key and a not-found value
=XLOOKUP(TRIM(UPPER(E1)), A2:A6, C2:C6, "Not found")
TRIM(UPPER(" sku-103 ")) is "SKU-103", which matches A4, so XLOOKUP returns C4 — 24.99. When the key is missing, the fourth argument returns "Not found" instead of #N/A.
Approach 2: INDEX + MATCH wrapped in IFERROR
=IFERROR(INDEX(C2:C6, MATCH(TRIM(UPPER(E1)), A2:A6, 0)), "Not found")
Same cleaning, older syntax: MATCH finds the row, INDEX returns the price, and IFERROR supplies the fallback that XLOOKUP takes as an argument.
Function guide
FAQ
Why does my lookup fail when the values look identical?
Invisible padding and case are the usual culprits: "SKU-103" and " sku-103 " are different strings. Clean both sides with TRIM and UPPER before comparing.
How do I show a friendly message instead of #N/A?
XLOOKUP takes the fallback as its fourth argument: =XLOOKUP(key, keys, values, "Not found"). With INDEX + MATCH, wrap the whole thing in IFERROR.