Excel function guide · Lookup
VLOOKUP function in Excel
VLOOKUP looks for a value in the first column of a table and returns the value from another column on the same row, chosen by its number. Add FALSE as the last argument to require an exact match.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
VLOOKUP works in every version of Excel. In Microsoft 365 and Excel 2021 or later, XLOOKUP does the same job with fewer pitfalls.
| Argument | Required? | What it means |
|---|---|---|
| lookup_value | Required | The value to find in the first column of the table. |
| table_array | Required | The whole table, starting with the column you search in. |
| col_index_num | Required | Which column of the table to return: 1 is the first column of table_array, 2 the next, and so on. |
| range_lookup | Optional | FALSE (or 0) for an exact match. TRUE or left out means approximate match, which needs the first column sorted ascending. |
VLOOKUP examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Product | Category | Price |
| 2 | P-318 | Monitor arm | Accessories | 85 |
| 3 | P-110 | Desk lamp | Lighting | 39 |
| 4 | P-517 | Webcam | Electronics | 65 |
| 5 | P-205 | Office chair | Furniture | 240 |
| 6 | P-402 | Standing desk | Furniture | 520 |
1. Price of a product
=VLOOKUP("P-205", A2:D6, 4, FALSE)Result: 240
VLOOKUP finds P-205 in column A of the table and returns the 4th column of the table, Price.
2. Name of a product
=VLOOKUP("P-517", A2:D6, 2, FALSE)Result: Webcam
Same lookup, column 2. The column number counts from the first column of table_array, not from column A of the sheet.
3. A code that isn't there
=VLOOKUP("P-300", A2:D6, 4, FALSE)Result: #N/A
With FALSE, a missing code gives #N/A, which is honest. Wrap it in IFERROR or IFNA to show a message instead.
Try it now
Practise VLOOKUP in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 3
In F3, return the product name for the SKU in F2 with VLOOKUP. The list isn't sorted, so ask for an exact match.
Common VLOOKUP mistakes
Leaving out FALSE
Without the fourth argument VLOOKUP uses approximate match and assumes the first column is sorted. On an unsorted list it can return the wrong row, or #N/A, with no warning. Write FALSE for ID and code lookups.
Looking to the left
VLOOKUP only searches the first column of table_array and only returns columns to its right. To return a column on the left, use XLOOKUP or INDEX and MATCH.
A hard-coded column number
If someone inserts a column inside the table, col_index_num 4 now points at a different column. XLOOKUP and INDEX/MATCH refer to the return column directly.
Not locking the table
When you fill the formula down, A2:D6 becomes A3:D7. Use $A$2:$D$6 so the table stays put.
Column number too big
A col_index_num larger than the number of columns in table_array returns #REF!.
Practise this in Excel Quest
VLOOKUP (and why FALSE matters) Members
Course 6 · Lookups & Relationships · 12 min
Price an order list from a product catalog with VLOOKUP, fix an approximate-match bug and learn its limits.
This lesson is included in Formula Quest memberships. A free account starts you on the free lessons of the Excel Quest curriculum.