Excel track · Exercise 40 of 86 Paid
XLOOKUP with a fallback when nothing matches
Answer a missing SKU with a sentence instead of an error.
Intermediate
A three-SKU price list
Read-only data · answer in B6
In B6, look up the price of the SKU in B5, and return "not stocked" when the list has no such SKU.
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | TX-101 | 19.99 |
| 3 | TX-102 | 14 |
| 4 | TX-103 | 9.5 |
| 5 | Lookup | TX-104 |
| 6 | Price |
Reference answer
What it does
The list holds three SKUs and B5 asks for TX-104, which is not among them. A three-argument XLOOKUP would answer #N/A, and a column of #N/A sends the reader to the formula bar to work out what went wrong.
The fourth argument is the text to show instead. XLOOKUP returns "not stocked" and the cell reads as a sentence. That text appears exactly as typed, so the lower case in the prompt is part of the answer.
Use it on sheets that other people read. The fallback should say what is missing, and it should never be a zero, because a zero passes for a price.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])Worked example
On A three-SKU price list, =XLOOKUP(B5,A2:A4,B2:B4,"not stocked") returns not stocked.
=XLOOKUP(B5,A2:A4,B2:B4,"not stocked")Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | No fallback was supplied, so a SKU that is not in A2:A4 came back with nothing to say. |
#VALUE! | The search column and the return column are different sizes, and some rows have no partner. |
At work
A stock sheet where a discontinued SKU has to read as out of stock in an email, rather than #N/A.