XLOOKUP Approximate Match with Multiple Criteria

XLOOKUP can perform an approximate match with multiple criteria by nesting XLOOKUP functions or using boolean logic to combine conditions into a single lookup value. This article explains how to build such formulas, when to use them, and common pitfalls to avoid.

Formula

The core formula for an approximate match with multiple criteria is:

=XLOOKUP(1, (criteria_range1=criteria1) * (criteria_range2=criteria2) * (lookup_range<=lookup_value), return_range, , -1)

Here, the product of boolean arrays creates a single array where 1 means all criteria match. The lookup value is 1, and the match mode -1 (exact or next smaller) finds the largest row that satisfies all conditions. Each variable represents:

• criteria_range1, criteria_range2 – columns containing the criteria to match exactly.
• criteria1, criteria2 – the specific values you want to match.
• lookup_range – the column containing the numeric value for the approximate match (e.g., score, date, quantity).
• lookup_value – the numeric value you are looking up (e.g., a test score of 85).
• return_range – the column with the result you want to return.

Worked Example

Suppose you have a pricing table with columns: Product (A), Quantity (B), Price per unit (C). You want to find the price for Product “Widget” with a quantity of 150. The table is sorted by Quantity ascending within each Product.

Using the default values from the calculator block (which are unrelated to this example, but the logic is the same), the formula would be:

=XLOOKUP(1, (A2:A100="Widget") * (B2:B100<=150), C2:C100, , -1)

Step by step:

1. The expression (A2:A100="Widget") returns an array of TRUE/FALSE.
2. The expression (B2:B100<=150) returns an array of TRUE/FALSE.
3. Multiplying them converts TRUE to 1 and FALSE to 0, producing an array with 1 only where both conditions are TRUE.
4. XLOOKUP searches for the value 1 in that array, using match mode -1 (exact or next smaller). Since the array contains only 0s and 1s, it finds the last 1 before a 0 (or the first 1 if all are 1s).
5. The function returns the corresponding value from C2:C100.

If the table has quantities 100, 200, 300 for Widget, the formula returns the price for quantity 100 because 150 is less than 200, and 100 is the next smaller value.

How XLOOKUP Approximate Match Works

XLOOKUP’s match mode argument has four options:

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

For approximate matches, the lookup array must be sorted in ascending order (if using -1) or descending order (if using 1). However, when using the boolean multiplication technique, the array is not sorted—it’s a binary array of 0s and 1s. XLOOKUP still works because it finds the last 1 before a 0 when scanning from top to bottom. This is a subtle but critical point: the boolean technique does not require the entire table to be sorted, only that within each criteria group the lookup_range is sorted ascending.

When to Use This Technique

This approach is ideal for tiered pricing, tax brackets, shipping rates, or any scenario where you need to find a value based on a numeric threshold and one or more categorical filters. For example:

• Finding the shipping cost for a zone (East/West) and weight (up to 10 kg, 20 kg, etc.).
• Finding the commission rate for a salesperson’s region and sales amount.
• Finding the discount percentage for a product category and order quantity.

It is particularly useful when you cannot sort the entire table because the criteria columns are not sorted together. The boolean array approach bypasses the sorting requirement for the criteria columns.

Common Problems and Solutions

Several issues frequently arise with this formula:

• #N/A error – This occurs when no row meets all criteria. Check that the criteria values exist and that the lookup value is within the range of the lookup_range.
• Wrong result due to unsorted lookup_range – Even though the criteria columns don’t need sorting, the lookup_range within each criteria group must be sorted ascending for -1 to work correctly. If not, you may get an incorrect match.
• Using whole columns (A:A) – This can slow down the calculation. Use a defined range like A2:A1000.
• Forgetting to press Ctrl+Shift+Enter – In older Excel versions, array formulas require this. In Excel 365 and Excel 2021, dynamic arrays handle it automatically.
• Mixing text and numbers – Ensure criteria values are the same data type (e.g., text vs. number).

Alternatives to XLOOKUP

If you are using an older Excel version, you can achieve the same result with INDEX/MATCH:

=INDEX(return_range, MATCH(1, (criteria_range1=criteria1) * (criteria_range2=criteria2) * (lookup_range<=lookup_value), 1))

This uses MATCH with match type 1 (exact or next smaller), but it requires the lookup_range to be sorted ascending. Another alternative is to use the FILTER function (Excel 365) to create a filtered array and then use MAX or MIN to find the appropriate value. However, XLOOKUP is simpler and more readable.

Tips for Building the Formula

Here are practical tips to make the formula easier to build and maintain:

• Use named ranges or Excel Tables to make the formula readable.
• Test each boolean expression separately by entering it in a cell (e.g., =A2:A100="Widget") to verify it returns the expected TRUE/FALSE array.
• Use the Evaluate Formula tool (Formulas tab > Evaluate Formula) to step through the calculation.
• If you have many criteria, consider using a helper column that concatenates the criteria values, then use a simple XLOOKUP with exact match on that helper column.

Frequently Asked Questions

Can I use approximate match with multiple criteria without sorting?
Yes, the boolean multiplication technique does not require the criteria columns to be sorted. However, the numeric lookup_range must be sorted within each criteria group for the approximate match to return the correct result.

Why does my XLOOKUP return the wrong value?
Most often it’s because the lookup_range is not sorted ascending. Even if the criteria columns are unsorted, the numeric column must be sorted within each group. Also check that you are using match mode -1, not 0.

What does the -1 in the match mode argument do?
It tells XLOOKUP to return the exact match if found, otherwise the next smaller value. In the boolean array, this means it finds the last row where all criteria are TRUE and the lookup value is less than or equal to the threshold.

Can I use this with wildcards?
No, wildcard matching (match mode 2) only works with exact match, not with the boolean technique. If you need wildcards, you must use a different approach, such as FILTER or a helper column.

Is there a limit to the number of criteria?
No, you can multiply as many boolean arrays as needed. However, performance may degrade with very large datasets (over 100,000 rows). In such cases, consider using Power Query or a database.

Related

• Project Blue Lock 2 Codes? Real Status & How to Get Them
• How to Use Kami with Google Classroom
• RPCS3 Settings for Stable Performance & Fewer Stutters

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2