XLOOKUP() in Advanced Excel (Excel 365)

The XLOOKUP() function is a powerful feature introduced in Excel 365 and Excel 2021. It is designed to replace older lookup functions like VLOOKUP() and HLOOKUP(). The XLOOKUP() function simplifies the process of searching for a value in a table or range, allowing for more flexibility and ease of use.

XLOOKUP() Function

The XLOOKUP() function allows you to search for a value in a range or table and return a corresponding value from another column or row. It is more powerful than the traditional lookup functions because it can search in any direction (horizontal or vertical), and it doesn’t require sorting of the data.

XLOOKUP() Syntax

Syntax:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • lookup_value: The value to search for in the lookup_array.
  • lookup_array: The range or array containing the values to search for.
  • return_array: The range or array containing the values to return based on the lookup_value.
  • [if_not_found]: Optional. The value to return if no match is found (default is #N/A).
  • [match_mode]: Optional. Specifies how the match is performed (0 for exact match, 1 for exact or next smaller, -1 for exact or next larger, and 2 for a wildcard match).
  • [search_mode]: Optional. Specifies the direction of the search (1 for search from first to last, -1 for search from last to first).

Real-Time Example of XLOOKUP()

Scenario: You have a list of products and their prices, and you want to find the price of a specific product.

        Product      Price
        Apple        1.5
        Banana       0.8
        Orange       1.2
        Mango        2.0

Steps:

  1. In cell A1, enter the name of the product you want to find (e.g., "Orange").
  2. In cell B1, use the following formula to find the price of the product:
    3. =XLOOKUP(A1, A2:A5, B2:B5)
  3. Explanation: The function searches for the value in cell A1 ("Orange") in the range A2:A5 (the list of products) and returns the corresponding value from the range B2:B5 (the prices). The result will be "1.2", which is the price of Orange.

Using XLOOKUP() with Exact Match

Scenario: You want to look up an employee's department based on their employee ID.

        Employee ID  Department
        101          Sales
        102          Marketing
        103          HR
        104          Finance

Steps:

  1. In cell D1, enter the employee ID you want to look up (e.g., "102").
  2. In cell E1, use the following formula to find the department for the employee ID:
    3. =XLOOKUP(D1, A2:A5, B2:B5, "Not Found")
  3. Explanation: The function searches for the value in D1 ("102") in the range A2:A5 (the employee IDs) and returns the corresponding department from B2:B5. If no match is found, it returns "Not Found". The result will be "Marketing".

Using XLOOKUP() for Approximate Match

The XLOOKUP() function can also perform approximate matches, making it a versatile tool for looking up data such as grades, tax rates, or commission structures.

Scenario: You have a list of sales figures and corresponding commission rates, and you want to find the commission rate based on a sales figure.

        Sales       Commission Rate
        1000        5%
        2000        7%
        3000        10%
        4000        12%

Steps:

  1. In cell D1, enter the sales figure you want to look up (e.g., "2500").
  2. In cell E1, use the following formula to find the commission rate for the sales figure:
    3. =XLOOKUP(D1, A2:A5, B2:B5, "No Rate", 1)
  3. Explanation: The function searches for the sales figure (2500) in the range A2:A5 (sales values) and returns the corresponding commission rate from B2:B5. The match mode is set to 1, meaning it will return the next smaller value if an exact match is not found. The result will be "7%", as the next smaller value to 2500 is 2000, which corresponds to a 7% commission rate.

Using XLOOKUP() for Reverse Search

One of the advantages of XLOOKUP() is that it can search in any direction, unlike VLOOKUP() and HLOOKUP(), which are limited to searching vertically or horizontally.

Scenario: You want to look up a department based on the employee name.

        Employee     Department
        John         Sales
        Sarah        Marketing
        Peter        HR
        Jane         Finance

Steps:

  1. In cell D1, enter the employee name you want to look up (e.g., "Peter").
  2. In cell E1, use the following formula to find the department for the employee:
    3. =XLOOKUP(D1, A2:A5, B2:B5)
  3. Explanation: The function searches for the value in D1 ("Peter") in the range A2:A5 (the employee names) and returns the corresponding department from B2:B5. The result will be "HR".

Key Benefits of XLOOKUP()

  • Search in Both Directions: Unlike VLOOKUP() and HLOOKUP(), XLOOKUP() can search both vertically and horizontally, providing more flexibility.
  • Exact or Approximate Match: You can perform exact or approximate matches, allowing for more customized lookups.
  • No Need for Sorted Data: Unlike older lookup functions, XLOOKUP() does not require the data to be sorted.
  • Simplified Syntax: XLOOKUP() has a simpler and more intuitive syntax compared to VLOOKUP() and HLOOKUP(), making it easier to use and troubleshoot.

Conclusion

The XLOOKUP() function is a significant improvement over older lookup functions like VLOOKUP() and HLOOKUP(). It offers more flexibility, simpler syntax, and greater functionality, making it a powerful tool for anyone working with data in Excel 365. By mastering XLOOKUP(), you can perform more complex lookups and analyses in a much easier and more efficient way.




Advertisement