How to Use XLOOKUP in Google Sheets with Fallbacks
Struggling to find values in your data? XLOOKUP provides a powerful solution with fallback options for missing results.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Use XLOOKUP in Google Sheets to find a value with a fallback for missing results"
=XLOOKUP(A2, B2:B10, C2:C10, "Not Found")This formula searches for a value in a specified range and returns a corresponding value, with a fallback if the value is not found.
Step-by-step breakdown
- A2 is the lookup value you want to find.
- B2:B10 is the range where the formula searches for the lookup value.
- C2:C10 is the range from which the formula returns the corresponding value.
- "Not Found" is the fallback text returned if the lookup value is not found.
Edge cases & warnings
- The lookup value is not present in the search range.
- The search range contains duplicate values.
- The lookup value is of a different data type than the search range.
- The search range is empty.
Examples
"Looking for 'Apple' in column B with corresponding values in column C"
'Not Found' if 'Apple' is not in B2:B10"Looking for 'Banana' in column B with corresponding values in column C"
Returns the corresponding value from column C for 'Banana'Frequently asked questions
What happens if the lookup value is not found?
The formula returns the specified fallback value, such as 'Not Found'.
Can I use XLOOKUP with multiple criteria?
XLOOKUP does not support multiple criteria directly; consider using FILTER or combining conditions.
Is XLOOKUP available in all versions of Google Sheets?
XLOOKUP is available in the latest versions of Google Sheets, so ensure your app is updated.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.