Excel VLOOKUP function: find a value in a table
VLOOKUP finds a value in the first column of a table and returns a value from another column in the same row. =VLOOKUP(E2,$J$2:$L$4,2,FALSE) looks up Dana's region and returns its manager. Always end with FALSE for an exact match.
The lookup examples use this small table in J1:L4, next to the sales data:
| J Region | K Manager | L Rate | |
|---|---|---|---|
| 2 | East | Ann Cole | 5% |
| 3 | West | Ben Ortiz | 4% |
| 4 | North | Cara Lim | 6% |
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value: what to find, like E2 (East).
- table_array: the table. The value you look up must be in its first column.
- col_index_num: which column to return, counting from the left of the table. Manager is 2, Rate is 3.
- range_lookup: FALSE for exact match. Leave it out and Excel uses approximate match.
Examples
| Formula | Result |
|---|---|
=VLOOKUP(E2,$J$2:$L$4,2,FALSE) | Ann Cole |
=VLOOKUP("West",$J$2:$L$4,2,FALSE) | Ben Ortiz |
=B2*VLOOKUP(E2,$J$2:$L$4,3,FALSE) | 62.5 |
Why FALSE matters
Without FALSE, VLOOKUP assumes the first column is sorted and returns the closest smaller match. On unsorted data that gives wrong answers with no error. It's the most common VLOOKUP bug, and tests check for it.
Why the $ signs
When you copy the formula down to the next rep, J2:L4 would shift to J3:L5 and miss East. $J$2:$L$4 keeps it fixed. See absolute references.
VLOOKUP's limits
- It can only look to the right. To return a column left of the lookup column, use XLOOKUP or INDEX and MATCH.
- The column number is fixed, so inserting a column inside the table breaks it.
- When there's no match, it returns #N/A. Wrap it in IFERROR for a friendly message.
Try it: Look up the manager
Columns J to L are a lookup table of regions and managers. In C2, use VLOOKUP to show the manager for Dana's region (E2).
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
Why does VLOOKUP return #N/A?
The value isn't in the first column of the table, often because of extra spaces, a typo, or a number stored as text. It also happens when the table range is missing $ signs and shifted when copied.
Should I use VLOOKUP or XLOOKUP?
Use XLOOKUP if everyone has Excel 2021, Microsoft 365 or Google Sheets. Use VLOOKUP if the file needs to work in older Excel, or if a test asks for it by name.
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How XLOOKUP works in Excel and Google Sheets, how it compares to VLOOKUP, the built-in not-found value, which versions have it, and a live exercise.
How INDEX and MATCH work together in Excel, why analysts use it instead of VLOOKUP, the 0 that makes it exact, and a live exercise.
How to use IFERROR in Excel to replace #N/A, #DIV/0! and other errors, when not to use it, and a live exercise with VLOOKUP.