Approximate Match Lookup in Excel: Formulas and Tips

Formulas & functions · Updated November 21, 2026

For a numeric approximate lookup in Excel, use an ascending threshold table and a formula that returns the largest threshold not greater than the lookup value. In Microsoft 365, with thresholds in A2:A5, results in B2:B5, and the value to look up in D2, enter =XLOOKUP(D2,A2:A5,B2:B5,, -1) (remove the space after the comma if copying: =XLOOKUP(D2,A2:A5,B2:B5,,-1)). For an older-style lookup, use =VLOOKUP(D2,A2:B5,2,TRUE); its first column must be sorted ascending. “Approximate match” can also mean finding similar text, which is a different task.

What approximate match means in Excel

An approximate lookup does not necessarily find a value that is numerically closest in absolute distance. In the common threshold-table setup, it finds the applicable boundary: the greatest threshold that is less than or equal to the lookup value. For example, a score of 76 belongs in the band beginning at 70, not the band beginning at 80. This is useful when a value falls between listed breakpoints and you want to return the rate, category, commission tier, or grade assigned to that interval.

The phrase is also used for fuzzy text matching, such as matching “Jon Smyth” with “John Smith.” A normal approximate numeric lookup does not do that. Excel has separate tools for text searches and fuzzy merging, and the right choice depends on whether the problem is a range of numbers, a text pattern, or inconsistent names and descriptions.

Build a threshold table

Put the lower boundary of each band in the first column and the answer to return beside it. For a grading scale, a compact table might look like this:

  • 0 — F
  • 60 — D
  • 70 — C
  • 80 — B
  • 90 — A

With a score of 76, the appropriate result is C because 70 is the largest listed boundary no higher than 76. The first boundary should cover the smallest possible input. If the table starts at 60 and a score of 55 is searched, a lookup using next-smaller behavior has no valid threshold to return. Decide how to handle values below the first boundary: add a lower starting band, use an error-handling result, or validate the input separately.

Keep the boundaries in one column and the associated return values in the next column for VLOOKUP. Avoid blank or duplicate thresholds: duplicates can make it unclear which row should represent a boundary. For a scale with a maximum, the last boundary can simply cover every larger value, or you can add a separate validation rule if values above the maximum should be rejected.

Use XLOOKUP for approximate numeric matches

XLOOKUP is usually the clearest option in Microsoft 365 and Excel 2021 or later. With thresholds in A2:A6, grades in B2:B6, and the score in D2, this formula returns the grade for an exact threshold or the next smaller threshold:

With the stated table layout and 76 in D2, enter =XLOOKUP(D2,A2:A6,B2:B6,,-1). The result is C, because 70 is the largest threshold at or below 76.

The fifth argument, -1, means “exact match or next smaller item.” Use 1 instead when the intended rule is “exact match or next larger item,” such as assigning a value to the next available package size. XLOOKUP’s match mode does not require the lookup column to be sorted for these two modes, though a sorted threshold table is easier to inspect and maintain. XLOOKUP returns the first matching item it encounters when there are duplicates, so unique boundaries are still preferable.

XLOOKUP is not available in some older perpetual Excel versions. If a workbook will be opened by people using older software, VLOOKUP or INDEX with MATCH may be more compatible. A formula that works in Microsoft 365 may display as an unsupported-function error in an older version.

Use VLOOKUP or INDEX and MATCH

VLOOKUP’s fourth argument controls approximate matching. With thresholds in A2:A6, grades in B2:B6, and the score in D2, enter:

With that layout and a score of 76 in D2, use =VLOOKUP(D2,A2:B6,2,TRUE). The formula returns C. The value TRUE requests approximate matching; leaving the fourth argument out also makes VLOOKUP approximate by default, so it is safer to specify TRUE or FALSE explicitly.

For approximate VLOOKUP, the first column of the table must be sorted from smallest to largest. If it is unsorted, Excel can return an incorrect result without warning. The column index 2 tells VLOOKUP to return the second column of the table range. If columns are inserted or the table layout changes, that number may need updating.

INDEX and MATCH can separate the lookup and return ranges. With the same thresholds, grades, and score, enter =INDEX(B2:B6,MATCH(D2,A2:A6,1)). MATCH’s 1 mode finds the largest value less than or equal to the lookup value, and it also requires the lookup range to be sorted ascending. This combination is useful in older Excel versions and does not rely on VLOOKUP’s limitation of returning values only from columns to the right of its lookup column.

Choose the correct direction and match mode

