Approximate vs exact match in Google Sheets - Formula Comparison Trace

Sample Data

This table shows scores and their corresponding grades. We will lookup a score (75) to find the grade using approximate and exact match.

Cell	Value
A1	Score
A2	50
A3	70
A4	90
B1	Grade
B2	C
B3	B
B4	A
D1	Lookup Score
D2	75
E1	Approximate Match
E2
F1	Exact Match
F2

Formula Trace

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

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

Step 2: search in A2:A4 = {50, 70, 90}

Step 3: return corresponding value from column 2 in same row as 70

Step 4: =VLOOKUP(D2, A2:B4, 2, FALSE)

Step 5: search in A2:A4 = {50, 70, 90}

Step 6: formula returns #N/A error

Cell Reference Map

    A      B      C      D      E      F
1 |Score |Grade |      |Lookup |Approx |Exact 
2 |  50  |  C   |      |  75   |       |     
3 |  70  |  B   |      |       |       |     
4 |  90  |  A   |      |       |       |     

Arrows:
D2 --> VLOOKUP uses D2 as lookup value
A2:B4 --> VLOOKUP searches this range
E2 --> Approximate match result cell
F2 --> Exact match result cell

The formula looks up the value in D2 (75) in the range A2:B4. The approximate match formula result goes to E2, exact match result goes to F2.

Result

    A      B      C      D      E      F
1 |Score |Grade |      |Lookup |Approx |Exact 
2 |  50  |  C   |      |  75   |   B   | #N/A 
3 |  70  |  B   |      |       |       |     
4 |  90  |  A   |      |       |       |     

The approximate match finds grade B for score 75 because 70 is the closest lower score. The exact match returns #N/A because 75 is not exactly in the list.

Sheet Trace Quiz - 3 Questions

What grade does the approximate match formula return for score 75?

A B

B C

C A

D #N/A

Key Result

VLOOKUP(value, range, column, TRUE) finds closest lower match; with FALSE finds exact match only.