VLOOKUP in Excel: how to use it step by step with examples
How to use VLOOKUP in Excel: syntax, a practical example, exact match, the most common #N/A errors and the modern alternative, XLOOKUP.
Quick answer
VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row. The formula is =VLOOKUP(lookup_value, table_array, col_index_num, FALSE). Use FALSE for an exact match. In recent versions of Excel, XLOOKUP does the same job and can look in any direction.
Key takeaways
- Syntax: =VLOOKUP(value, range, column, FALSE).
- The lookup value must be in the first column of the range.
- FALSE = exact match, which is almost always what you need.
- XLOOKUP is the modern, more flexible version.
VLOOKUP lets you “pull” data from a table: a product’s price by its code, a customer’s phone by name, or a student’s grade by ID.
The syntax
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
| Part | What it is |
|---|---|
| lookup_value | What you’re looking for (for example, the code in H2) |
| table_array | The table to search; the value must be in its first column |
| col_index_num | Which column of the range to return (1 is the first) |
| FALSE | Exact match |
Example
| A (Code) | B (Product) | C (Price) | |
|---|---|---|---|
| 2 | P-001 | Pencil | 0.50 |
| 3 | P-002 | Notebook | 2.25 |
| 4 | P-003 | Backpack | 18.00 |
If H2 contains P-002 and I2 has:
=VLOOKUP(H2, A2:C4, 3, FALSE)
the result is 2.25, the notebook’s price.
Tip: lock the range as $A$2:$C$4 before copying the formula down so it doesn’t shift.
Common errors
- #N/A: the value isn’t in the first column, has extra spaces, or is a number stored as text.
- #REF!: the column number is larger than the number of columns in the range.
- Wrong result: you left out FALSE and Excel used an approximate match.
To replace #N/A with a message:
=IFERROR(VLOOKUP(H2, $A$2:$C$4, 3, FALSE), “Not found”)
The modern alternative: XLOOKUP
In Microsoft 365 and recent versions, XLOOKUP looks in any direction and uses exact match by default:
=XLOOKUP(H2, A2:A4, C2:C4, “Not found”)
Review the other basics in Excel formulas.
Frequently asked questions
Why does VLOOKUP return #N/A?
It didn't find the value. Check for extra spaces, numbers stored as text, that the value is in the first column of the range, and that you used FALSE for an exact match.
What does FALSE at the end mean?
It asks for an exact match. With TRUE, Excel looks for an approximate match and the first column must be sorted in ascending order.
Can VLOOKUP look to the left?
No. It only returns columns to the right of the lookup column. To look left, use XLOOKUP or INDEX with MATCH.
Sources
- VLOOKUP function Microsoft Support
This article is general and educational; it is not personalized financial, legal or tax advice. Financial product terms change: always confirm current terms with the institution.