In this article:

XLOOKUP in Google Sheets: How to Use it in 2024

May 8, 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.  

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->