Handling Misses With if_not_found
Return a friendly message when no match exists.
When a Lookup Finds Nothing
What happens when XLOOKUP cannot find the value you asked for? By default it returns the #N/A error.
That error is technically correct but looks ugly in a report and can break any formula that uses the result. XLOOKUP gives you a clean, built-in way to handle this.
=XLOOKUP("Mouse Pad", A2:A20, B2:B20)The if_not_found Argument
XLOOKUP has an optional fourth argument called if_not_found. Whatever you put there is returned when no match exists.
This is a major upgrade over VLOOKUP, which needed to be wrapped in IFERROR. With XLOOKUP the fallback is part of the same function.
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found)All lessons in this course
- The XLOOKUP Syntax
- Handling Misses With if_not_found
- Searching Left and From the Bottom
- Returning Whole Rows or Columns