Before writing a formula, describe the business rule in plain language. “Use the highest rate whose minimum is no more than the sale” is a next-smaller rule. “Use the first capacity that can hold this load” is a next-larger rule. Reversing the direction can produce plausible-looking but wrong results, so test values that fall between boundaries as well as values exactly on a boundary.

  • Largest boundary at or below the input: XLOOKUP match mode -1; VLOOKUP with TRUE; or MATCH with 1 on ascending data.
  • Smallest boundary at or above the input: XLOOKUP match mode 1. For other lookup functions, arrange and test the data carefully because their approximate modes have specific sort-order requirements.
  • Exact value only: XLOOKUP’s default mode, VLOOKUP with FALSE, or an exact MATCH mode. This is not approximate matching.

For instance, suppose shipping brackets are listed as 1, 5, 10, and 20 kilograms. A 7-kilogram parcel may use the 5-kilogram lower bracket if the table defines rates by minimum weight; it may instead need the 10-kilogram bracket if the rule is to round up to a capacity. The worksheet cannot determine that policy by itself. The lookup direction must reflect the actual rule.

Approximate text matches and fuzzy lookup

If the goal is to find text containing a word or phrase, use a wildcard search rather than a numeric approximate mode. XLOOKUP supports wildcard matching when its match-mode argument is 2. With names or descriptions in A2:A20, corresponding IDs in B2:B20, and a search fragment in D2, enter:

With that layout, =XLOOKUP("*"&D2&"*",A2:A20,B2:B20,"No match",2) returns the ID on the first row whose text contains the search fragment. This is a pattern match, not a similarity score: it will not automatically treat misspellings, swapped letters, or abbreviations as equivalent. If several rows match, it returns the first one, so check for duplicates before relying on the result.

For genuinely similar text, Power Query offers fuzzy matching during a merge in supported Windows versions of Excel for Microsoft 365. Convert each list to a table, select a cell in a table, and use Data > From Table/Range to open Power Query. Then use Home > Merge Queries, choose the matching columns in both tables, and select the option to use fuzzy matching. The merge can pair text values that are similar rather than identical, and its settings let you adjust the similarity threshold and other matching behavior.

Fuzzy results are candidates, not proof that two records refer to the same person or product. Review matches before combining important records, especially where names are common or descriptions are short. Power Query’s available features can differ on Mac and in Excel for the web; if the fuzzy-matching option is not available in your installation, use supported Windows Excel or another data-cleaning tool rather than assuming a worksheet lookup will identify misspellings.

Common problems and how to check results

The most frequent error is an unsorted VLOOKUP or approximate MATCH range. Sort the threshold column ascending and keep each return value on the same row as its boundary. Also verify that the lookup value and thresholds have compatible data types. A number stored as text may not behave like a numeric threshold, and dates should be real Excel dates rather than text that merely looks like a date.

  • Test an input exactly on a boundary, just below it, and just above it.
  • Check values below the first threshold and above the last one.
  • Look for duplicate boundaries, blanks, hidden spaces, and numbers stored as text.
  • Compare a few formula results with a manually selected row before filling the formula down.

For example, if a threshold of 70 should include a score of exactly 70, test 69, 70, and 71. With next-smaller matching, these should select the bands beginning at 60, 70, and 70 respectively. If the output is unexpected, inspect the threshold column and the formula’s match direction before changing the return range. A wrong result is often a setup issue rather than a calculation bug.

Frequently Asked Questions

Does VLOOKUP use approximate match by default?
Yes. If you omit its fourth argument, VLOOKUP defaults to approximate matching. Use TRUE explicitly for approximate matching or FALSE for exact matching, and sort the first table column ascending when using TRUE.

Does approximate match mean the closest number?
Usually, in threshold tables, it means the largest listed value that is less than or equal to the lookup value. That is not necessarily the numerically closest value. If you need the closest value by absolute distance, define and calculate that rule separately.

Can XLOOKUP return the next larger value?
Yes. Set XLOOKUP’s match-mode argument to 1 for an exact match or the next larger item. Use -1 for an exact match or the next smaller item, and confirm that this direction matches the table’s rule.

Can Excel approximately match misspelled names?
Ordinary lookup formulas do not reliably correct misspellings. Wildcards can find a text pattern, but they do not measure general similarity. Use Power Query fuzzy matching where available and review the proposed pairs.

Why does approximate VLOOKUP return the wrong row?
The usual cause is that its first lookup column is not sorted ascending, though mismatched data types and incorrect thresholds can also cause trouble. Sort the thresholds, confirm their values, and test inputs around the relevant boundary.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.