What you will fix

A VLOOKUP can return a value even when the lookup key is not present if its fourth argument allows approximate matching. In this practice sheet, P03 is not in the SKU list, yet =VLOOKUP(D2,A2:B4,2,TRUE) returns 35 because it falls back to the preceding approximate match. You will replace that behavior with exact matching.

Learning objectives

Open the practice workbook

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Google Sheets practice table with SKU values P01, P02, P10 and lookup key P02 before the VLOOKUP test.

Sample data

SKUPrice
P0112
P0235
P1080

The lookup table is A2:B4. P03 is intentionally absent so you can reproduce the wrong-match symptom.

Lookup cellStarting keyResult cell
D2P02E2

You will temporarily change D2 during diagnosis, then restore P02.

Why the wrong result happens

Step 1: Change D2 to P03, a SKU that does not exist in A2:A4. Enter =VLOOKUP(D2,A2:B4,2,TRUE) in E2. In the verified workbook, E2 returns 35 even though P03 is missing.

=VLOOKUP(D2,A2:B4,2,TRUE)
Cell E2 selected after an approximate-match VLOOKUP returns 35 for missing lookup key P03.

Fix the lookup with an exact match

Step 2: Keep D2 as P03 and replace TRUE with FALSE. The formula becomes =VLOOKUP(D2,A2:B4,2,FALSE). Because P03 is not present, the verified result is #N/A instead of a misleading price.

=VLOOKUP(D2,A2:B4,2,FALSE)
Cell E2 selected with exact-match VLOOKUP returning #N/A for missing SKU P03.

Step 3: Restore D2 to P02. Keep the exact-match formula in E2. The verified result returns to 35.

Cell E2 selected with =VLOOKUP(D2,A2:B4,2,FALSE) and result 35 for lookup key P02.

Recalculation check: temporarily change D2 from P02 to P10. E2 should update from 35 to 80. Restore D2 to P02 and confirm E2 returns to 35.

Checkpoint

Lookup SKUFormulaExpected result
P02=VLOOKUP(D2,A2:B4,2,FALSE)35
P10=VLOOKUP(D2,A2:B4,2,FALSE)80
P03=VLOOKUP(D2,A2:B4,2,FALSE)#N/A

These three cases distinguish exact-match behavior from the misleading approximate match.

Common mistakes and fixes

If FALSE returns #N/A for a key you believe exists, compare the key exactly: leading/trailing spaces, text-versus-number differences, or a lookup range that starts in the wrong column can all prevent an exact match. Do not “fix” #N/A by switching back to TRUE.

Independent practice

Add a temporary SKU P05 with price 50, test it with FALSE, then remove the row. Also try a missing SKU and confirm the exact-match formula returns #N/A rather than another item’s price.

Key takeaways

  • ✓TRUE enables approximate matching; it is not appropriate for ordinary exact-ID lookups.
  • ✓A missing key can produce a believable but wrong value under approximate matching.
  • ✓FALSE requires an exact match and returns #N/A when the key is absent.
  • ✓For this sample, P02 returns 35 and P10 returns 80 with the exact formula.
  • ✓Test a changed lookup key, then restore the original sample to confirm the formula is live.
QUICK CHECK

Check your understanding

Answer four questions about the practice sheet.

1. Why did P03 return 35 with =VLOOKUP(D2,A2:B4,2,TRUE)?
2. Which fourth argument should you normally use for exact SKU or ID matching?
3. What should exact-match VLOOKUP return for P03 in this sample?
4. What live test confirms E2 is formula-driven?

Next lesson

Continue with missing-value handling in XLOOKUP, where you can also control the not-found result directly.

Next: Fix XLOOKUP missing values ↗