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.

Practice

1. What does the FALSE argument in the VLOOKUP function specify?

easy

A. It sorts the data automatically before searching.

B. It tells Excel to find the closest smaller value.

C. It ignores errors in the lookup value.

D. It tells Excel to find an exact match only.

Solution

1. Step 1: Understand the role of the last argument in VLOOKUP

   The last argument in VLOOKUP is the range_lookup, which controls match type.

2. Step 2: Identify what FALSE means in range_lookup

   When set to FALSE, VLOOKUP looks for an exact match only, returning an error if not found.

3. Final Answer:

   It tells Excel to find an exact match only. -> Option D

4. Quick Check:

   FALSE = exact match [OK]

Hint: FALSE means exact match; TRUE means approximate match [OK]

Common Mistakes:

• Confusing FALSE with approximate match
• Thinking FALSE sorts data automatically
• Assuming FALSE ignores errors

2. Which of the following is the correct syntax for an exact match VLOOKUP formula searching for value in A2 in range B2:D10 and returning the 3rd column?

easy

A. =VLOOKUP(A2, B2:D10, 3, TRUE)

B. =VLOOKUP(A2, B2:D10, 3, FALSE)

C. =VLOOKUP(A2, B2:D10, 3)

D. =VLOOKUP(A2, B2:D10, 3, 1)

Solution

1. Step 1: Recall exact match syntax for VLOOKUP

   Exact match requires the last argument to be FALSE.

2. Step 2: Check each option

   =VLOOKUP(A2, B2:D10, 3, FALSE) uses FALSE explicitly, which is the standard exact match syntax.

3. Final Answer:

   =VLOOKUP(A2, B2:D10, 3, FALSE) -> Option B

4. Quick Check:

   Exact match uses FALSE in VLOOKUP [OK]

Hint: Use FALSE for exact match in VLOOKUP formulas [OK]

Common Mistakes:

• Omitting the last argument defaults to approximate match
• Using TRUE when exact match is needed
• Confusing 0 and FALSE syntax

3. Given this sorted table in cells A1:B5:

A1: Score
A2: 50
A3: 70
A4: 90
A5: 100

What will =VLOOKUP(85, A2:B5, 1, TRUE) return?

medium

A. 70

B. 85

C. 90

D. #N/A

Solution

1. Step 1: Understand approximate match behavior

   With TRUE, VLOOKUP finds the largest value less than or equal to lookup value (85) in sorted data.

2. Step 2: Find closest smaller or equal value to 85

   Values are 50, 70, 90, 100. The largest value ≤ 85 is 70.

3. Final Answer:

   70 -> Option A

4. Quick Check:

   Approximate match returns closest smaller value [OK]

Hint: TRUE finds closest smaller value in sorted data [OK]

Common Mistakes:

• Expecting exact match with TRUE
• Choosing 90 because it's closest larger value
• Getting #N/A error for approximate match

4. You used =VLOOKUP(75, A2:B5, 2, TRUE) but got wrong results. The data in A2:A5 is not sorted. What is the best fix?

medium

A. Change the column index to 1.

B. Change TRUE to FALSE to force exact match.

C. Sort the data in ascending order before using TRUE.

D. Use INDEX instead of VLOOKUP.

Solution

1. Step 1: Identify problem with unsorted data and approximate match

   Approximate match (TRUE) requires data sorted ascending to work correctly.

2. Step 2: Fix by sorting data ascending

   Sorting the lookup column ascending ensures approximate match finds correct closest smaller value.

3. Final Answer:

   Sort the data in ascending order before using TRUE. -> Option C

4. Quick Check:

   Approximate match needs sorted data [OK]

Hint: Sort data ascending before using TRUE in VLOOKUP [OK]

Common Mistakes:

• Switching to FALSE without needing exact match
• Changing column index without reason
• Replacing VLOOKUP with INDEX unnecessarily

5. You have a price list sorted by quantity:

Qty: 1, 10, 20, 50
Price: 100, 90, 80, 70

Which formula correctly finds the price for an order of 15 units using approximate match?

hard

A. =VLOOKUP(15, A2:B5, 2, TRUE)

B. =VLOOKUP(15, A2:B5, 2, FALSE)

C. =VLOOKUP(15, A2:B5, 1)

D. =VLOOKUP(15, A2:B5, 2, 0)

Solution

1. Step 1: Understand the need for approximate match in price tiers

   For quantity 15, price depends on closest smaller quantity tier (10), so approximate match is needed.

2. Step 2: Choose formula with TRUE for approximate match

   =VLOOKUP(15, A2:B5, 2, TRUE) uses TRUE, enabling approximate match to find price for 10 units tier.

3. Final Answer:

   =VLOOKUP(15, A2:B5, 2, TRUE) -> Option A

4. Quick Check:

   Approximate match finds correct tier price [OK]

Hint: Use TRUE for tiered pricing with sorted quantity [OK]

Common Mistakes:

• Using FALSE causes #N/A if exact quantity missing
• Omitting last argument defaults to TRUE but unclear
• Using 0 instead of TRUE for approximate match