XLOOKUP is a versatile function in Excel used for performing lookups across a range or array. It is a more powerful and flexible replacement for older lookup functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP can search both vertically and horizontally, return results from either side of the lookup value, and handle missing data elegantly.

Syntax of XLOOKUP:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

lookup_value: The value you want to look for.

lookup_array: The range or array where you want to search for the lookup_value.

return_array: The range or array from which you want to return a value.

if_not_found (optional): The value to return if the lookup result is not found.

match_mode (optional):

• 0: Exact match (default).
• -1: Exact match or the next smaller item.
• 1: Exact match or the next larger item.
• 2: A wildcard match (can be used with * and ?).

search_mode (optional):

• 1: Search from the first item to the last (default).
• -1: Search from the last item to the first (useful for reverse searching).

Example Use Cases

1. Basic Lookup

Looking up the price of a product based on the product name.

=XLOOKUP("Product A", A2:A10, B2:B10)

In this case:

• lookup_value: “Product A” (the product you are searching for).
• lookup_array: A2(the column where products are listed).
• return_array: B2(the column where the product prices are listed).

If “Product A” is found in the range A2:A10, Excel will return the corresponding value from B2:B10.

2. Lookup with if_not_found

If the value is not found, a custom message can be displayed:

=XLOOKUP("Product Z", A2:A10, B2:B10, "Not Found")

If “Product Z” is not found in column A, it will return “Not Found” instead of an error.

3. Exact Match or Next Larger Value

If you want to return the next larger value when an exact match is not found:

=XLOOKUP(500, A2:A10, B2:B10, "Not Found", 1)

• match_mode set to 1 means that if the exact value is not found, Excel will return the next larger value.

4. Horizontal Lookup

XLOOKUP can also search across rows instead of columns.

=XLOOKUP("Product B", B1:E1, B2:E2)

This example looks for “Product B” in the row B1:E1 and returns the corresponding value from B2:E2.

5. Searching from the Bottom to the Top

If you want to search the list from the bottom up, you can use the search_mode parameter.

=XLOOKUP("Product A", A2:A10, B2:B10, "Not Found", 0, -1)

• search_mode set to -1 makes the function search from the last row to the first row in the range.

Advantages Over VLOOKUP

• No more left-to-right limitation: XLOOKUP can search and return from any side of the lookup value.
• Defaults to an exact match: Unlike VLOOKUP, which defaults to approximate matches.
• Handles errors gracefully: You can specify an if_not_found argument.
• Supports reverse searching: You can search in reverse order with the search_mode argument.

Example with Data

Let’s say you have a table like this:

You can use XLOOKUP to find the price of “Product B”:

=XLOOKUP("Product B", A2:A4, B2:B4)