VLOOKUP Step-by-Step
A complete walkthrough of setting up and using VLOOKUP
The Scenario
You have a product lookup table and need to find prices based on product codes:
Lookup Table (A1:C5):
| A | B | C | |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P001 | Laptop | $999 |
| 3 | P002 | Mouse | $25 |
| 4 | P003 | Keyboard | $75 |
| 5 | P004 | Monitor | $350 |
Task:
Write a VLOOKUP formula that finds the price for product code "P003".
Understanding the VLOOKUP Syntax
lookup_value
The value you're searching for (e.g., "P003")
table_array
The range containing your data (e.g., A2:C5)
col_index_num
Which column to return (1=first, 2=second, etc.)
range_lookup
FALSE = exact match, TRUE = approximate
1 Identify the Lookup Value
What are we searching for? The product code "P003".
2 Identify the Table Range
Where is our data? The lookup table is in A2:C5.
Important: The lookup value (codes) MUST be in the FIRST column of this range. That's why we start from column A.
3 Determine the Column Index
Which column contains the data we want to return?
| Column in range | Contains | Index |
|---|---|---|
| A (first) | Code | 1 |
| B (second) | Product | 2 |
| C (third) | Price | 3 |
We want the Price, which is in column 3 of our range.
4 Choose Exact or Approximate Match
Do we need an exact match or approximate?
FALSE (or 0) - Exact Match
Use when you need to find an EXACT value. Best for codes, IDs, names.
This is what we need!
TRUE (or 1) - Approximate
Finds closest match. Used for ranges like tax brackets. Requires sorted data.
Complete Formula
Result: $75 (the price of the Keyboard)
How VLOOKUP Processes This
- 1 Looks at the FIRST column of range (A2:A5) for "P003"
- 2 Finds "P003" in row 4 (cell A4)
- 3 Moves to column 3 of that row (cell C4)
- 4 Returns the value: $75
Better Practice: Use Cell References
Instead of typing "P003" directly, reference a cell containing the code:
| E | F | |
|---|---|---|
| 1 | Enter Code: | P003 |
| 2 | Price: | =VLOOKUP(F1, A2:C5, 3, FALSE) |
Now users can change the code in F1 and the price updates automatically!
Common Errors
#N/A Error
The lookup value wasn't found. Check for typos or use FALSE for exact match.
#REF! Error
Column index is larger than the table range. Make sure col_index_num doesn't exceed your columns.
Wrong value returned
Often caused by using TRUE instead of FALSE, or lookup column not being the first column.