VLOOKUP
Searches the first column of a table for a match, and returns a value from another column in that same row.
VLOOKUP is still the lookup function most people learn first, and it's still the one you'll see in the most inherited spreadsheets: templates, legacy models, other people's work. Even with XLOOKUP now available, knowing VLOOKUP cold is table stakes for any Excel-heavy finance role, because you'll be reading other people's VLOOKUPs far more often than you'll be choosing to write a new one.
The plain-English version: VLOOKUP looks down the first column of a range for a match, then reads across that same row to a column you specify, by number, counted from the lookup column. That counting-by-number part is both the whole mechanism and, as below, the whole problem.
Syntax
| Argument | Description |
|---|---|
| lookup_value | Required. What to search for. Must match something in the table's FIRST column. |
| table_array | Required. The range to search, starting at the lookup column, e.g. A2:C10. |
| col_index_num | Required. Which column of table_array to return, counted from 1 at the lookup column. |
| range_lookup | Optional. FALSE for an exact match (almost always what you want); TRUE or omitted allows an approximate match, which needs the first column sorted. |
The mistake almost everyone makes
Omitting the fourth argument, range_lookup. Leave it out (or leave it TRUE) and VLOOKUP switches to an approximate match: it assumes your data is sorted and will happily return the closest value below your search term instead of telling you there's no exact match. That's the dangerous part: it doesn't error. It returns a plausible-looking wrong number, which can sit in a model for weeks before anyone notices the data wasn't actually sorted the way VLOOKUP assumed.
Always write FALSE explicitly for an exact match, even though it's the last argument and easy to forget once you're moving fast.
A real example: pulling a bond's rating from a master ratings table
Say you're building a fixed-income portfolio report and need to attach each holding's credit rating from a separate master ratings tab, keyed by CUSIP:
This searches the CUSIP in A2 against column A of the ratings table and returns the rating from column C, the third column counting from the lookup column, not the sheet's actual column C. If a new column ever gets inserted into the middle of that ratings table, this 3 silently points at the wrong column with no warning at all, which is exactly why INDEX/MATCH (below) is worth learning as the more durable alternative once you've got VLOOKUP down.
Worked examples
| A | B | C | |
|---|---|---|---|
| 1 | SKU | Product | Price |
| 2 | A100 | Notebook | 3.5 |
| 3 | A200 | Pen | 1.2 |
| A | B | |
|---|---|---|
| 1 | ID | Name |
| 2 | 1 | Aya |
Common mistakes
- Omitting FALSE and getting a plausible-looking WRONG answer instead of an error. Always pass FALSE for exact matches.
- Pointing table_array at a range that starts after the lookup column instead of at it.
- Hardcoding col_index_num, then having it silently point at the wrong column after inserting a new column into the table.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice VLOOKUP →