VLOOKUP Approximate Match: Step-by-Step Guide

Updated 14 November 2026 · Standard formula, shown below

This guide explains how to use VLOOKUP for approximate matches in Excel and Google Sheets. Approximate match mode finds the closest value that is less than or equal to your lookup value, which is essential for tiered pricing, tax brackets, grading scales, and shipping rates. You’ll learn the exact syntax, how to set it up correctly, and how to avoid the most common errors.

What You’ll Need

A spreadsheet with a lookup table that is sorted in ascending order (smallest to largest) by the first column.
The lookup value you want to match (a number, cell reference, or formula).
Knowledge of the column number in the table that contains the result you want to return.

Step-by-Step

Sort your lookup table by the first column in ascending order. This is non-negotiable. Select the entire table (including headers), go to the Data tab, and click Sort. Choose the first column as the sort column and sort A to Z. If you skip this, VLOOKUP will return incorrect or #N/A results.
Identify the lookup value. This is the value you want to find in the first column of your table. It can be typed directly, or more commonly, it’s a cell reference (e.g., B2) or a formula result.
Determine the column index number. Count the columns in your table from left to right. The first column is 1, the second is 2, and so on. The column index number is the position of the column that contains the value you want to retrieve. For example, if your table is A:B and you want the value from column B, the index is 2.
Write the VLOOKUP formula. In the cell where you want the result, type: =VLOOKUP(lookup_value, table_array, col_index_num, TRUE). The TRUE at the end is what triggers approximate match mode. In Excel, you can also use 1 instead of TRUE.
Replace the arguments with your actual data. For example: =VLOOKUP(B2, A2:B10, 2, TRUE). Here, B2 is the lookup value, A2:B10 is the table range, 2 is the column index, and TRUE enables approximate matching.
Press Enter. The formula will return the value from the row where the first column is the largest value that is less than or equal to your lookup value.

Common Mistakes

Forgetting to sort the table. This is the #1 cause of wrong results. Even if the table looks sorted, double-check that the first column is truly in ascending order with no duplicates or gaps.
Using FALSE instead of TRUE. If you type FALSE or 0, VLOOKUP will only return an exact match. This is useful for exact lookups but defeats the purpose of approximate matching.
Wrong column index. Counting from 1 is easy to mess up, especially with wide tables. Always verify the index by counting the columns in your selected range.
Including header rows in the table array. If your table array includes the header row, VLOOKUP might return the header text or an error. Start the table array at the first data row.

Tips

Use absolute references for the table array. When copying the formula down, you don’t want the table range to shift. Use dollar signs, like $A$2:$B$10, to lock the range.
Name your table. Select the table, go to the Formulas tab, and click Define Name. Then use that name in your formula, e.g., =VLOOKUP(B2, RateTable, 2, TRUE). This makes the formula easier to read and maintain.
Understand the “less than or equal to” logic. VLOOKUP approximate match finds the largest value in the first column that is less than or equal to your lookup value. If your lookup value is smaller than the smallest value in the table, you’ll get #N/A.
Use XLOOKUP if you have a newer version of Excel. XLOOKUP with match mode -1 gives you more control and doesn’t require sorting. But VLOOKUP is still widely used and works in older files.

Troubleshooting

#N/A error: This means no match was found. Check that your lookup value is not smaller than the smallest value in the table. Also, verify the table is sorted ascending. If the lookup value is text, ensure it exactly matches the format in the table (no extra spaces, correct case).
#REF! error: The column index number is greater than the number of columns in the table array. Count the columns in your selected range and adjust the index.
#VALUE! error: The lookup value or the table array contains text that Excel can’t interpret as a number, or the column index is 0 or negative. Check the data types.
Wrong result (not an error): This usually means the table isn’t sorted. Re-sort the first column in ascending order. Also, check for duplicate values in the first column; VLOOKUP will use the first occurrence.
Formula returns the header text: Your table array likely includes the header row. Adjust the range to start from the first data row.

Frequently Asked Questions

Why does VLOOKUP require the table to be sorted?

Approximate match uses a binary search algorithm. It assumes the data is sorted so it can quickly narrow down the position. If the data isn’t sorted, the binary search can land on the wrong value, giving incorrect results.

Can I use approximate match with text?

Yes, but it’s less common. For text, VLOOKUP will find the largest text value that is alphabetically less than or equal to the lookup value. Sorting must be alphabetical. This is rarely useful, so most people use exact match for text.

What’s the difference between approximate and exact match?

Exact match (FALSE) finds the first row where the first column equals the lookup value. Approximate match (TRUE) finds the largest value that is less than or equal to the lookup value. Use exact match for IDs, names, or codes; use approximate for ranges like income brackets or scores.

Does VLOOKUP work in Google Sheets?

Yes, the syntax is identical. The same sorting requirement applies. Google Sheets also supports XLOOKUP in newer versions, but VLOOKUP remains compatible.

How do I handle values that are below the minimum in my table?

If your lookup value is smaller than the smallest value in the first column, VLOOKUP returns #N/A. You can wrap the formula in IFERROR to display a custom message, like =IFERROR(VLOOKUP(...), "Below minimum").

Can I use approximate match with unsorted data if I use XLOOKUP?

Yes. XLOOKUP with match mode -1 (exact match or next smaller) does not require sorting. It’s a more flexible alternative, but VLOOKUP is still useful for compatibility with older spreadsheets.

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