VLOOKUP Approximate Match Not Working? Fix the Cause

VLOOKUP approximate match usually fails because the lookup column is not sorted in ascending order, the lookup value and keys use different data types, or the formula is using the wrong match mode. Set the last argument to TRUE, sort the first column from smallest to largest, and check that the lookup value is at least as large as the first key; approximate match returns the value for the largest key that does not exceed the lookup value, not the nearest key.

How VLOOKUP approximate match works

VLOOKUP searches the leftmost column of its table range and returns a value from another column in the matching row. Its fourth argument, often called range_lookup, controls whether the search is exact or approximate. TRUE requests approximate matching; FALSE requests exact matching. If you leave that argument out, Excel uses TRUE, which is why a formula can behave unexpectedly when its match mode is not explicit.

With approximate matching, an exact match is returned when one exists. Otherwise, Excel returns the row associated with the greatest first-column value that is less than the lookup value. For a tax band, grading scale, shipping rate, or commission table, that is often exactly the desired rule. It is not a search for the closest value in either direction.

For example, suppose A2:B5 contains these breakpoints and rates: 0 and 0%, 50 and 5%, 75 and 8%, and 100 and 10%. With a lookup value of 73 in E2, Excel should return 5%, because 50 is the largest breakpoint not greater than 73. It should not return 8%, which belongs to the next breakpoint, 75.

Check the formula and match mode first

Start by confirming the formula’s table range, return-column number, and final argument. The table range must begin with the lookup keys, and the column index counts from the left edge of that range. If the range is A2:B5, column 2 returns the values in B. If the range starts at B instead, the index numbers change accordingly.

With breakpoints in A2:A5, corresponding rates in B2:B5, and the lookup value in E2, this formula returns the rate for the largest breakpoint at or below E2. With the example values above and E2 equal to 73, the result is 5%:

=VLOOKUP(E2,$A$2:$B$5,2,TRUE)

Make TRUE explicit rather than relying on the default. A common cause of confusion is assuming that VLOOKUP is doing an exact search when the fourth argument has been omitted. Conversely, if you actually need an exact match, use FALSE. With the same example table and E2 equal to 73, an exact-match formula returns #N/A because 73 is not one of the listed keys:

=VLOOKUP(E2,$A$2:$B$5,2,FALSE)

That #N/A is correct for an exact search; it does not indicate that the formula is broken. Also check that the return-column index is a positive number within the table range. An incorrect index can produce a different error or return the wrong field, even if the approximate search itself is working.

Sort the lookup column in ascending order

Approximate VLOOKUP expects the first column of the selected table range to be sorted in ascending order. For numbers, that means smallest to largest; for text, it means alphabetical order. If the keys are out of order, Excel may return a plausible-looking but incorrect answer. Do not treat a result that looks reasonable as proof that the table is sorted correctly.

In Excel for Microsoft 365 on Windows, select the table and use Data > Sort, then sort by the lookup-key column from smallest to largest. If Excel asks whether to expand the selection, choose to expand it so the related return values move with their keys. Sorting only the first column can scramble the relationship between each key and its result. On Mac, use the Data tab’s Sort controls; in Excel for the web, use the Data tab’s sort options. The labels and layout can vary slightly by platform.

Check every row in the key column, not only the first few values.
Make sure the keys are consistently numeric or consistently text; sorting mixed types does not make them equivalent.
Keep the entire lookup table together when sorting, including any adjacent return columns.
After adding new breakpoints, sort the table again before relying on approximate results.

For a small numeric table, scan down the first column and confirm that each value is greater than or equal to the one above it. Duplicate keys are usually a sign that the table needs review: approximate lookup is meant to locate a threshold, and repeated thresholds can make the intended return row unclear. Decide which key should apply and remove or resolve duplicates where appropriate.

Make sure the keys and lookup value have the same type

A value that looks like a number may actually be text. For instance, a key entered as the text 50 may not behave like the numeric value 50 in E2. This can happen after importing data, pasting values from a website, or using a leading apostrophe. Dates can also be stored as text rather than as Excel date values. Formatting a cell to display a number or date does not necessarily convert the underlying value.

To investigate, select a key and look at the formula bar and cell alignment, but do not rely on alignment alone because formatting can change it. You can also try sorting the key column and inspect whether values group or order unexpectedly. Convert imported data deliberately—for example, use the warning indicator’s conversion option when Excel offers it, or use Data > Text to Columns for appropriate text data. Make a copy first if conversion could affect other calculations.

For dates, confirm that the lookup value and the breakpoint dates are real Excel dates, and that the table is sorted from earliest to latest. A date that appears as 1/1/2025 but is stored as text may fail to compare as intended with a real date. Similarly, avoid mixing values such as 50, 50%, and text labels like 50 units in the same key column unless the comparison rules are deliberately designed for that data.

