An incorrect price can be harder to spot than an error. Test an absent code before copying a formula down a long order list.
Name the columns relative to the lookup table
This original fictional price table occupies A2:C4, beneath headers in row 1:
| A: Code | B: Item | C: Unit price |
|---|---|---|
| P100 | Notebook | 12 |
| P200 | Binder | 18 |
| P300 | Folder | 6 |
Cell E2 contains the code to price. The formula for F2 is:
=VLOOKUP(E2,$A$2:$C$4,3,FALSE)
The lookup searches column A, the first column of the selected table. The index 3 returns column C, the third column within A:C. The dollar signs keep the lookup range fixed when the formula is copied to another row; E2 remains relative so the next row looks up E3.
Microsoft's VLOOKUP reference, checked October 24, 2026, documents the lookup column, relative return-column index and match modes. Approximate matching expects the first column to be sorted. Exact product-code matching does not need an approximate fallback.
If your source begins in H rather than A, an index of 3 still means the third column of that supplied source range. It does not mean worksheet column C. Supply the real range before asking the model to explain an index.
Test an existing code and a missing code
| E2 input | Expected exact-match result | Meaning |
|---|---|---|
| P200 | 18 | The listed Binder price |
| P250 | #N/A | No exact product code exists in this fixture |
| P100 | 12 | The first listed code is still found |
For the sorted teaching table, an approximate lookup of P250 can choose the preceding P200 row and return 18. That would be the wrong rule for this price task. The absent-key test exposes the difference even when the present-key test passes.
These values are authored expectations, not an observed Excel session. Use your actual workbook's values when checking a delivered formula.
Decide how a missing price should appear
Keep #N/A visible while diagnosing the lookup. Replacing every error with 0 would make a missing price look like a free item and could hide an unrelated formula defect. If the finished workbook needs a friendly missing-code label, first agree on that label and use targeted error handling after confirming the basic formula.
If a listed code still fails, compare the stored values and types on both sides. Text, numeric codes and unwanted spaces need a deliberate correction policy. Do not ask ChatGPT to strip characters from every identifier before establishing which characters belong to the real code.
Write and explain an Excel VLOOKUP for this permitted sample.
Lookup cell and value: [ENTER]
Exact source range with headers, values and stored code types: [PASTE]
Required return column and what it means: [ENTER]
Match rule: exact product code only.
Missing-code rule: keep #N/A during diagnosis.
Formula separator used by my Excel locale: [COMMA OR SEMICOLON]
Return the formula, explain each argument, and show which references
remain fixed when copied down. Test a present code and an absent code
using the supplied data. Do not invent prices, use approximate fallback,
turn missing prices into zero, or claim workbook execution. If the
return column is left of the key, explain the limitation before choosing
another function. Keep source data unchanged.
The request follows OpenAI's prompting guidance by supplying the task and the expected boundaries. For a broader workbook formula review, the Excel guide covers scoped changes and comparison checks. Here, finish with both the 18 result for P200 and the visible missing result for P250 before copying the reviewed formula into the maintained workbook.