How To Use the XLOOKUP Function in Google Sheets

The XLOOKUP function in Google Sheets is a game-changer for anyone working with large datasets.

By combining the best features of VLOOKUP and HLOOKUP, XLOOKUP makes data retrieval faster, more versatile, and simpler than ever. The XLOOKUP Google Sheet function can match values across both columns and rows.

In this post, we’ll walk through how to use XLOOKUP in Google Sheets with real-life examples.

What is XLOOKUP in Google Sheets?

The XLOOKUP function is designed to search for a specified value within a range and return a corresponding value from another range. Unlike VLOOKUP and HLOOKUP, which require data to be arranged in a particular order, XLOOKUP is more flexible, allowing searches in any direction. This means you can use it for both vertical and horizontal lookups without rearranging your data.

Why Use XLOOKUP Over VLOOKUP or HLOOKUP?

XLOOKUP not only replaces VLOOKUP and HLOOKUP but also improves upon them by allowing:

• Dynamic searches in any direction—up, down, left, or right.

• Exact and approximate matches, even with wildcards for partial matches.

• Custom error handling through the [missing_value] argument, so you can specify what to return if no match is found.

Syntax and Arguments of XLOOKUP Google Sheets

Here’s the basic syntax of XLOOKUP in Google Sheets:

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

Explanation of each argument in the XLOOKUP formula:

1. search key: The value you want to look up.

2. lookup range: The range where the function searches for the search_key.

3. result range: The range where the function will return a corresponding value if a match is found.

4. [missing value] (optional): A value to return if no match is found. This prevents error messages.

5. [match mode] (optional): Specifies the match type:

   • 0: for an exact match (default).

   • 1: for an exact or next larger match.

   • -1: for an exact or next smaller match.

   • 2: Wildcard match.

6. [search mode] (optional): Determines the order of search:

   • 1: search from the start (default).

   • -1: search from the end for reverse lookups.

   • 2: binary search, ascending

   • -2: binary search, descending

How To Use XLOOKUP Google Sheets (Real-Life Examples)

Here are some detailed examples demonstrating different ways to use the XLOOKUP function in Google Sheets. Each example will show how to solve real-world data lookup scenarios.

Example 1: Basic XLOOKUP for vertical lookup.

Let’s say you have a list of employees and their respective departments, and you want to find the department of a specific employee.

Task:

Find the department of "Charlie."

Formula:

=XLOOKUP("Charlie", A2:A5, B2:B5)

Explanation:

• search_key: "Charlie" is the name we're looking up.

• lookup_range: A2:A5, the range containing employee names.

• result_range: B2:B5, the range containing departments.

XLOOKUP searches for "Charlie" in A2:A5 and returns the corresponding value from B2:B5, which is "HR."

Example 2: Custom Error Message with [missing_value]

Imagine you want to search for an employee’s department, but if the employee doesn’t exist, you want to display a custom message instead of an error.

Task:

Look up "Eve’s" department. If not found, return "Employee Not Found."

Formula:

=XLOOKUP("Eve", A2:A5, B2:B5, "Employee Not Found")

Explanation:

The formula searches for "Eve" in A2:A5. Since "Eve" isn’t in the list, the XLOOKUP function returns the custom message "Employee Not Found" instead of an error.

Example 3: XLOOKUP with approximate match for price ranges.

Suppose you have a list of spending ranges and corresponding discount percentages. You want to find the discount percentage based on a spending amount, using an approximate match to find the closest value.

Task:

Find the discount for a spending amount of $750.

Formula:

=XLOOKUP(750, A2:A5, B2:B5, , -1)

Explanation:

• search_key: 750, the spending amount.

• lookup_range: A2:A5, the spending amounts.

• result_range: B2:B5, the discount percentages.

• [match_mode]: -1 specifies an exact or next smaller match, so it returns the discount for the closest value without exceeding 750.

The function will find 500 (the largest lookup value less than or equal to 750) and return 10% as the discount.

Example 4: Horizontal lookup with XLOOKUP.

If you have data laid out horizontally, such as scores for different subjects, XLOOKUP can perform a horizontal lookup to retrieve a score for a particular subject.

Task:

Find the score for "Science."

Formula:

=XLOOKUP("Science", B1:D1, B2:D2)

Explanation:

• search_key: "Science" is the subject to look up.

