How to Use XLOOKUP in Google Sheets?
XLOOKUP simplifies data retrieval in Google Sheets, making it easier to replace the older VLOOKUP function with enhanced capabilities.
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 (available in Sheets as a beta and now generally available) to replace VLOOKUP"
=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
- lookup_value: the value you want to search for.
- lookup_array: the range or array where the lookup_value is searched.
- return_array: the range or array from which to return the corresponding value.
- if_not_found: optional value to return if no match is found.
- 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
"XLOOKUP('Apple', A2:A5, B2:B5)"
Returns the price of 'Apple' from the price list."XLOOKUP('Banana', A2:A5, B2:B5, 'Not Found')"
'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 a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.