Check the lower boundary and table layout

Approximate matching cannot return a valid threshold row when the lookup value is smaller than the first key. In that situation, VLOOKUP returns #N/A. If the lowest valid value should receive a rate, include a breakpoint that covers that value—for example, 0 for a nonnegative quantity scale. Do not add an arbitrary low key if negative values have a different business meaning; define the table according to the intended rule.

Also verify that the lookup column is the first column in the VLOOKUP range. VLOOKUP cannot search a column to the right and return a value to its left. If the key is in column C and the desired result is in column B, a range beginning at B does not solve that problem: the leftmost column of the range must contain the lookup key. Consider rearranging the table or using a different lookup function if the layout cannot change.

When the range is written as A2:B5, adding a new row below row 5 will not automatically include it in the formula. Update the range or turn the data into an Excel table using Insert > Table so the data can expand more reliably. A table also makes it easier to sort the complete set of key-and-result rows together. Check that the formula’s selected range includes all current breakpoints and no unrelated rows.

Choose approximate or exact matching deliberately

Approximate match is suitable when values represent ordered cutoffs: a score maps to a grade band, a quantity maps to a price tier, or a date maps to the most recent effective date. The table should express the rule using lower-bound thresholds. For example, if 75 is the threshold for an 8% rate, store 75 as the key and expect values from 75 up to (but not including) the next threshold to return that rate.

Exact match is generally the right choice for identifiers such as employee IDs, product codes, or account numbers. Those values should match a specific key, not fall into a band. Use FALSE when exactness is required, even if your key column happens to be sorted. Sorting does not turn an approximate formula into an exact one, and a near-looking code should not be treated as the same identifier.

A useful diagnostic is to compare results for a known breakpoint and a value between two breakpoints. If a value equal to 75 returns the 75 row, but 73 returns the 50 row, the threshold behavior is consistent with approximate matching. If a lookup value is supposed to return only an identical key, test the same value with FALSE and investigate #N/A as a data or key issue instead of accepting a nearby row.

When XLOOKUP may be a better alternative

In Microsoft 365 and Excel 2021 or later, XLOOKUP can make the match rule more visible and can return values from either side of the lookup column. Its match mode of -1 means exact match or, if no exact match exists, the next smaller item. For the same sorted threshold table in A2:A5 and B2:B5, with the lookup value in E2, the formula returns 5% when E2 is 73:

=XLOOKUP(E2,A2:A5,B2:B5,,-1)

The table still needs to represent the intended thresholds, and the values should be consistently typed. XLOOKUP can use other match and search modes, so verify the selected mode rather than assuming it behaves like approximate VLOOKUP. It is not available in older perpetual Excel versions such as Excel 2019. If compatibility with those versions matters, VLOOKUP remains useful; specify TRUE or FALSE explicitly and document the sorting requirement.

Frequently Asked Questions

Why does VLOOKUP return the wrong value with TRUE?
Most often, the first column of the lookup range is not sorted ascending, or the keys and lookup value have inconsistent data types. Confirm the complete table is sorted with its return columns, then test with a known threshold.

Does approximate match mean closest match?
No. It returns the largest key that is less than or equal to the lookup value. It does not choose the next larger value or whichever key is closest by distance.

Why does approximate VLOOKUP return #N/A?
One common reason is that the lookup value is smaller than the first key in the sorted lookup column. It can also happen when the formula’s range excludes the key you expect, or when the data is inconsistent. Check those details before changing the match mode.

Can I use approximate match with an unsorted table?
Do not rely on it. Sort the first column ascending and keep each row’s related data together. An unsorted table can give an incorrect result without an obvious warning.

Should I use TRUE or FALSE for product codes?
Use FALSE for codes that must match exactly. Approximate matching is for ordered thresholds, not for treating one code as a substitute for a nearby code.

A quick troubleshooting checklist

Before changing a formula that appears to fail, work through these checks in order. They distinguish a formula error from a table-design issue and help prevent a fix that only masks the problem:

Confirm the final VLOOKUP argument is TRUE for approximate matching or FALSE for exact matching.
Verify the key column is the first column in the table range and the return-column index points to the intended field.
Sort all table rows by the key column in ascending order, keeping each row intact.
Check that keys and lookup values are the same kind of value, especially after importing data or working with dates.
Confirm the lookup value is not below the smallest key and that the formula range includes all current rows.
Test a key exactly on a breakpoint and another value between breakpoints to confirm the intended threshold rule.

Once these conditions are right, approximate VLOOKUP is predictable: it applies a sorted set of lower-bound thresholds and returns the result for the threshold immediately below or equal to the lookup value. If that is not the rule your data needs, switch to an exact lookup or redesign the lookup table rather than forcing approximate match to behave like a nearest-value search.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.