Index Match Approximate Match with Multiple Criteria

Yes, you can use INDEX and MATCH for an approximate match with multiple criteria by combining the criteria into a single lookup value and using a MATCH with a lookup array of concatenated values. This technique works in Excel and Google Sheets, letting you find the closest match below a given value (or above, depending on your setup) when more than one condition must be met.

Formula

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

=INDEX(return_range, MATCH(1, (criteria1_range=criteria1) * (criteria2_range=criteria2) * (lookup_range<=lookup_value), 1))

In plain words: you multiply several TRUE/FALSE arrays together (which Excel treats as 1s and 0s) and then match against 1 with match_type set to 1 (approximate). The multiplication creates an array where only rows meeting all criteria are candidates. The MATCH with 1 finds the last row where the product equals 1 and the lookup value is less than or equal to the target.

Variables:

• return_range: the column containing the value you want to return.
• criteria1_range, criteria2_range: columns containing your filter conditions.
• criteria1, criteria2: the specific values you are filtering for.
• lookup_range: the column with numeric values that must be sorted ascending for approximate match to work correctly.
• lookup_value: the numeric value you are comparing against.

This formula must be entered as an array formula in older Excel versions (Ctrl+Shift+Enter). In Excel 365 and Google Sheets, it works as a normal formula due to dynamic arrays.

Worked Example

Suppose you have a pricing table for shipping costs:

• Column A: Zone (A, B, C)
• Column B: Weight (kg)
• Column C: Price ($)

Rows (sorted by Zone then Weight):

• A, 1, 5
• A, 5, 10
• A, 10, 15
• B, 1, 7
• B, 5, 12
• B, 10, 18

You want the price for Zone B with a weight of 3 kg. Since 3 is not in the table, you want the last row with Zone B and weight ≤ 3, which is row 4 (B, 1, 7).

Formula:

=INDEX(C1:C6, MATCH(1, (A1:A6="B") * (B1:B6<=3), 1))

Step-by-step:

1. (A1:A6="B") returns {FALSE;FALSE;FALSE;TRUE;TRUE;TRUE} → {0;0;0;1;1;1}
2. (B1:B6<=3) returns {TRUE;FALSE;FALSE;TRUE;FALSE;FALSE} → {1;0;0;1;0;0}
3. Multiplying gives {0;0;0;1;0;0}
4. MATCH(1, {0;0;0;1;0;0}, 1) finds the position of the last 1, which is 4.
5. INDEX(C1:C6, 4) returns 7.

So the result is $7. The calculator block above shows a different example (pace calculation) but the logic is the same: you are finding a value based on a condition.

Why Approximate Match with Multiple Criteria Is Tricky

The standard VLOOKUP with approximate match only works with one criterion and requires the lookup column to be sorted. When you add multiple criteria, you cannot simply use VLOOKUP. The INDEX/MATCH approach above works because it creates a virtual array where only rows meeting all criteria are marked as 1. However, there is a catch: the MATCH with match_type 1 expects the lookup array to be sorted ascending. When you multiply criteria arrays, the resulting array is not necessarily sorted, so the approximate match may return an incorrect result if the data is not ordered correctly.

The safe way to use this technique is to sort your data by the lookup column (the numeric one) within each criteria group. If you cannot sort, you might need to use an exact match with a helper column that concatenates criteria and lookup values, then use a binary search approach with a sorted helper column.

How to Choose Between Exact and Approximate Match

Use approximate match when you need to find the closest value that is less than or equal to your lookup value. Common scenarios:

• Tax brackets: find the tax rate for a given income.
• Shipping weights: find the price for a weight that falls between table values.
• Discount tiers: find the discount percentage for a purchase amount.
• Grades: find the letter grade for a numeric score.

Use exact match when you need to find an exact value, such as a product ID or a specific date. Exact match does not require sorting and is more forgiving.

If you are unsure, ask yourself: “If the exact value is not in the table, what should happen?” If the answer is “return the next lower value,” use approximate. If the answer is “return an error,” use exact.

Common Problems and Fixes

• #N/A error: The lookup value is smaller than the smallest value in the lookup range, or no row meets all criteria. Check your criteria and the range.
• Wrong result: The data is not sorted by the lookup column within each criteria group. Sort your data by the numeric column (and optionally by criteria columns) before using approximate match.
• Array formula not working: In older Excel, you must press Ctrl+Shift+Enter. In Excel 365 and Google Sheets, it should work automatically.
• Multiple matches: If there are duplicate rows with the same criteria and lookup value, the formula returns the last one. If you need the first, use match_type 0 (exact) instead.
• Text criteria: Ensure text criteria match exactly (case-insensitive by default). Use TRIM and PROPER if needed.

Alternatives to INDEX/MATCH

If the above formula feels fragile, consider these alternatives:

• XLOOKUP (Excel 365): =XLOOKUP(1, (criteria1_range=criteria1) * (criteria2_range=criteria2) * (lookup_range<=lookup_value), return_range, , -1). The -1 tells XLOOKUP to find the next smaller item. This is cleaner and does not require array entry.
• Helper column: Create a column that concatenates all criteria and the lookup value (e.g., =A2&"|"&B2). Then use a regular VLOOKUP or INDEX/MATCH with approximate match on that helper column, ensuring it is sorted.
• FILTER + MAX: =MAX(FILTER(return_range, (criteria1_range=criteria1) * (criteria2_range=criteria2) * (lookup_range<=lookup_value))). This returns the maximum value that meets all criteria, which works if your return values are numeric and you want the largest one.
• Power Query: For complex data, you can merge tables with a “less than or equal to” join, but that is more advanced.

Frequently Asked Questions

Can I use approximate match with multiple criteria in Google Sheets?

Yes, the same INDEX/MATCH array formula works in Google Sheets. You do not need to press Ctrl+Shift+Enter; it works automatically. Alternatively, you can use FILTER with MAX or the newer XLOOKUP (available in Google Sheets as of 2022).

Why does my formula return the wrong value even though the data is sorted?

Check that the data is sorted by the lookup column within each criteria group. For example, if you have Zone A and Zone B, all Zone A rows must be sorted by weight, and all Zone B rows must be sorted by weight. If the zones are interleaved, the approximate match can fail. Also ensure there are no blank cells in the criteria or lookup ranges.

What is the difference between match_type 1 and match_type -1 in MATCH?

Match_type 1 finds the largest value that is less than or equal to the lookup value (requires ascending sort). Match_type -1 finds the smallest value that is greater than or equal to the lookup value (requires descending sort). For most pricing/tier tables, you want match_type 1.

Can I use approximate match with text criteria?

Yes, but the text criteria must be exact (case-insensitive). The numeric lookup column still needs to be sorted. For example, finding the price for a specific product category and weight works the same way.

How do I handle multiple criteria where one is a date?

Dates are stored as numbers in Excel, so they work in the lookup range. Ensure your date column is sorted ascending. If you need to find the last date before a target date, use the same formula with the date column as the lookup_range.

Is there a way to avoid sorting the data?

Yes, use XLOOKUP with the -1 search mode (if available) or use an array formula with MAX/FILTER. These do not require sorting because they evaluate all rows. However, they may be slower on very large datasets.

Related

• ThinkPad X1 vs IdeaPad: Which Lenovo Line to Pick
• Uninstall Office Suite: How to Remove It Fully
• OnePlus 13 Series Magnetic Charging Support

in t | Target time | min | 200 | 0 | 1440
in d | Distance | km | 10 | 0.1 | 1000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2