Excel VLOOKUP Exact Match: Find a Price Without Guessing

A lookup can save you from repeatedly typing prices or names. It can also return a plausible wrong answer if you choose the wrong matching rule. Excel VLOOKUP exact match is a useful beginner technique when each product code should point to one known record.

Before you start: Use a new practice workbook. The names and figures below are fictional. Enter the headers in row 1 and the first record in row 2 unless a step says otherwise. Formulas use English function names and commas; your regional settings may require semicolons instead. Save a copy before experimenting.

Create a small reference list

Enter the reference table in A1:C5. The product code is in the first column because VLOOKUP searches the first column of its table range and returns a value from a column to its right.

Code	Product	Price
P101	Notebook	120
P102	Pen	25
P103	Folder	80
P104	Marker	150

Keep each code unique in this reference table. A lookup result cannot resolve a business disagreement about two different prices for the same supposedly unique code. Check duplicates before using the list as an authority.

Look up one code

Type Code to find in E1 and P103 in E2. In F1 type Price and in F2 enter =VLOOKUP(E2,$A$2:$C$5,3,FALSE). The result should be 80.

The first argument is the lookup value. The second is the reference table. The third argument, 3, means return the third column within that selected table, which is Price. It does not mean worksheet column C in every possible formula; it is a position counted from the left edge of the lookup range.

FALSE requests an exact match. This is the right choice for a product code that must exist in the list. If you omit the final argument, VLOOKUP defaults to approximate matching, which has different requirements and can return an unintended result for this task.

Copy the lookup safely

Enter P101 in E3 and P104 in E4, then copy F2 down through F4. The lookup value should change to E3 and E4, while the reference range remains $A$2:$C$5. The prices should be 120 and 150.

The dollar signs keep the reference list fixed. Without them, copying down would shift the table range and could leave valid products outside the search area. Inspect the last copied formula to confirm that its table range still covers the complete list.

Recognise a missing match

Try P999 in E5 and copy the formula into F5. VLOOKUP returns #N/A because no exact code matches. That error is useful information: the requested item is not found in the current reference list.

You can display a clearer message with =IFNA(VLOOKUP(E5,$A$2:$C$5,3,FALSE),"Code not found"). IFNA handles the not-available error while leaving other formula errors visible. Do not replace every missing price with zero unless zero is truly the agreed business meaning.

Investigate a match that should exist

Check for extra spaces, inconsistent data types and the correct lookup column. A code stored as text can differ from a numeric code with similar-looking digits. Leading zeros may be meaningful, so do not automatically convert every code into a number.

VLOOKUP exact matching is not case-sensitive. It also returns the first matching record if the reference list contains duplicates. Those behaviours mean it is not a complete data-quality check. Use a genuinely unique code and investigate duplicate reference records separately.

Know the limits of the layout

VLOOKUP cannot return a column to the left of its search column within the selected table. Its numeric return-column index can also need adjustment if the reference layout changes. Newer Excel versions offer other lookup tools, but understanding the matching rule and key quality remains important with any function.

When extending the reference list, update the range or use an Excel Table reference that grows with the list. A correct formula against an incomplete list is still an incomplete lookup.

Try it yourself

Use P102 as the code to find. Write one VLOOKUP that returns the product name and another that returns the price. Explain which argument changes and why FALSE should stay in both formulas.

Check your answer

With P102 in E2, =VLOOKUP(E2,$A$2:$C$5,2,FALSE) returns Pen. =VLOOKUP(E2,$A$2:$C$5,3,FALSE) returns 25.

Only the return-column index changes: Product is column 2 within A:C and Price is column 3. FALSE stays because both questions require the exact product code. Sorting the reference table is not required for this exact-match example.

Keep learning: Return to the Excel learning path to choose your next lesson.

Further reading: Microsoft VLOOKUP reference.