VLOOKUP explained, with practice exercises
VLOOKUP looks for a value in the first column of a table and returns a value from the same row. For example the price for an item number.
In short: =VLOOKUP(lookup_value,table,column_number,FALSE). The search always happens in the first column of the table, the column number counts from that column, and FALSE means exact match.
Try it now
The sheet contains a price list. Find the price for the item number in E2. Click the yellow cell and write the formula.
Task In F2, use VLOOKUP to find the price of the item whose number is in E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item no. | Item | Price $ | Lookup | Price | |
| 2 | A-101 | Ballpoint pen | 4.9 | A-104 | ||
| 3 | A-104 | Stapler | 19.9 | |||
| 4 | A-103 | Notepad | 7.2 | |||
| 5 | A-102 | Binder | 12.5 | |||
| 6 | A-105 | Eraser | 3.4 |
The VLOOKUP formula
| Argument | Meaning | In the example |
|---|---|---|
| lookup_value | The value you are looking for | E2 |
| table_array | The table to search. Its first column must contain the lookup values. | A2:C6 |
| col_index_num | Which column of the table the result comes from, counted from the table's first column | 3 |
| range_lookup | FALSE for exact match, TRUE or empty for approximate match on sorted data | FALSE |
One more: department by employee ID
Task F2 should show the department of the employee whose ID is in E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee ID | Name | Department | Employee ID | Department | |
| 2 | 1012 | Murat Kaya | Sales | 1031 | ||
| 3 | 1007 | Jana Weber | Purchasing | |||
| 4 | 1023 | Lena Hoffmann | Finance | |||
| 5 | 1044 | Sara Klein | Sales | |||
| 6 | 1031 | Tom Schulz | HR |
Common VLOOKUP errors
#N/A
Excel did not find the lookup value in the first column. Typical causes are typos, trailing spaces or numbers stored as text. If a missing value is normal, wrap the formula: =IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),"not found").
Forgetting FALSE
Without the fourth argument VLOOKUP does an approximate match. On unsorted data that often returns a wrong value without any error message. For IDs, names and item numbers always use FALSE. What approximate match is actually for is covered in VLOOKUP approximate match.
The lookup column is not the first column
VLOOKUP can only search the first column of the table. Values to the left of it cannot be returned. Use XLOOKUP or INDEX with MATCH for that.
References move when copying
When you fill the formula down, the table moves with it. Lock it with dollar signs: =VLOOKUP(E2,$A$2:$C$6,3,FALSE).
Which Excel versions have VLOOKUP?
All of them, including Excel 2016 and 2019, Excel for Mac and Excel for the web. In a German Excel the function is called SVERWEIS and arguments are separated by semicolons.