VLOOKUP in Excel: A Simple Guide With Examples
Learn VLOOKUP step by step: the four arguments, exact vs approximate match, locking the table with $, and fixing #N/A. Plus when to use XLOOKUP instead.
By Ayoub Farid · · 2 min read
VLOOKUP is the most searched Excel function for a reason: almost every job involves finding a value in a table. The price of a product code. The department of an employee ID. The grade for a score. Here's how it works, without the jargon.
What VLOOKUP does
It looks for a value in the first column of a table, finds that row, and returns the value from a column you choose to the right.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Argument | Plain English |
|---|---|
| lookup_value | What you're looking for (e.g. "P-104") |
| table_array | The table, with the lookup column first |
| col_index_num | Which column to return, counting from 1 |
| range_lookup | FALSE for exact match (use this 95% of the time) |
Example
| A | B | C | |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P-101 | Espresso | 2.50 |
| 3 | P-102 | Latte | 3.50 |
| 4 | P-103 | Tea | 2.00 |
With "P-102" in F2:
=VLOOKUP(F2,A2:C4,3,FALSE) → 3.50Column 3 of the table is Price. Change 3 to 2 to get "Latte".
Lock the table before filling down
If you look up a whole list of codes and fill the formula down, the table range will shift (A3:C5, A4:C6…) and lookups at the bottom fail. Lock it:
=VLOOKUP(F2,$A$2:$C$4,3,FALSE)This is the number-one VLOOKUP mistake in job tests. See absolute references.
Exact vs approximate match
- FALSE (or 0): exact match. Returns
#N/Aif not found. Use for codes, names, IDs. - TRUE (or omitted): approximate. Finds the largest value not greater than the lookup value. Only for sorted bands like tax brackets or grade thresholds.
Leaving the last argument out means TRUE, which silently returns wrong answers on unsorted data. Always type FALSE unless you mean bands.
Handle "not found"
=IFERROR(VLOOKUP(F2,$A$2:$C$100,3,FALSE),"Not found")See IFERROR. If you get #N/A when the value is there, read VLOOKUP not working.
Lookup from another sheet
=VLOOKUP(A2,Prices!$A$2:$C$500,3,FALSE)VLOOKUP's limits
- It can only look right. If the value you want is left of the lookup column, use INDEX MATCH or XLOOKUP.
- The column number breaks if someone inserts a column in the table.
- It returns only the first match.
That's why Microsoft introduced XLOOKUP. Compare them in XLOOKUP vs VLOOKUP. Still, VLOOKUP works in every Excel version and is still expected in many job tests, so learn both.
Practise it
Lookups (VLOOKUP, XLOOKUP, INDEX/MATCH) are a coming unit in Cellio. The live units already train the part people get wrong: locking ranges with $ before filling down.
Frequently asked questions
How do I do a VLOOKUP in Excel?
=VLOOKUP(what, table, column number, FALSE), e.g. =VLOOKUP(F2,$A$2:$C$100,3,FALSE).
Why does VLOOKUP return #N/A?
The value isn't found exactly: extra spaces, numbers stored as text, or the value isn't in the first column of the table.
What does FALSE mean in VLOOKUP?
Exact match. TRUE means approximate match on sorted data.
Should I use VLOOKUP or XLOOKUP?
XLOOKUP if everyone uses Excel 2021, Microsoft 365 or Google Sheets. VLOOKUP if files must work in older versions.