In this article:

XLOOKUP in Google Sheets: How to Use it in 2024

Source: golayer.io

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.

XLOOKUP SYNTAX

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.

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

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.

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

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.

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

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

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.

5. Reverse Search

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.

6. Horizontal Lookup

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.

7. Case-Sensitive Search

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.

8. Partial Match Search without Wildcards

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.  

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.

Trigger
Action
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
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started