Challenge 3: XLOOKUP with a Built-In "Not Found" Message — Solution Walkthrough

Table: A1:B5 = SKU-100/9.99, SKU-200/14.50, SKU-300/22.00, SKU-400/7.25
D1 = a code typed by the user (may or may not exist in the table).

Formula:
  =XLOOKUP(D1, A1:A5, B1:B5, "Code not found")

Behaviour:
  - If D1 matches one of the codes in A1:A5, the formula returns that
    row's price from B1:B5, exactly like VLOOKUP or INDEX/MATCH would.
  - If D1 does NOT match anything in A1:A5 (e.g. a typo, or a
    discontinued code), the formula returns the literal text
    "Code not found" instead of an error.

Which argument makes this possible:

The 4th argument to XLOOKUP — often called if_not_found — is checked
specifically when no match is located. It's entirely optional; if
left out, a failed XLOOKUP falls back to the same #N/A error VLOOKUP
and INDEX/MATCH both produce. Supplying it turns that error into
whatever readable message you choose.

Why VLOOKUP and INDEX/MATCH can't do this on their own:

Neither function has an equivalent built-in argument for a custom
"not found" message — a failed VLOOKUP or a failed MATCH (inside an
INDEX/MATCH pair) simply returns the #N/A error value directly, with
no way to intercept it from inside the function call itself. Getting
the same friendly-message behaviour with VLOOKUP or INDEX/MATCH
requires wrapping the ENTIRE formula in a separate function, IFERROR:
  =IFERROR(VLOOKUP(D1,A1:B5,2,FALSE), "Code not found")
This works, but needs an extra function nested around the outside —
XLOOKUP simply has the option built directly into its own argument
list, one of its genuine, deliberate improvements over both of its
predecessors from this chapter.