Google Sheets XLOOKUP Function: Syntax, Examples & Tips

search_key:
The value that you want to search for within the lookup_range. This can be a number, text, or a cell reference.

lookup_range:
The range of cells that you want to search through to find the search_key.

result_range:
The range of cells that contains the values you want to return if a match is found in the lookup_range.

missing_value:
The value to return if the search_key is not found in the lookup_range.

match_mode:
(Optional) The mode that specifies how to match the search_key with the values in the lookup_range. It can be set to exact match, exact match or next smaller, exact match or next larger, etc.

search_mode:
(Optional) The mode that specifies the order in which to search the lookup_range. It can be set to search from first to last or last to first.

What is an XLOOKUP?

The XLOOKUP function in Google Sheets is a powerful tool used to search for a value in a range or array and return a corresponding value from another range or array. It is a more versatile and user-friendly alternative to older lookup functions like VLOOKUP and HLOOKUP. Unlike these older functions, XLOOKUP can search both vertically and horizontally, and it can return multiple results.

In simpler terms, XLOOKUP allows you to find specific data in a table and retrieve related information from that table in a straightforward manner.

A Practical Example

Imagine you are managing a product inventory and sales records stored in an Excel spreadsheet:

Products Table:

Sales Table:

You want to find the price of each product sold in the sales table. If a product has not been sold, you still want to display its price.

XLOOKUP Formula

To achieve this, you would use the XLOOKUP function as follows:

=XLOOKUP(B2, Products!A:A, Products!C:C, ""Not Sold"")

In this formula:

• B2 is the cell containing the ProductID from the Sales Table.
• Products!A:A is the range where the function will look for the ProductID in the Products Table.
• Products!C:C is the range from which the function will return the corresponding Price.
• ""Not Sold"" is the value that will be returned if there is no match found.

Result of the Formula

When you apply the XLOOKUP formula to the Sales Table, the output would look like this:

If you were to add a row for a product that hasn't been sold, such as Widget B, the result would still show its price as follows:

In this case, if you input ProductID 102 in the Sales Table, the XLOOKUP would return ""30.00"" for Widget B.

Why Use XLOOKUP?

XLOOKUP is beneficial because it simplifies the process of looking up data in Excel. It eliminates the need for complex nested functions and provides a more intuitive way to retrieve data. Additionally, it allows for error handling by returning a custom message when no match is found, making your spreadsheets more user-friendly.

Key Takeaways:

• XLOOKUP: A modern Excel function that searches for a value and returns a corresponding value from another range.
• Versatile: Can search both vertically and horizontally, making it suitable for various data layouts.
• Error Handling: Offers the ability to return custom messages for unmatched lookups, enhancing usability.
• Common Use Cases: Ideal for inventory management, sales tracking, and any scenario where data retrieval from related tables is necessary.

XLOOKUP is an essential function for anyone working with data in Excel, providing a powerful way to enhance data analysis and reporting capabilities.

Happy spreadsheeting!