Approximate vs exact match in Excel - Formula Comparison Trace

Sample Data

This table shows student scores in column A and their corresponding grades in column B. We want to find the grade for a lookup score 80 using approximate and exact match.

Cell	Value
A1	Score
A2	45
A3	78
A4	92
B1	Grade
B2	F
B3	C
B4	A
D1	Lookup Score
D2	80
E1	Approximate Match
E2
F1	Exact Match
F2

Formula Trace

=VLOOKUP(D2, A2:B4, 2, TRUE)

Step 1: VLOOKUP(80, A2:B4, 2, TRUE)

Step 2: Values in A2:A4 are [45, 78, 92]

Step 3: Return corresponding value from column 2 for 78

Cell Reference Map

    A       B       C       D       E       F
1 | Score | Grade |       | Lookup | Approx | Exact |
2 |  45   |   F   |       |   80   |        |       |
3 |  78   |   C   |       |        |        |       |
4 |  92   |   A   |       |        |        |       |

Arrows:
D2 --> A2:A4 (lookup range)
A2:A4 --> B2:B4 (return column)

The lookup value in D2 is searched in the range A2:A4. The matching or closest lower value's corresponding grade from B2:B4 is returned.

Result

    A       B       C       D       E           F
1 | Score | Grade |       | Lookup | Approx Match | Exact Match |
2 |  45   |   F   |       |   80   |      C      |    #N/A    |
3 |  78   |   C   |       |        |             |            |
4 |  92   |   A   |       |        |             |            |

The approximate match formula returns 'C' because 78 is the closest lower score to 80. The exact match formula returns #N/A because 80 is not found exactly.

Sheet Trace Quiz - 3 Questions

What does the TRUE parameter in VLOOKUP do?

A Finds the closest lower or equal value if exact match not found

B Finds only exact matches

C Returns the last value in the range

D Sorts the data before lookup

Key Result

VLOOKUP searches first column of range; TRUE finds closest lower or equal; FALSE finds exact match only.

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