XLOOKUP in Google Sheets is a function designed to search for a specified item in a range or array, then return the corresponding item from a different range or array. It is particularly useful for finding and retrieving data by row from a table or range. This function is seen as a more flexible and powerful alternative to older functions like VLOOKUP and HLOOKUP, as it overcomes several of their limitations.
The basic syntax of XLOOKUP is as follows:
XLOOKUP(search_key, search_range, return_range, [if_not_found], [match_mode], [search_mode])
search_key: The value to search for.
search_range: The range or array to search within.
return_range: The range or array from which to return a value.
[if_not_found]: (Optional) The value to return if the search_key is not found.
[match_mode]: (Optional) Specifies whether to look for an exact match or an approximate match.
[search_mode]: (Optional) Specifies the order to search in.
Here are eight examples of XLOOKUP functions in Google Sheets that you can use.
Scenario: Find the price of a specific item in a list.
Data:
Column A: Items (Apple, Banana, Carrot)
Column B: Prices (1.50, 0.75, 2.00)
Formula: = XLOOKUP("Banana", A2:A4, B2:B4)
In a new cell, enter =XLOOKUP("Banana", A2:A4, B2:B4).
The formula searches for "Banana" in the range A2:A4. It returns the corresponding price from B2:B4.
Scenario: Retrieve a row of data for a specific person.
Data:
Column A: Names (Alice, Bob, Charlie)Columns B-D: Age, City, Phone Number
Formula:
= XLOOKUP("Bob", A2:A4, B2:D4)
Enter =XLOOKUP("Bob", A2:A4, B2:D4).
This looks up "Bob" in A2:A4. It returns his Age, City, and Phone Number from B2:D4.
Scenario: Display a custom message if an item is not found.
Data: Same as in example 1.
Formula:
= XLOOKUP("Orange", A2:A4, B2:B4, "Not Found")
Type =XLOOKUP("Orange", A2:A4, B2:B4, "Not Found").
It tries to find "Orange" in A2:A4. Since "Orange" is not there, it displays "Not Found".
Scenario: Find the closest lower value for a numeric key.
Data: Column A: Minimum Order Quantity (100, 500, 1000)Column B: Discount Rate (5%, 10%, 15%)
Formula:
= XLOOKUP(750, A2:A4, B2:B4, , -1)
Use =XLOOKUP(750, A2:A4, B2:B4, , -1). This formula looks for the closest value to 750 in A2:A4, without exceeding it.
This returns the corresponding discount rate from B2:B4.
Scenario: Find the last occurrence of an item in a list.
Data: Same as in example 1.
Formula:
= XLOOKUP("Banana", A2:A4, B2:B4, , 0, -1)Steps:
Enter =XLOOKUP("Banana", A2:A4, B2:B4, , 0, -1).
Searches for "Banana" from bottom to top. This returns the last matching price from B2:B4.
Scenario: Retrieve a value from a row instead of a column.
Data:
Row 1: Months (January, February, March)Row 2: Sales (1000, 2000, 1500)
Formula:
= XLOOKUP("February", A1:C1, A2:C2)Steps:
Type =XLOOKUP("February", A1:C1, A2:C2).
This looks up "February" in A1:C1. This returns the corresponding sales figure from A2:C2.
Scenario: Perform a case-sensitive lookup.
Data:
Column A: Codes (ABC, abc, AbC)
Column B: Values (10, 20, 30)
Formula:
= XLOOKUP("abc", A2:A4, B2:B4, , 2)
Use =XLOOKUP("abc", A2:A4, B2:B4, , 2). This looks for "abc" exactly which respects the case.
This returns the corresponding value from B2:B4.
Scenario: Find stock for a product name containing the word "Widget".
Data:
Column A: Product Names (SuperWidget, MegaWidget, UltraWidget)
Column B: Stock (50, 75, 30)
Formula:
= XLOOKUP(TRUE, ARRAYFORMULA(REGEXMATCH(A2:A4, "Widget")), B2:B4)
Enter =XLOOKUP(TRUE, ARRAYFORMULA(REGEXMATCH(A2:A4, "Widget")), B2:B4).
This utilizes REGEXMATCH within an ARRAYFORMULA to identify cells containing "Widget", then XLOOKUP fetches the corresponding stock number.
We hope that you now have a better understanding of what XLOOKUP in Google Sheets is and how to use XLOOKUP in Google Sheets. If you enjoyed this article, you might also like our article on how to set up Google Calendar settings or our article on how to set up HLOOKUP in Google Sheets.
