Try the example, then check the result.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
Quick answer. VLOOKUP returns wrong value when approximate match is used accidentally. Make exact matching explicit, then check a known product and a missing code. This guide uses a small fictional table and an independent answer check.
Download the original Excel practice workbook. A plain-text practice table and independent answer check are also available. Make changes in a disposable copy.
What success looks like. With exact-match FALSE, P-104 returns $8.75 from its source row and missing P-107 returns #N/A rather than a neighboring price. A missing-code error is a correct warning, not a price.
1. Prepare the sample
Open a disposable copy of the workbook. Its five fictional product rows are already in A1:C6, with Product Code in column A and Price in column C. E2 contains the missing code P-107. F2 contains =VLOOKUP(E2,$A$2:$C$6,3), which omits the fourth argument and returns the nearby P-106 price of $3.25 in this sorted practice table. Check the starting result in your Excel copy before replacing the formula.
Checkpoint: P-104 exists; P-107 does not.
2. Inspect the setting or formula
Inspect the existing formula in F2. Confirm it has three arguments and therefore uses approximate matching. P-107 is already entered in E2.
Checkpoint: Omitted means approximate, not exact.
3. Make the focused correction
Replace F2 with =VLOOKUP(E2,$A$2:$C$6,3,FALSE) for an intentional exact match. Keep the table range fixed and leave the source values intact.
Checkpoint: P-107 must not display a price.
4. Run a positive check
Check that P-107 now returns #N/A, then change E2 to P-104 and check for $8.75. Restore E2 to P-107 if you want the practice file to show the missing-code branch again.
Checkpoint: Source row and result agree.
5. Run a negative check
Check the return column and duplicate codes if another workbook still returns an unwanted value.
Checkpoint: Missing code is reported, not guessed.
If it still does not work
FALSE prevents an approximate neighbor price; it does not solve duplicate product codes, wrong return-column indices, spaces or type mismatches. Do not replace a missing code with a fabricated price.
Verify the result
With exact-match FALSE, P-104 returns $8.75 from its source row and missing P-107 returns #N/A rather than a neighboring price. A missing-code error is a correct warning, not a price. In your Excel copy, confirm both outcomes after changing the formula. If either result differs, recheck the lookup code, exact-match argument and source table.
Official sources
- Microsoft Excel help (checked 1 November 2026).
- Microsoft Excel help (checked 1 November 2026).
Any video panel without a marked actual-app capture is an illustrative diagram of fictional practice data.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.