The VLOOKUP function in Excel
VLOOKUP finds a value in the first column of a table and returns something from the same row.
What VLOOKUP does
You give it the value to find, the table to search and the number of the column to return. It always searches the first column of the table, so the thing you look up has to sit on the left. Use FALSE as the last argument to ask for an exact match.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value
- What to find, such as an ID in cell E2.
- table_array
- The table to search. The lookup value must be in its first column.
- col_index_num
- Which column of the table to return, counting from 1 at the left.
- range_lookup
- Use FALSE for an exact match. Leaving it out can return wrong answers.
Worked example
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Look up ID | Price | |
| 2 | 1001 | Notebook | 8 | 1007 | 40 | |
| 3 | 1004 | Desk lamp | 25 | |||
| 4 | 1007 | Backpack | 40 | |||
| 5 | 1010 | Headphones | 60 |
ID 1007 is found in the first column. Price is the third column of the table, so VLOOKUP returns 40.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Sales & Ops
Pull a price or a customer name from a list using an ID.
Finance
Match account codes to account names.
HR
Look up an employee's department from their ID.
Common mistakes
- Leaving out FALSE. Without it Excel looks for the nearest match, which gives wrong answers on unsorted data.
- The lookup value is not in the first column of the table. VLOOKUP cannot look to the left.
- Inserting a column into the table, which can change which column number you needed.
- Spaces or numbers stored as text that stop two values matching, which gives #N/A.
The VLOOKUP course
- 1Look up a price by IDFree practice belowPractise
- 2Approximate match for tax bandsComing soon
- 3Look up across sheetsComing soon
- 4Fix #N/A errorsComing soon
- 5Return several columnsComing soon
- 6Common VLOOKUP trapsComing soon
Common questions
Why does VLOOKUP return #N/A?
The value was not found. Common causes are a typo, a trailing space, a number stored as text, or the lookup value not being in the first column.
Should I use XLOOKUP instead?
If your version of Excel has it, yes, because it is more flexible. VLOOKUP is still everywhere in older workbooks, so it is worth knowing.