The VLOOKUP function in Excel
VLOOKUP searches down the first column of a table for a value, then returns something from the same row in another column. Use it to pull a price by product code, a salary by employee ID, or a name by email.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| lookup_value | What you're looking for, like an ID in another cell. |
|---|---|
| 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: 1 is the first column, 2 the second, and so on. |
| range_lookup | FALSE for an exact match (what you want almost every time). TRUE or omitted means approximate match. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
In G2, use VLOOKUP to find the salary for the employee ID in F2.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Salary | Salary | ||||||
| 2 | ||||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 |
Tip: Salary is the 4th column of A2:D6. Don't forget FALSE.
Examples
=VLOOKUP(103,A2:D6,2,FALSE)→ "Chloé Martin"Finds ID 103 and returns column 2 of the table: the name.
=VLOOKUP(105,A2:D6,4,FALSE)→ 70300Same row lookup, but column 4 returns the salary.
=IFERROR(VLOOKUP(999,A2:D6,2,FALSE),"Not found")→ "Not found"Wrap in IFERROR to show a friendly message instead of #N/A.
Example data
| 1 | ID | Name | Department | Salary |
|---|---|---|---|---|
| 2 | 101 | Ana Ruiz | Finance | 64000 |
| 3 | 102 | Ben Okoro | Sales | 58500 |
| 4 | 103 | Chloé Martin | Ops | 61200 |
| 5 | 104 | Dev Patel | Sales | 55900 |
| 6 | 105 | Eun-ji Kim | Finance | 70300 |
Common mistakes
Leaving out FALSE
Without
FALSE, VLOOKUP does an approximate match and can silently return the wrong row. Always finish with,FALSE)for IDs, names and codes.The lookup column isn't first
VLOOKUP only searches the leftmost column of
table_array. Start the range at the lookup column, or use XLOOKUP or INDEX/MATCH.Range moves when you fill down
Lock the table with
$, as in$A$2:$D$6, before copying the formula to other rows.#REF! error
col_index_numis bigger than the number of columns in your table. Count columns from the left edge of the range.
FAQ
What does the FALSE in VLOOKUP mean?+
It asks for an exact match. If the value isn't found you get #N/A, instead of a wrong answer from a nearby row.
Can VLOOKUP look to the left?+
No. It only returns columns to the right of the lookup column. Use XLOOKUP or INDEX/MATCH to look left.
VLOOKUP or XLOOKUP?+
XLOOKUP is newer and more flexible, but VLOOKUP still appears in most interview tests and older workbooks, so know both.
Lesson
Master VLOOKUP step by step: VLOOKUP exact match
Pull product names and prices from a catalog with VLOOKUP, and lock the table so the formula fills down cleanly.