VLOOKUP exact match: find a price by product code

Start with a code, not a row number

A repair shop keeps a price list for replacement parts. A customer asks for part P-240. You could scan the list, but a lookup is more useful when the same report has dozens of requested codes. Put the following table in A1:C4.

Ask for an exact match

The first argument is the code to find. A2:C4 is the lookup table. The number 3 means return the third column within that table, which is the price. FALSE requests an exact match. The answer is 28.

=VLOOKUP("P-240",A2:C4,3,FALSE)

Why the last argument matters

A product code identifies a particular item. A nearby code is not an acceptable substitute. In Excel, omitting the last VLOOKUP argument selects approximate matching. That behaviour is useful for sorted thresholds, but it is the wrong choice for this price list. Write FALSE explicitly when the code must match.

The return-column number is relative to the table. If your table starts in D and ends in F, its third column is F, not C. Check the selected table whenever a lookup returns the right row but the wrong field.

Separate a missing code from a zero price

The code P-999 does not appear in the list. An exact lookup returns #N/A. Do not replace every missing result with zero: zero could be interpreted as a free part. IFNA lets you return a readable label for a missing match while leaving other error types visible.

=IFNA(VLOOKUP("P-999",A2:C4,3,FALSE),"Check code")

Use an input cell

Enter the requested code in E2. Use E2 as the lookup value and lock the source range if the formula will be copied down. Changing E2 from P-240 to P-390 should change the answer from 28 to 45.

=VLOOKUP(E2,$A$2:$C$4,3,FALSE)

Check duplicate codes before relying on the result

An exact match is not a uniqueness check. If the table contains the same code twice, VLOOKUP returns the first matching row. Two prices for P-240 are a data-quality problem that the lookup cannot resolve for you.

VLOOKUP searches the first column of its table and returns a field to its right. If you need to search a later column and return one to its left, use a different lookup arrangement, such as INDEX with MATCH, rather than inventing a negative column number.