VLOOKUP Exact Match vs Approximate Match: When to Use Each

VLOOKUP’s last argument determines whether Excel searches for an exact value or an approximate one.

The difference can change a correct-looking formula into a misleading result, especially when the lookup column is unsorted.

How does VLOOKUP choose exact or approximate matches?

VLOOKUP uses the syntax VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

The optional range_lookup argument controls the match type:

FALSE (or 0) requests an exact match.
TRUE (or 1) requests an approximate match.
If you omit the argument, VLOOKUP defaults to approximate matching.

For reliable formulas, include the fourth argument explicitly.

Use FALSE when the lookup value should identify a specific item, such as an employee ID or product code.

Use TRUE when values represent ordered thresholds, such as tax brackets or grade boundaries.

When should you use exact match?

Exact match looks for a value equal to the lookup value in the first column of the table array.

If it finds a match, VLOOKUP returns a value from the requested column on the same row.

If it does not, Excel returns #N/A.

Suppose cells A2:B5 contain these records:

A2: P-104; B2: Keyboard
A3: P-207; B3: Mouse
A4: P-315; B4: Monitor
A5: P-422; B5: Webcam

To find the product associated with the code in D2, use:

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

If D2 contains P-315, the expected result is Monitor.

If it contains P-999, the result is #N/A, because that code is not in the lookup column.

Exact matching is usually the right choice for names, IDs, SKU numbers, account codes, and other discrete keys.

The lookup column does not need to be sorted for an exact match.

When should you use approximate match?

Approximate match is useful when the lookup table defines ranges by lower-bound thresholds.

VLOOKUP finds the largest value in the first column that is less than or equal to the lookup value, then returns the corresponding result.

For this behavior to work correctly, the first column must be sorted in ascending order.

For example, suppose A2:B5 contains score boundaries and letter grades:

0 — F
60 — D
70 — C
80 — B

With a score in D2, use:

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

If D2 is 76, the expected result is C: 70 is the largest threshold that does not exceed 76.

A score of 80 returns B.

A score below the smallest threshold, 0, returns #N/A.

This pattern works for tiered rates, commission bands, shipping brackets, and other step-based rules.

Each threshold marks where a new result begins.

What happens if the lookup column is not sorted?

Approximate match depends on ascending order.

If the first column is unsorted, VLOOKUP can return an unexpected result without warning.

For example, a threshold table arranged as 0, 70, 60, 80 does not reliably define the intended grade bands when used with TRUE.

Sort the threshold column from smallest to largest before using approximate matching.

Keep each threshold paired with its result row while sorting.

If sorting is not practical, consider a different lookup method or build a formula that explicitly checks the relevant conditions.

Exact match with FALSE does not have this sorting requirement.

Sorting can still help people scan a table, but it is not needed to locate an exact value.

How do blanks, duplicates, and data types affect results?

Duplicate exact-match values: VLOOKUP returns the first matching row it encounters.

If duplicate IDs have different return values, clean up the table or define which record should take precedence.
Numbers stored as text: A numeric lookup value may not match a text value that looks identical, such as the number 104 versus text "104".

Standardize the data type in both the lookup cell and table.
Extra spaces: Leading or trailing spaces in text keys can prevent an exact match.

A cleanup step such as TRIM may help, depending on the data.
Wildcard characters: With FALSE, text lookup values can use * to match any sequence of characters or ? for one character.

Prefix a wildcard with ~ to search for it as a literal character.
Blank lookup values: A blank can match a blank entry in an exact-match lookup column.

Check for empty keys if a result seems unexpected.
Column index: The column number counts from the left edge of table_array, not from column A.

In $C$2:$F$20, column C is index 1 and column F is index 4.

Does the match setting work the same in Excel and Google Sheets?

Excel and Google Sheets both support the traditional VLOOKUP arguments and the same core distinction: FALSE requests an exact match, while TRUE requests an approximate match based on an ascending lookup column.

Both use approximate matching when the fourth argument is omitted, so including it makes the formula’s intent clear.

In current Microsoft 365 and newer Excel versions, XLOOKUP offers a clearer alternative for many lookups.

Its default match mode is exact, and it can return results from columns on either side of the lookup column.

Google Sheets also provides functions such as XLOOKUP in supported environments, though function availability can vary by application version or account rollout.

If a workbook must work in older Excel versions, VLOOKUP is often a more compatible choice.

How do you choose the right setting?

Use this quick rule:

Choose FALSE for an exact identifier or category lookup.
Choose TRUE for sorted lower-bound thresholds and range-based results.
Always state the fourth argument so the formula does not rely on the default.
For approximate matching, verify that the first table column is sorted ascending and that the lowest threshold covers the smallest valid input.

If an exact lookup returns #N/A, check spelling, spaces, data types, and whether the value exists in the first column of the selected range.

If an approximate result is wrong, check the sort order, threshold values, and lookup range before changing the return-column index.