• lookup_range: B1:D1, the header row with subject names.

• result_range: B2:D2, the row containing scores.

The function finds "Science" in B1:D1 and returns the corresponding score from B2:D2, which is 92.

Example 5: Multiple criteria lookup using XLOOKUP with nested IF statements.

To perform a multi-criteria lookup with XLOOKUP in Google Sheets, you can combine it with the ARRAYFORMULA. Let’s say you have a list of products, colors, and stock levels and want to find the stock level for a specific product and color combination. 

Task:

Find the stock level for a "Shirt" that is "Blue."

Formula:

=ARRAYFORMULA(XLOOKUP(1, (A2:A5 = "Shirt") * (B2:B5 = "Blue"), C2:C5))

Explanation:

• ARRAYFORMULA allows the entire expression to operate over arrays, enabling multi-criteria lookups.
• (A2:A5 = "Shirt") and (B2:B5 = "Blue") each return an array of TRUE and FALSE values, depending on whether the conditions are met.
• Multiplying these two arrays with * creates an array of 1s (for rows where both conditions are met) and 0s (for rows where they aren’t).
• XLOOKUP(1, ..., C2:C5) looks up 1 in this new array, finding the first row where both conditions are met and returning the corresponding stock level from C2:C5.

This formula will return the stock level for a "Shirt" that is "Blue," which should be 45.

Example 6: Reverse lookup with XLOOKUP.

Imagine you have a list of item codes and descriptions and need to perform a reverse lookup to find the item code based on the description.

Task:

Find the item code for "Monitor."

Formula:

=XLOOKUP("Monitor", B2:B5, A2:A5)

Explanation:

• search_key: "Monitor" is the description we’re looking up.

• lookup_range: B2:B5, the range containing descriptions.

• result_range: A2:A5, the range containing item codes.

The formula searches for "Monitor" in B2:B5 and returns the corresponding item code from A2:A5, which is "A103."

Example 7: XLOOKUP with wildcards for Partial Matches

Suppose you want to find an email based on a partial name in a list of contacts and emails.

Task:

Find the email for a name that contains "Brown."

Formula:

=XLOOKUP("*"&"Brown"&"*", A2:A5, B2:B5, "Not Found", 2)

Explanation:

• "*"&"Brown"&"*": Combines * wildcards before and after "Brown" using & (concatenation) to allow any characters before or after "Brown". This part becomes *Brown*, which searches for any cell in A2:A5 containing "Brown" within the text, regardless of other surrounding characters.

• A2:A5: This is the lookup_range, where XLOOKUP searches for the value matching *Brown*.

• B2:B5: This is the result_range, where XLOOKUP retrieves the corresponding value if a match is found in the lookup_range.

• "Not Found": The [missing_value] argument specifies what to display if no match is found. Here, it will display "Not Found" if "Brown" isn’t present in any cell in A2:A5.

• 2: The [match_mode] argument is set to 2, which enables wildcard matching. This allows XLOOKUP to interpret the * and ? as wildcards for flexible searching.

The formula finds "Alice Brown" and returns her email address, [email protected].

Final Thoughts on "How To Use the XLOOKUP Function in Google Sheets"

With the introduction of XLOOKUP, Google Sheets has made data lookup tasks much easier and more flexible. This lookup function not only simplifies the process but also eliminates the need for workarounds with INDEX/MATCH or VLOOKUP. Give XLOOKUP a try, and see how it can streamline your work in Google Sheets.

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

FAQ on "How To Use the XLOOKUP Function in Google Sheets"

1. What is XLOOKUP in Google Sheets?
XLOOKUP is a Google Sheets function that finds data by searching in any direction within a range. It allows you to look up a value and return a corresponding value without the limitations of VLOOKUP or HLOOKUP.

2. How is XLOOKUP different from VLOOKUP?
Unlike VLOOKUP, XLOOKUP can search both horizontally and vertically, giving it more flexibility. Additionally, it defaults to exact matches, reducing potential errors from approximate matches.

3. Can I use XLOOKUP to handle missing values?
Yes, XLOOKUP allows you to specify a custom message or value if the search key isn’t found. This is done with the [missing_value] argument, making your spreadsheet cleaner and easier to read.

Related Articles

XLOOKUP vs. VLOOKUP

VLOOKUP vs. Index Match

XLOOKUP vs. Index Match