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.