XLOOKUP Basic Approximate Match: How to Use It

XLOOKUP’s approximate match mode finds the closest value that is less than or equal to your lookup value in a sorted list. You enable it by setting the third argument (match_mode) to -1, and it is ideal for tiered tables like tax brackets, shipping rates, or discount scales.

Background: Why Approximate Match Exists

Before XLOOKUP, Excel users relied on VLOOKUP or HLOOKUP with the range_lookup argument set to TRUE to perform approximate matches. That approach had a notorious flaw: if your data was not sorted in ascending order, VLOOKUP would return incorrect or unpredictable results. XLOOKUP was introduced in 2019 (Microsoft 365 and Excel 2021) to modernize lookup functions, and its approximate match mode is more intuitive and safer because it explicitly requires you to choose between exact and approximate matching.

The core idea is simple: when you search for a number that does not exist in your list, approximate match returns the next smallest value. For example, if your table has commission rates for sales amounts of 100, 200, and 300, and you look up 250, the function returns the rate associated with 200. This is perfect for tiered pricing, progressive tax calculations, or any scenario where you want to find which bracket a value falls into.

How XLOOKUP Approximate Match Works

The syntax for XLOOKUP is:

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

The fifth argument, match_mode, is where you control the behavior:

• 0 (default) – exact match
• -1 – exact match or next smaller item (approximate match)
• 1 – exact match or next larger item
• 2 – wildcard match

When you set match_mode to -1, XLOOKUP scans the lookup array for the largest value that is less than or equal to your lookup value. It assumes the data is sorted in ascending order. If the data is not sorted, the results are undefined—Excel may return a wrong value, so always sort your lookup column before using approximate match.

How to Choose Between Exact and Approximate

Use approximate match when you need to categorize a numeric value into a range. Common real-world examples include:

• Income tax brackets (e.g., income $75,000 falls into the 22% bracket)
• Shipping cost by weight (e.g., packages up to 5 kg cost $10, up to 10 kg cost $15)
• Bulk discount tiers (e.g., orders over 100 units get 10% off)
• Grade boundaries (e.g., a score of 85 gets a B if the boundary for A is 90)

If your lookup value is a category name, product ID, or any text that must match exactly, use the default 0. Approximate match only makes sense for numeric thresholds that define steps.

Step-by-Step Example: Shipping Rates

Suppose you have a shipping rate table:

• Weight up to 1 kg: $5
• Weight up to 5 kg: $10
• Weight up to 10 kg: $15
• Weight up to 20 kg: $25

In Excel, you would list the upper limits in column A (1, 5, 10, 20) and the rates in column B. To find the rate for a 7 kg package, use:

=XLOOKUP(7, A2:A5, B2:B5, "No rate", -1)

Since 7 is greater than 5 but less than 10, the function returns the rate for 5 kg, which is $10. If you look up 0.5, it returns $5 because 0.5 is less than 1. If you look up 25, it returns $25 because 25 is greater than 20, and the next smaller value is 20.

Common Problems and How to Avoid Them

The most frequent mistake is forgetting to sort the lookup array in ascending order. If your list is 10, 1, 5, 20, XLOOKUP may return the wrong rate because it cannot reliably find the correct position. Always sort the lookup column before using approximate match.

Another issue is using approximate match when you need exact matching. For example, if you are looking up employee IDs and set match_mode to -1, you might get a result for a different employee if the exact ID is missing. Always double-check that your data type matches your intent.

Also, be aware that XLOOKUP returns the first match when there are duplicates. In approximate match mode, if your lookup value equals a boundary, it returns that boundary’s result, not the next one. For instance, looking up 5 in the shipping table returns $10, not $15.

Comparison to VLOOKUP and INDEX/MATCH

VLOOKUP’s approximate match (TRUE) works similarly but has limitations: it can only look to the right of the lookup column, and it does not handle unsorted data gracefully. INDEX/MATCH with MATCH type 1 is more flexible but requires a more complex formula. XLOOKUP simplifies everything: it can return values from any column, works with arrays, and has a clear match_mode argument.

One advantage XLOOKUP has over VLOOKUP is that it does not require the lookup column to be the first column in the table. You can define a lookup array that is not adjacent to the return array. This makes your formulas more readable and less prone to errors when you insert or delete columns.

Limitations and Edge Cases

Approximate match only works with numeric lookup values. If you try to use it with text, Excel may return a #N/A error or an incorrect result. Also, if your lookup value is smaller than the smallest value in the lookup array, XLOOKUP returns the #N/A error (or your custom if_not_found text). For example, looking up 0 in the shipping table (where the smallest is 1) returns “No rate”.

Another edge case: if your lookup array contains negative numbers, approximate match still works, but you must ensure the array is sorted from most negative to most positive. The function treats the values numerically, not by absolute value.

Frequently Asked Questions

What is the difference between match_mode -1 and 1 in XLOOKUP?

Match_mode -1 returns the exact match or the next smaller value. Match_mode 1 returns the exact match or the next larger value. For example, if you look up 7 in a list of 1, 5, 10, -1 returns 5, while 1 returns 10. Use -1 for typical tiered pricing where you want the lower bracket.

Does XLOOKUP approximate match require sorted data?

Yes, the lookup array must be sorted in ascending order for match_mode -1 to work correctly. If the data is not sorted, the results are unpredictable. This is a key difference from exact match, which does not require sorting.

Can I use approximate match with text values?

No, approximate match is designed for numeric values. For text, use exact match (0) or wildcard match (2). If you attempt approximate match with text, Excel may return #N/A or an incorrect value.

What happens if the lookup value is smaller than the smallest value in the lookup array?

XLOOKUP returns the #N/A error, or your custom if_not_found text if you provided it. For example, in the shipping rate table, looking up 0 returns “No rate” if you set that as the fourth argument.

Is XLOOKUP available in all Excel versions?

XLOOKUP is available in Microsoft 365, Excel 2021, and Excel for the web. It is not available in Excel 2019 or earlier versions. If you use an older version, you can use VLOOKUP with TRUE or INDEX/MATCH with MATCH type 1.

Related

• Disk Write Protected? Fix It in 5 Steps
• Bootstrap Accordion Plus/Minus Icon Toggle
• Prompt Compression: Measure Coding Agent Accuracy & Cost