XLOOKUP function

The XLOOKUP function in Excel is a powerful lookup function that replaces older functions like VLOOKUP, HLOOKUP, and LOOKUP by providing a more flexible and efficient way to search for data in a range or table. XLOOKUP allows you to search for a value in a row or column and return a corresponding value from another row or column.

Syntax

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

Parameters

• lookup_value: The value you want to search for. This is the value that XLOOKUP will look for in the lookup_array.
• lookup_array: The range or array containing the values to search through. It can be a row, column, or array.
• return_array: The range or array that contains the values to return. It must be the same size as the lookup_array.
• [if_not_found] (optional): The value to return if XLOOKUP does not find the lookup_value. If omitted, it defaults to #N/A.
• [match_mode] (optional): Specifies how XLOOKUP matches the lookup_value to the values in lookup_array. You can choose from:
  • 0 for an exact match (default).
  • -1 for an exact match or next smaller item.
  • 1 for an exact match or next larger item.
• [search_mode] (optional): Specifies the direction of the search. You can choose from:
  • 1 to search from the first to the last (default).
  • -1 to search from the last to the first.

How It Works

• XLOOKUP searches for a value in a specified array (lookup_array), and if it finds a match, it returns the corresponding value from another array (return_array).
• You can customize its behavior by specifying an alternative value if the lookup value is not found ([if_not_found]), defining how it should match the lookup value ([match_mode]), and deciding whether the search should be performed from top to bottom or vice versa ([search_mode]).

Examples

Example 1: Basic Lookup

Suppose you have the following data:

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

=XLOOKUP("Banana", A2:A4, B2:B4)

This will return 1.5, which is the price of “Banana”.

Example 2: Lookup with Custom Not Found Message

If the value you search for is not found, you can use the [if_not_found] parameter to return a custom message:

=XLOOKUP("Grapes", A2:A4, B2:B4, "Not Found")

Since “Grapes” is not in the list, this will return "Not Found".

Example 3: Approximate Match

If you want to find an approximate match, such as the closest larger value, use the [match_mode] parameter:

Suppose you have the following data for student scores:

To find the grade for a score of 65, use:

=XLOOKUP(65, A2:A6, B2:B6, "Not Found", 1)

This will return "C", as 65 falls between the scores 60 and 70, and XLOOKUP will return the next largest match.

Example 4: Reverse Search (From Last to First)

If you want to search in reverse order (from the bottom up), use the [search_mode] parameter:

=XLOOKUP("C", B2:B6, A2:A6, "Not Found", 0, -1)

This will search for “C” in the B2:B6 range from the bottom to the top and return the corresponding value from the A2:A6 range.

Key Advantages of XLOOKUP over Older Functions

1. Single Function for All Lookup Needs: Unlike VLOOKUP, which only searches vertically, XLOOKUP can search both vertically and horizontally.
2. Exact Matches: XLOOKUP allows for exact matching by default, unlike VLOOKUP which defaults to approximate matching.
3. Flexible Return: You can return a value from any column or row, not just the ones to the right (as in VLOOKUP).
4. Reverse Search: XLOOKUP allows you to search from the last value to the first with the [search_mode] argument.
5. Error Handling: With the [if_not_found] argument, you can provide a custom message when no match is found, rather than receiving a #N/A error.

Use Case Scenarios

• Replacing VLOOKUP/HLOOKUP: Use XLOOKUP when you need to search for values in a table or range, as it combines the features of VLOOKUP, HLOOKUP, and LOOKUP.
• Dynamic Lookups: When your data changes or if you need to reference a different row or column dynamically.
• Handling Missing Data: With the ability to return a custom message for missing data, XLOOKUP is ideal for more user-friendly reporting.

XLOOKUP is a powerful and flexible lookup function that simplifies many common data search tasks in Excel.