VLOOKUP Exact vs Approximate Match Explained

VLOOKUP’s exact match (FALSE) finds the first row where the lookup value equals the first column value, while approximate match (TRUE) finds the largest value that is less than or equal to the lookup value. You choose between them by the fourth argument in the formula: FALSE for exact, TRUE or omitted for approximate.

Background: Why VLOOKUP Has Two Match Modes

VLOOKUP was designed in the 1980s for spreadsheet users who needed two distinct behaviors. Exact match is for looking up specific identifiers like product codes, employee IDs, or order numbers. Approximate match is for finding where a value falls within a range, such as tax brackets, shipping tiers, or grading scales. The two modes use different search algorithms, which is why the choice affects both results and performance.

How Exact Match Works

When you set the fourth argument to FALSE (or 0), VLOOKUP scans the first column from top to bottom until it finds a cell that exactly equals the lookup value. It then returns the value from the same row in the column you specified. If no exact match exists, VLOOKUP returns #N/A.

Key characteristics of exact match:

• The first column does not need to be sorted.
• It finds the first occurrence if duplicates exist, not the last.
• It is case-insensitive: “apple” and “APPLE” are treated as the same.
• It works with text, numbers, or dates, as long as the types match.

Example: =VLOOKUP("A-100", A2:B10, 2, FALSE) looks for the exact text “A-100” in A2:A10 and returns the corresponding value from column B.

How Approximate Match Works

When you set the fourth argument to TRUE (or omit it), VLOOKUP assumes the first column is sorted in ascending order. It then finds the largest value in the first column that is less than or equal to the lookup value. This is a binary search, which is fast but requires sorting.

Important details:

• If the first column is not sorted, results are unpredictable and often wrong.
• If the lookup value is smaller than the smallest value in the first column, VLOOKUP returns #N/A.
• It returns the closest match going down, not the nearest overall.
• It works best with numeric ranges or dates.

Example: =VLOOKUP(75000, A2:B5, 2, TRUE) where A2:A5 contains 0, 50000, 100000, 150000. The lookup value 75000 falls between 50000 and 100000, so VLOOKUP returns the value associated with 50000.

How to Choose the Right Match Type

The decision is straightforward: use exact match when you are looking for a specific item, and approximate match when you are categorizing a value into a tier. Here are concrete guidelines:

• Use exact match (FALSE) when the lookup value is a unique identifier: product SKU, customer ID, email address, or part number.
• Use approximate match (TRUE) when you have a lookup table with ranges: tax rates, commission tiers, shipping costs by weight, or letter grades by score.
• If you are unsure, default to exact match; approximate match with unsorted data is a common source of errors.
• For approximate match, always sort the first column ascending, and make sure the ranges do not overlap.

Common Problems and How to Fix Them

Several issues trip up users:

• #N/A error: No exact match found, or for approximate match the lookup value is below the first entry. Check for extra spaces, non-printable characters, or number stored as text.
• Wrong approximate result: The first column is not sorted. Sort it ascending.
• Unexpected exact match failure: The lookup value and table values look the same but one is text and the other is a number. Use TRIM() and CLEAN() to clean data, or convert types.
• Column index too high: The column number exceeds the number of columns in the table array. Count columns from the first column of the range.
• Duplicate lookup values: Exact match returns the first occurrence; if you need the last, use LOOKUP or an array formula.

Performance and Alternatives

Exact match on a large dataset (tens of thousands of rows) can be slow because it scans row by row. Approximate match is faster due to binary search, but only works with sorted data. For modern Excel, consider XLOOKUP which defaults to exact match and handles unsorted data efficiently. In Google Sheets, VLOOKUP works similarly, and INDEX/MATCH offers more flexibility.

If you frequently need to look up values from the left of the lookup column, VLOOKUP cannot do that; use INDEX/MATCH or XLOOKUP instead.

Frequently Asked Questions

What is the default match type in VLOOKUP?

The default is approximate match (TRUE). If you omit the fourth argument, VLOOKUP assumes TRUE. This is a common pitfall because many users forget to specify FALSE and get unexpected results.

Can VLOOKUP find an exact match when the first column is not sorted?

Yes, exact match (FALSE) does not require sorting. It scans the column sequentially, so it works regardless of order. Approximate match (TRUE) requires sorting.

Why does VLOOKUP return #N/A even though the value exists?

Common causes: the lookup value has extra spaces, the data type differs (text vs number), or the table array does not include the lookup column as the first column. Use TRIM() and check the cell formatting.

Is VLOOKUP case-sensitive?

No, VLOOKUP is not case-sensitive by default. “ABC” and “abc” are treated as identical. If you need case-sensitive lookup, use EXACT in an array formula or XLOOKUP with match mode 0.

What is the difference between VLOOKUP approximate match and XLOOKUP?

XLOOKUP defaults to exact match and does not require sorting for approximate match if you use the optional search mode. It also allows lookup to the left and returns multiple values. VLOOKUP approximate match is limited to sorted data and always returns the first column.

How do I use VLOOKUP with approximate match for tax brackets?

Create a table with the lower bound of each bracket in the first column (sorted ascending) and the tax rate in the second. Use =VLOOKUP(income, bracket_table, 2, TRUE). For an income of 75,000 with brackets starting at 0, 50,000, 100,000, it returns the rate for 50,000.

Related

• Moto G6 Play Images Leaked? What We Know
• Recover Deleted Music from iPod Touch
• Star Wars Galactic Racer Single Player: How to Progress