Use a small known example
Our source data is in Products. On Orders, A2 contains P-204. The intended result is the price, 12.00.
| Row | A: ID | B: Product | C: Price |
|---|---|---|---|
| 2 | P-101 | Notebook | 4.50 |
| 3 | P-204 | Stapler | 12.00 |
| 4 | P-318 | Markers | 7.25 |
=VLOOKUP(A2,Products!$A$2:$C$4,3,FALSE)#N/A: the lookup key was not found
With exact matching, #N/A means no matching key was found in the first column of the selected range. If A2 contains P-999, #N/A is correct for this catalog. If A2 contains P-204 but the formula searches Products!B2:C4, it searches names rather than IDs. Start the range in column A.
If the key appears present, inspect both cells. A space after P-204 changes the text. TRIM can clean ordinary surrounding spaces in a helper column; it does not remove every kind of imported whitespace. For numeric IDs, a number and a text representation can fail to match. Align the source and lookup types. Preserve leading zeros when they are part of an identifier rather than blindly converting to numbers.
Only after checking the source should you consider a manual missing-value wrapper:
=IFNA(VLOOKUP(A2,Products!$A$2:$C$4,3,FALSE),"ID missing")This displays a message for #N/A; it cannot recover an absent or mistyped ID. Our generator currently returns a single VLOOKUP, without wrappers.
Wrong column: count from the start of the table
=VLOOKUP(A2,Products!$A$2:$C$4,2,FALSE)This returns Stapler, not its price. Column 2 of A:C is B. Change 2 to 3 to return 12.00. With a table in F:H, H is still return column 3. The column number is relative to the selected range, not the worksheet.
Unexpected near match: specify FALSE
A formula ending in TRUE, or one omitting the last argument, requests approximate matching. That is a different operation and can return an unintended row, particularly when the first column is not sorted for approximate lookup.
=VLOOKUP(A2,Products!$A$2:$C$4,3)
=VLOOKUP(A2,Products!$A$2:$C$4,3,FALSE)The first line leaves the match mode implicit; the second explicitly requires an exact match. For product IDs, use the second. Approximate lookup has legitimate uses such as sorted threshold tables, but this generator does not support it. See Google’s VLOOKUP reference for match-mode and data-cleaning guidance.
#REF! or errors after copying
A return column of 4 is outside a three-column A:C table and produces #REF!. Correct the column number or extend the range to include the intended data. If the first row works but copied rows fail, compare the table references: A2:C4 shifts when filled down, while $A$2:$C$4 stays fixed.
If you need to search names in B and return IDs in A, changing the column number will not enable that left lookup. Use INDEX MATCH or XLOOKUP manually.