Skip to main content

Xlookup (office 365 only)

Source Metadata​

  • Tested: No
  • Author: OPEN AI
  • Category: Function & Formula
  • Created time: March 17, 2025 1:11 PM
  • Source: AI

Elaborate xlookup

XLOOKUP is a powerful and flexible function introduced in Excel 365 and Google Sheets (in 2021) that is designed to replace older lookup functions like VLOOKUP, HLOOKUP, and LOOKUP. It overcomes many of the limitations of those functions and provides more features for performing lookups in a straightforward way. XLOOKUP can search for values in both vertical and horizontal ranges and supports exact or approximate matches.

Syntax of XLOOKUP:​

=XLOOKUP(search_key, search_range, return_range, [if_not_found], [match_mode], [search_mode])

Parameters:​

  1. search_key: The value you want to look up (similar to VLOOKUP's search key).
  2. search_range: The range where you want to search for the search_key. It can be a column or row.
  3. return_range: The range from which to return the corresponding value. The return_range must be the same size as search_range.
  4. if_not_found (optional): A value to return if the search_key is not found. If omitted, it defaults to #N/A.
  5. match_mode (optional):
    • 0 (Exact match): The default setting.
    • 1 (Exact match or next larger item): For approximate matches, used when data is sorted in ascending order.
    • 1 (Exact match or next smaller item): For approximate matches, used when data is sorted in descending order.
  6. search_mode (optional):
    • 1 (Search from the top to bottom or left to right): The default search direction.
    • 1 (Search from bottom to top or right to left): Useful for reverse lookups.

Key Benefits of XLOOKUP:​

  1. Bidirectional Lookup:
    • Unlike VLOOKUP, which can only search for values in the first column, XLOOKUP allows you to search in any column or row and return values from any other column or row, whether it's to the left, right, or anywhere in between.
  2. Exact or Approximate Match:
    • XLOOKUP automatically supports both exact and approximate matches, making it more versatile than VLOOKUP or HLOOKUP. You can choose whether you want an exact match or the next largest/smallest match without needing to sort the data.
  3. Easier Syntax:
    • The syntax of XLOOKUP is simpler and less error-prone. For example, there's no need for an index number (like in VLOOKUP), and the return range is explicitly defined.
  4. Can Handle Missing Values:
    • XLOOKUP allows you to specify a value to return if the search key is not found, which avoids the #N/A error commonly encountered in VLOOKUP and HLOOKUP.
  5. Improved Flexibility:
    • XLOOKUP can search in either vertical or horizontal data ranges, whereas VLOOKUP is limited to vertical lookups and HLOOKUP to horizontal ones.

Examples of XLOOKUP:​

1. Basic Exact Match Lookup:​

Imagine you have a product table with ProductID in column A and Price in column B. You want to find the price of a product with ProductID = 103.

AB
ProductIDPrice
10110
10220
10330
10440

You can use XLOOKUP to find the price for ProductID 103:

=XLOOKUP(103, A2:A5, B2:B5)

Explanation:​

  • search_key: 103 (the product ID you are searching for).
  • search_range: A2:A5 (the range where ProductID values are located).
  • return_range: B2:B5 (the range from which the price will be returned).

Result:​

The result will be 30, as that is the price corresponding to ProductID = 103.

2. Lookup with Custom Message for Missing Values:​

Suppose you have the same table and want to look for ProductID = 106, which does not exist in the list. You can specify a custom message using the if_not_found parameter:

=XLOOKUP(106, A2:A5, B2:B5, "Product not found")

Explanation:​

  • if_not_found: "Product not found" (This is the value returned if ProductID = 106 is not found.)

Result:​

The result will be "Product not found" because ProductID = 106 does not exist in the list.

3. Approximate Match Lookup (Next Larger Value):​

Let's say you have a tax bracket table where income is in column A and tax rate is in column B. You want to find the applicable tax rate for an income of 35000.

AB
IncomeTax Rate
05%
2000010%
5000015%
10000020%

You can use XLOOKUP with the approximate match setting:

=XLOOKUP(35000, A2:A5, B2:B5, , 1)

Explanation:​

  • search_key: 35000 (the income for which you want to find the tax rate).
  • search_range: A2:A5 (the income range).
  • return_range: B2:B5 (the tax rate range).
  • match_mode: 1 (Approximate match returns the next larger value if an exact match is not found.)

Result:​

The result will be 10%, as 35000 falls between 20000 and 50000, and the next larger value is 50000, which has a tax rate of 10%.

4. Reverse Lookup (Search from Bottom to Top):​

Let's say you have a list of product names and their quantities, but the list is sorted in reverse order. If you want to find the quantity of a product called "Banana", you can use XLOOKUP to search from bottom to top.

AB
ProductQuantity
Mango30
Orange20
Banana10
Apple50

To search for "Banana" starting from the bottom:

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

Explanation:​

  • search_key: "Banana" (the product you want to find).
  • search_range: A2:A5 (the list of products).
  • return_range: B2:B5 (the list of quantities).
  • search_mode: 1 (Search from bottom to top).

Result:​

The result will be 10, as that is the quantity of "Banana", and XLOOKUP will search in reverse order.

Advantages of XLOOKUP:​

  1. Bidirectional Lookup: Unlike VLOOKUP or HLOOKUP, XLOOKUP can search both to the left and to the right of the search column.
  2. No Need for Index Numbers: In VLOOKUP, you have to specify the column index number, but XLOOKUP directly references the return range.
  3. Simpler Syntax: XLOOKUP is easier to read and use because you don't need to specify an index or worry about rearranging columns.
  4. Exact and Approximate Matches: Handles both exact and approximate matches easily with just one function.
  5. Custom Error Handling: You can specify what to return when the search key isn't found, rather than getting an error.
  6. Search Direction Flexibility: You can search from the top to bottom (default) or bottom to top using the search_mode parameter.

Conclusion:​

XLOOKUP is an improved version of traditional lookup functions, providing a flexible, powerful way to search data in both vertical and horizontal ranges. It simplifies many tasks that would otherwise require multiple functions (e.g., VLOOKUP and MATCH) and enhances flexibility with support for reverse lookups and customizable error messages.

Let me know if you'd like further details or more examples on how to use XLOOKUP!

Explain benefit and limitation and find alternative

XLOOKUP is a modern, versatile lookup function in Excel and Google Sheets that has many benefits but also some limitations. Its designed to replace older lookup functions like VLOOKUP, HLOOKUP, and LOOKUP. Below is a detailed explanation of its benefits, limitations, and alternatives.

Benefits of XLOOKUP:​

  1. Bidirectional Lookup:
    • Benefit: Unlike VLOOKUP or HLOOKUP, which are limited to searching in one direction (vertical for VLOOKUP, horizontal for HLOOKUP), XLOOKUP allows you to search both vertically and horizontally. It can search for a value anywhere in a row or column, making it highly versatile.
    • Example: You can search in a column and return a value from any column to the left or right, which was previously a limitation of VLOOKUP.
  2. Simplified Syntax:
    • Benefit: XLOOKUP eliminates the need to specify column index numbers, unlike VLOOKUP. This makes the formula easier to understand and maintain. You simply specify the search range and the return range.
    • Example: Instead of VLOOKUP(search_key, range, column_index, FALSE), XLOOKUP(search_key, search_range, return_range) is much simpler.
  3. Exact and Approximate Matches:
    • Benefit: XLOOKUP supports both exact and approximate matches. For approximate matches, it also allows searching for the next larger or next smaller match, which was possible only with specific settings in VLOOKUP or HLOOKUP.
    • Example: You can use XLOOKUP to find the closest match in a price list or tax table with a simple formula.
  4. Flexible Error Handling:
    • Benefit: The if_not_found argument in XLOOKUP allows you to return a custom value if the search key is not found. This eliminates the need for error-handling functions like IFERROR when the result is not found.
    • Example: You can return a custom message like "Not Found" instead of #N/A.
  5. No Need for Sorted Data:
    • Benefit: XLOOKUP does not require the data to be sorted in any order (unlike VLOOKUP with approximate matches, which requires sorted data). This makes XLOOKUP more reliable in unsorted datasets.
  6. Search from Bottom to Top or Right to Left:
    • Benefit: XLOOKUP allows you to specify the search direction (from top to bottom or bottom to top, left to right or right to left). This is useful for reverse lookups, which would otherwise require additional functions or formulas.
    • Example: If your dataset is organized in reverse order, you can search from bottom to top to find the most recent or last entry.
  7. Supports Dynamic Array:
    • Benefit: XLOOKUP works seamlessly with dynamic arrays, allowing it to return multiple results in a single formula, without needing to drag the formula down or across. This makes it more efficient for large datasets.

Limitations of XLOOKUP:​

  1. Availability:
    • Limitation: XLOOKUP is available in Excel 365 and Excel 2021, but it is not available in older versions of Excel or earlier versions of Google Sheets. This limits its use to only those using the latest versions of these applications.
    • Solution: Users on older versions of Excel or Google Sheets will have to rely on alternatives like VLOOKUP or INDEX + MATCH.
  2. No Support for Wildcards in Lookup Value:
    • Limitation: Unlike VLOOKUP, XLOOKUP does not support wildcards (e.g., or ?) directly for searching. If you need advanced text matching, you may have to combine XLOOKUP with other functions or resort to MATCH with wildcards.
    • Solution: If wildcards are essential, consider using INDEX + MATCH with wildcard support.
  3. Overkill for Simple Lookups:
    • Limitation: XLOOKUP is a very powerful function, but for basic, simple lookups (like looking up one value in a column), it can be overkill. VLOOKUP or HLOOKUP may be faster and simpler in these cases.
    • Solution: For straightforward lookups, VLOOKUP or HLOOKUP may still be sufficient and easier to use.
  4. Complexity in Multi-Condition Lookups:
    • Limitation: XLOOKUP is designed to look up one condition at a time. For multi-condition lookups, you would need to use other techniques or combine XLOOKUP with other functions (such as IF or AND).
    • Solution: Use a combination of XLOOKUP with FILTER or INDEX + MATCH for multi-condition lookups.

Alternatives to XLOOKUP:​

While XLOOKUP is a powerful and flexible function, there are several alternatives, depending on the task you want to accomplish.

  1. VLOOKUP:

    • When to Use: VLOOKUP is still a common choice when you have simple vertical lookups and your data is structured in such a way that the search key is in the first column.
    • Limitation: VLOOKUP can only search for values in the first column and return values to the right. It also doesnt support bidirectional lookups or reverse lookups.

  2. HLOOKUP:

    • When to Use: Use HLOOKUP when your data is organized horizontally (i.e., lookup in rows instead of columns).
    • Limitation: Like VLOOKUP, HLOOKUP can only search for values in the first row and return values below it. It also lacks the flexibility of XLOOKUP.

  3. INDEX + MATCH:

    • When to Use: INDEX + MATCH is a highly flexible combination that allows you to perform lookups in any direction (not just from left to right). It is particularly useful for complex lookups, including reverse lookups, and can handle multiple conditions.
    • Limitation: INDEX + MATCH requires multiple functions and can be more difficult for beginners to use compared to XLOOKUP.

    Example of INDEX + MATCH:

    =INDEX(B2:B5, MATCH("Banana", A2:A5, 0))

    This searches for "Banana" in column A and returns the corresponding value from column B.

  4. LOOKUP:

    • When to Use: LOOKUP is a simpler alternative to XLOOKUP for basic lookups, and it works in both vertical and horizontal ranges.
    • Limitation: LOOKUP only works when the data is sorted and has fewer options for error handling.

  5. FILTER:

    • When to Use: Use FILTER when you need to return multiple matching values based on conditions. FILTER can return an array of results, making it very useful for tasks where multiple values might match the condition.
    • Limitation: Its best used when you want to return multiple results, but it's more complex to use when only a single result is needed.

Conclusion:​

FunctionBenefitsLimitations
XLOOKUPFlexible, bidirectional lookup, supports exact/approximate match, error handling, no need for sorted dataNot available in older versions of Excel/Sheets, no wildcard support
VLOOKUPSimple, easy to use for vertical lookupsCan only search left to right, requires index numbers, limited error handling
HLOOKUPWorks for horizontal lookupsLimited to searching left to right, requires index numbers, error handling issues
INDEX + MATCHHighly flexible, works with any column, reverse lookupsMore complex syntax, requires multiple functions
LOOKUPSimple, works for both vertical and horizontal rangesLimited functionality, requires sorted data
FILTERGreat for multiple matching results, flexible conditionsCan be more complex, better suite

When to Use XLOOKUP:​

  • Use XLOOKUP when you need a modern and flexible lookup function that works for both vertical and horizontal data, provides exact and approximate matches, and handles errors well. Its ideal for complex lookups and when you want a simpler, more readable formula than using INDEX + MATCH.

When to Use Alternatives:​

  • Use VLOOKUP for simpler lookups when you dont need the flexibility of XLOOKUP.
  • Use INDEX + MATCH for more complex lookups, especially when dealing with large datasets or when searching in both directions.
  • Use FILTER when you need to return multiple results that match a condition.

Let me know if you need more detailed examples or specific guidance on how to use these functions!

Google sheet can use xlookup?

As of now, Google Sheets does not natively support the XLOOKUP function, which is available in Excel 365 and Excel 2021. However, Google Sheets provides powerful alternatives like VLOOKUP, INDEX + MATCH, and FILTER, which can be used to achieve similar functionality.

Heres a quick breakdown of how you can replicate some of the functionality of XLOOKUP in Google Sheets:

Alternatives to XLOOKUP in Google Sheets:​

  1. VLOOKUP:

    • Use Case: When you need to search for a value in the first column of a range and return a value from another column to the right.
    • Limitation: It only supports vertical lookups and requires the search key to be in the first column of the range.

    Example:

    =VLOOKUP("Apple", A2:B10, 2, FALSE)

  2. INDEX + MATCH:

    • Use Case: This combination is a flexible alternative for both vertical and horizontal lookups. INDEX returns the value of a cell in a specific row and column, while MATCH finds the position of a specific value in a range. This combo is more versatile than VLOOKUP, as it allows searching in any direction (left or right).

    Example:

    =INDEX(B2:B10, MATCH("Apple", A2:A10, 0))

    • Explanation: MATCH("Apple", A2:A10, 0) finds the row number where "Apple" is located in column A. Then, INDEX(B2:B10, ...) retrieves the corresponding value from column B.

  3. FILTER:

    • Use Case: The FILTER function can be used when you want to return multiple values that meet certain conditions, which is a useful feature when XLOOKUP returns multiple results.

    Example:

    =FILTER(B2:B10, A2:A10 = "Apple")

    • Explanation: This will return all the values from column B where the corresponding value in column A is "Apple".

  4. QUERY:

    • Use Case: The QUERY function is another alternative for retrieving data based on conditions. It is similar to SQL and can return rows or columns based on more complex conditions.

    Example:

    =QUERY(A2:B10, "SELECT B WHERE A = 'Apple'", 0)

    • Explanation: This will select the corresponding values from column B where column A is "Apple".

Replicating XLOOKUP in Google Sheets:​

While Google Sheets does not support XLOOKUP directly, you can combine the functions mentioned above to replicate its functionality.

Example: Bidirectional Lookup​

If you wanted to look up a value in column A and return a value from column B (similar to XLOOKUP), you can use VLOOKUP or INDEX + MATCH:

=VLOOKUP("Apple", A2:B10, 2, FALSE)

For a reverse lookup (i.e., looking up in column B and returning the corresponding value in column A), use:

=INDEX(A2:A10, MATCH("Apple", B2:B10, 0))

Summary:​

While Google Sheets doesnt have the XLOOKUP function, you can achieve similar results using VLOOKUP, INDEX + MATCH, FILTER, and QUERY. These functions are very powerful and can handle complex lookups, whether vertical or horizontal.

Let me know if youd like more examples or if you need further clarification on how to use these functions in your own sheets!