Understanding Match Mode in Excel’s XLOOKUP Function

The XLOOKUP function in Excel includes an argument for “match mode,” which is crucial for determining how the function matches the lookup value with values in the lookup array.

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

This affects how the function searches for and identifies the value being sought.

Below is a breakdown of the different match modes available in XLOOKUP:

1. Exact Match (0):
   • Syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], 0)
   • Behavior: This default mode searches for an exact match of the lookup value in the lookup array. If an exact match is not found, XLOOKUP returns #N/A.
2. Exact Match or Next Smaller Item (-1):
   • Syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], -1)
   • Behavior: Searches for an exact match; if not found, returns the next smaller item. Assumes the lookup array is sorted in descending order.
3. Exact Match or Next Larger Item (1):
   • Syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], 1)
   • Behavior: Searches for an exact match; if not found, returns the next larger item. Assumes the lookup array is sorted in ascending order.
4. Wildcard Match (2):
   • Syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], 2)
   • Behavior: Allows the use of wildcard characters (* for any number of characters, and ? for a single character) in the lookup value. Useful for finding partial matches.

Selecting the appropriate match mode depends on the nature of your data and the specific requirements of your lookup operation.

For example, exact match modes are typically used for finding specific items, while wildcard matches are helpful for searching patterns or partial text.

The “next smaller” or “next larger” modes are particularly useful in financial or statistical analyses where dealing with ranges or thresholds is common.

Exact Match (0)

This is the default mode used for finding an exact match of the lookup value.

Product Inventory Lookup

Scenario: You have a list of product IDs and their corresponding stock quantities. You want to find the stock quantity for a specific product ID “P123”.

Formula:

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

Result: Looks up “P123” and returns 20.

Explanation: Searches for “P123” in ProductID and returns the corresponding Quantity. If not found, it returns “Product not found”.

Exact Match or Next Smaller Item (-1)

Useful when the lookup array is sorted in descending order, and you want the closest match that is smaller than or equal to the lookup value.

Age Category Assignment

Scenario: You have age brackets and corresponding categories. You want to assign a category based on a person’s age.

Formula:

=XLOOKUP(35, A2:A5, B2:B5, "Below Age Range", -1)

Result: Finds the next smaller bracket for age 35 and returns “Young Adult”.

Explanation: For age 35, the formula finds the next smaller age bracket and returns the corresponding category.

Exact Match or Next Larger Item (1)

Ideal when the lookup array is sorted in ascending order, and you need the closest match that is larger than or equal to the lookup value.

Salary Band Allocation

Scenario: You have salary bands and corresponding titles. You want to allocate a title based on a person’s salary.

Formula:

=XLOOKUP(60000, A2:A5, B2:B5, "Above Salary Range", 1)

Result: Finds the next larger band for a salary of 60000 and returns “Senior”.

Explanation: For a salary of 60000, the formula finds the next larger salary band and returns the corresponding title.

Wildcard Match (2)

Allows the use of wildcard characters (* and ?) for partial matches.

Customer Support Ticket Tagging

Scenario: You have a list of keywords and corresponding tags for support tickets. You want to tag a ticket based on its content.

Formula:

=XLOOKUP("*payment*", A2:A5, B2:B5, "General Inquiry", 2)

Result: Looks for any keyword containing “payment” and assigns the corresponding tag “Payment Problem”.

Explanation: The formula searches for any keyword containing “payment” and assigns the corresponding support tag.

related:

#N/A error in XLOOKUP: Understanding the concept and exploring 5 ways to fix it

XLOOKUP is not working. Try these 5 Proven Solutions.

Top 8 Powerful Excel XLOOKUP Examples: Enhance Your Data Analysis Skills