Google Sheets intermediate xlookup lookup data-retrieval

How to Use XLOOKUP in Google Sheets?

Using XLOOKUP in Sheets is a Google Sheets function that xlookup searches a range or an array, and returns an item corresponding to the first match it finds.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

XLOOKUP simplifies data retrieval in Google Sheets, making it easier to replace the older VLOOKUP function with enhanced capabilities.

Generate This Formula Browse Library

The Formula

Prompt

"Use XLOOKUP in Google Sheets (available in Sheets as a beta and now generally available) to replace VLOOKUP"

Google Sheets 
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP searches a range or an array, and returns an item corresponding to the first match it finds.

Step-by-Step Breakdown

  1. lookup_value: the value you want to search for.
  2. lookup_array: the range or array where the lookup_value is searched.
  3. return_array: the range or array from which to return the corresponding value.
  4. if_not_found: optional value to return if no match is found.
  5. match_mode: optional parameter to specify exact or approximate match.

Edge Cases & Warnings

  • If the lookup_value is not found, the formula will return the if_not_found value if provided.
  • Using XLOOKUP with non-contiguous ranges can lead to errors.
  • If the lookup_array and return_array are of different sizes, it will result in an error.

Examples

Prompt

"XLOOKUP('Apple', A2:A5, B2:B5)"

Google Sheets 
Returns the price of 'Apple' from the price list.
Prompt

"XLOOKUP('Banana', A2:A5, B2:B5, 'Not Found')"

Google Sheets 
'Not Found' if 'Banana' is not in the list.

Frequently Asked Questions

What is the difference between XLOOKUP and VLOOKUP?

XLOOKUP allows for searching in any direction and does not require the lookup column to be the first column.

Can XLOOKUP return multiple values?

Yes, XLOOKUP can return an array of values if the return_array is a range of multiple columns.

Is XLOOKUP available in all Google Sheets?

XLOOKUP is available in Google Sheets as a beta feature and is now generally available.

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