Approximate Match Lookups: Step-by-Step Guide

Updated 14 November 2026 · Standard formula, shown below

This guide shows you how to find the closest match to a value in a table when an exact match doesn’t exist. You’ll learn the standard Excel and Google Sheets technique using INDEX and MATCH with match type 1, which handles numeric thresholds like tax brackets, shipping tiers, or discount levels. No special add-ins are needed—just a sorted lookup column and a basic understanding of the two functions.

What You’ll Need

A spreadsheet application (Excel 2010 or later, or Google Sheets)
A lookup table with two columns: the threshold values (must be sorted ascending) and the corresponding result values
The value you want to look up (the search value)

Step-by-Step

Sort your lookup table ascending. The first column (the one containing the values you compare against) must be sorted from smallest to largest. This is non-negotiable for approximate matching. If your table isn’t sorted, select the entire table, go to Data > Sort, and sort by that column ascending.
Identify your search value. Decide which cell contains the number you want to find a match for. It could be a typed number, a cell reference, or a formula result.
Understand the MATCH function. The syntax is MATCH(lookup_value, lookup_array, match_type). For approximate matches, set match_type to 1. This tells the function to find the largest value in the lookup array that is less than or equal to the lookup value.
Write the MATCH part. In a new cell, type =MATCH(search_value, threshold_range, 1). For example, if your search value is in cell E2 and your thresholds are in A2:A10, you’d write =MATCH(E2, A2:A10, 1). This returns the position (row number within the range) of the closest match.
Add the INDEX function. Now wrap MATCH inside INDEX to pull the actual result from the second column. The syntax is INDEX(result_range, row_number). So the full formula becomes =INDEX(B2:B10, MATCH(E2, A2:A10, 1)), where B2:B10 is the column with the results you want returned.
Press Enter and test. The cell should now show the result that corresponds to the closest threshold at or below your search value. Test with a value that exactly matches a threshold, one that falls between two thresholds, and one that is below the smallest threshold.
Handle values below the first threshold. If your search value is smaller than the smallest threshold, MATCH returns an error (#N/A). To handle this, wrap the formula in IFERROR. For example: =IFERROR(INDEX(B2:B10, MATCH(E2, A2:A10, 1)), "Below range").

Common Mistakes

Forgetting to sort ascending. This is the most frequent error. If the lookup column isn’t sorted from smallest to largest, MATCH with match type 1 returns wrong results or #N/A.
Using match type 0. Match type 0 is for exact matches only. If you use 0, you’ll get #N/A whenever an exact match doesn’t exist.
Mixing up column order. Ensure the threshold column is the first argument in MATCH and the result column is the first argument in INDEX. Swapping them returns the wrong data.
Including header rows in the ranges. If your table has a header, don’t include it in the MATCH or INDEX ranges. Otherwise, the row numbers shift and results will be off by one.
Using text values. Approximate matching only works reliably with numbers. Text values won’t sort correctly and will produce unpredictable results.

Tips

Use named ranges. Instead of referencing A2:A10, name the threshold range “Thresholds” and the result range “Results”. This makes formulas easier to read and maintain.
Lock your ranges. If you copy the formula down, use absolute references ($A$2:$A$10) so the ranges don’t shift.
Combine with other functions. You can nest this formula inside IF, SUM, or other functions to build more complex logic, like applying a tiered rate to a value.
For exact matches with fallback. If you want an exact match first and an approximate match only if no exact exists, use IFERROR(INDEX(..., MATCH(value, range, 0)), INDEX(..., MATCH(value, range, 1))).
Use XLOOKUP (Excel 365/2021). If you have a newer version, XLOOKUP(value, threshold_range, result_range, , -1) does the same thing more simply. The fourth argument (empty) means no exact match required, and -1 means “next smaller”.

Troubleshooting

Getting #N/A. First, check your sort order. Then verify the search value is numeric, not text. If the value is below the smallest threshold, that’s expected—use IFERROR to handle it.
Getting #VALUE!. This usually means your ranges are different sizes. Ensure the threshold range and result range have the same number of rows.
Getting #REF!. This means the row number returned by MATCH is outside the result range. Check that both ranges start on the same row.
Results are off by one row. You likely included a header row in your ranges. Adjust the ranges to start at the first data row.
Wrong result for a value that exactly matches a threshold. That’s correct behavior—approximate match returns the exact match when it exists. If you expected the next row up, you need to adjust your table design.

Frequently Asked Questions

What does match type 1 actually do?

It finds the largest value in the lookup array that is less than or equal to the lookup value. It assumes the array is sorted ascending. If the lookup value is smaller than the first element, it returns #N/A.

Can I use this for text or dates?

Dates are stored as numbers in Excel, so they work fine. Text does not work reliably because alphabetical sorting isn’t the same as numeric sorting for approximate matches. Use exact match (type 0) for text.

What if my table isn’t sorted?

You must sort it ascending, or the results will be unpredictable. There’s no way around this with MATCH type 1. If you can’t sort, use XLOOKUP with the -1 option, which also requires sorted data.

How do I get the next larger value instead of the next smaller?

Use match type -1 instead of 1. This finds the smallest value greater than or equal to the lookup value. Your table must still be sorted ascending.

Why does my formula work in Excel but not Google Sheets?

The syntax is identical. The most common issue is that Google Sheets may treat a cell with a number stored as text differently. Ensure your search value is formatted as a number, not text.

Can I use approximate match with multiple criteria?

Not directly with MATCH. You’d need to combine it with an array formula or use SUMPRODUCT. For most cases, it’s simpler to create a helper column that concatenates the criteria and use exact match on that.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.