Approximate vs exact match in Excel - Compared

Introduction

When you look up data in Excel, sometimes you want an exact match, and other times a close enough match works. This feature helps you choose between finding the exact value or the nearest one in your data.

When you want to find a product price exactly matching a product code.

When you need to find a grade letter based on a score range, like 90 or above is A.

When searching for a person's name exactly in a list of contacts.

When you want to find the closest date before or after a given date in a schedule.

When matching zip codes exactly to find location details.

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for formula input

Step 2: Type

- the selected cell

Formula input starts

💡 Start with =XLOOKUP or =VLOOKUP for lookup formulas

Step 3: Enter

- formula bar

Formula looks like =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])

💡 For exact match, set match_mode to 0 or omit it

Step 4: Set

- match_mode argument in XLOOKUP

Exact match is used when match_mode is 0; approximate match when match_mode is 1 or -1

💡 In VLOOKUP, use FALSE for exact match and TRUE for approximate match

Step 5: Press

- Enter key

Formula calculates and shows the matched value or closest match

Before vs After

Before

Formula =VLOOKUP(85, A2:B10, 2, TRUE) returns 80 for a lookup value of 85 because it finds the closest smaller value

After

Formula =VLOOKUP(85, A2:B10, 2, FALSE) returns #N/A because 85 is not exactly in the lookup range

Settings Reference

match_mode (XLOOKUP)

📍 5th argument in XLOOKUP formula

Controls how Excel matches the lookup value

Default: 0 (exact match)

range_lookup (VLOOKUP)

📍 4th argument in VLOOKUP formula

Determines if VLOOKUP finds exact or closest match

Default: TRUE (approximate match)

Common Mistakes

Using approximate match when exact match is needed

It returns wrong or unexpected values if the lookup value is not sorted or missing

Use exact match by setting match_mode to 0 in XLOOKUP or FALSE in VLOOKUP

Forgetting to sort data when using approximate match

Approximate match requires sorted data to work correctly

Sort the lookup column in ascending order before using approximate match

Summary

Exact match finds the value that exactly equals the lookup value.

Approximate match finds the closest value when an exact one is missing.

Use exact match for precise data like IDs; use approximate for ranges like grades.