Google Sheets beginner xlookup lookup data-analysis

How to Use XLOOKUP in Google Sheets with Fallbacks

Using XLOOKUP in Google Sheets is a Google Sheets function that searches for a value in a specified range and returns a corresponding value, with a fallback if the value is not found.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

Struggling to find values in your data? XLOOKUP provides a powerful solution with fallback options for missing results.

Generate This Formula Browse Library

The Formula

Prompt

"Use XLOOKUP in Google Sheets to find a value with a fallback for missing results"

Google Sheets 
=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

  1. A2 is the lookup value you want to find.
  2. B2:B10 is the range where the formula searches for the lookup value.
  3. C2:C10 is the range from which the formula returns the corresponding value.
  4. "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

Prompt

"Looking for 'Apple' in column B with corresponding values in column C"

Google Sheets 
'Not Found' if 'Apple' is not in B2:B10
Prompt

"Looking for 'Banana' in column B with corresponding values in column C"

Google Sheets 
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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples