VLOOKUP in Practice
A real, working example of the lookup function every intermediate Excel user needs to know.
Compatibility: All Excel versions
What You'll Learn
- How VLOOKUP finds and returns a value from another table
- Why you should almost always use FALSE for an exact match
- How to handle a VLOOKUP that doesn't find a match
VLOOKUP looks up a value in the first column of a range and pulls back a value from a column you specify, in the same row. A common real-world use is pulling a product's price from a separate lookup table based on its ID:
=VLOOKUP(A2,ProductTable,3,FALSE)
This looks for the value in A2 within the first column of ProductTable, and returns the value from the 3rd column of that table, in the matching row.
Always Use FALSE for an Exact Match
The last argument controls matching: FALSE requires an exact match (what you want almost every time — matching a product ID or an employee number). TRUE allows an approximate match, which is rarely what you actually want and can silently return the wrong row if you forget to specify it.
A Common Error: #N/A
If VLOOKUP can't find the lookup value at all, it returns #N/A. Wrap it in IFERROR (covered in the Formula Library) to show something more useful instead, like "Not Found".
Common Mistakes
- Leaving out FALSE (or accidentally using TRUE) and getting an incorrect approximate match
- Forgetting that VLOOKUP can only look to the right of its lookup column, never to the left
Practice Exercise
Build a small product lookup table (ID, Name, Price) and a separate order list referencing product IDs. Use VLOOKUP to pull each order's product name and price automatically.
Key Takeaways
- VLOOKUP searches the first column of a range and returns a value from a column you specify
- Always use FALSE unless you specifically need an approximate match
- VLOOKUP returns #N/A when no match is found — wrap it in IFERROR to handle that gracefully