In this article:

XLOOKUP in Google Sheets: How to Use it in 2024


What is XLOOKUP in Google Sheets?

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.

8 XLOOKUP Functions in Google Sheets

Here are eight examples of XLOOKUP functions in Google Sheets that you can use.

1. Finding a Specific Value

Scenario: Find the price of a specific item in a list.


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).

xlookup google sheets

The formula searches for "Banana" in the range A2:A4. It returns the corresponding price from B2:B4.

google sheets xlookup

2. Returning a Range of Values

Scenario: Retrieve a row of data for a specific person.


Column A: Names (Alice, Bob, Charlie)Columns B-D: Age, City, Phone Number


= XLOOKUP("Bob", A2:A4, B2:D4)

Enter =XLOOKUP("Bob", A2:A4, B2:D4).

xlookup in google sheets

This looks up "Bob" in A2:A4. It returns his Age, City, and Phone Number from B2:D4.

xlookup sheets

3. Using if_not_found Argument

Scenario: Display a custom message if an item is not found.

Data: Same as in example 1.


= 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".

4. Approximate Match for Numeric Data

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%)


= 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.

5. Reverse Search

Scenario: Find the last occurrence of an item in a list.

Data: Same as in example 1.


= 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.

6. Horizontal Lookup

Scenario: Retrieve a value from a row instead of a column.


Row 1: Months (January, February, March)Row 2: Sales (1000, 2000, 1500)


= 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.

7. Case-Sensitive Search

Scenario: Perform a case-sensitive lookup.


Column A: Codes (ABC, abc, AbC)

Column B: Values (10, 20, 30)


= 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.

8. Partial Match Search without Wildcards

Scenario: Find stock for a product name containing the word "Widget".


Column A: Product Names (SuperWidget, MegaWidget, UltraWidget)

Column B: Stock (50, 75, 30)




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.  

Automate everything you track in spreadsheets with Lido
Learn more

Automate manual tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
Creates a one- or multiple-day calendar event with optional attendees
Adds an object to Hubspot
Create a Slack channel, and optionally add a topic or members
Makes any HTTP request
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
Makes any HTTP request and returns the response
Inserts given array below defined values in given worksheet
Sends an email using your Google account
Sends an email using your Microsoft Outlook account
Sends a Slack message
Sends an SMS message using your Twilio account
Updates cells with given values
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started