VLOOKUP function
VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row. It is the classic Excel lookup and still sits in millions of files, but it can only look to the right, relies on a column number that breaks when columns move, and does an approximate match unless you add FALSE.
- Available in
- Every current version
- Category
- Lookup functions
- Returns
- The value from the matching row, or #N/A when there is no match.
- Google Sheets
- Works the same way in Google Sheets.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | Required? | What it is |
|---|---|---|
lookup_value | Required | The value to find in the first column of the table. |
table_array | Required | The table to search. The column you search must be its leftmost column. |
col_index_num | Required | Which column of the table to return, counting the first column as 1. |
range_lookup | Optional | FALSE for an exact match. TRUE or left out for an approximate match, which needs the first column sorted smallest to largest. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| H2 | =VLOOKUP("E-103",A2:C5,2,FALSE) | Lee Park | Exact match: the name for an employee ID |
| H3 | =VLOOKUP(12000,E2:F5,2,TRUE) | 5% | Approximate match: $12,000 falls in the $10,000 tier, so the rate is 5% |
| H4 | =VLOOKUP("E-110",A2:C5,3,FALSE) | #N/A | An ID that isn't in the table returns the #N/A error |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Emp ID | Name | Dept | Sales from | Commission | Result | ||
| E-101 | Ana Ruiz | Sales | $0 | 2% | Lee Park | ||
| E-102 | Raj Patel | Finance | $5,000 | 3% | 5% | ||
| E-103 | Lee Park | Sales | $10,000 | 5% | #N/A | ||
| E-104 | Kim Osei | Ops | $25,000 | 7% |
Practice VLOOKUP
From lesson 9, VLOOKUP. Graded instantly, then re-checked on changed data.
Look up product names
G2, look up the product name for the SKU in F2. Fill down to G4.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| A wrong answer with no error | The fourth argument was left out, so VLOOKUP did an approximate match on unsorted data. Add FALSE for exact matches. |
| #N/A | The value isn't in the first column, or it differs by a trailing space or by being text in one place and a number in the other. Clean the data with TRIM or VALUE. |
| #REF! | col_index_num is larger than the number of columns in table_array. Widen the table range or lower the number. |
| Results break after inserting a column | The column number doesn't move, so it now points at the wrong column. XLOOKUP and INDEX/MATCH reference the return column directly and don't have this problem. |
Tips
- Lock the table with $ ($A$2:$C$5) before filling the formula down.
- For a tier table, sort the first column smallest to largest and use TRUE: VLOOKUP returns the row for the largest value that is less than or equal to the lookup value.
Questions
What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP takes separate search and return ranges, so it can look left and doesn't break when columns move, and it defaults to an exact match. Choose XLOOKUP in Excel 2021 or later; use INDEX/MATCH when the file must work in older versions.
Why does VLOOKUP return the wrong value?
Usually because range_lookup was left out, so it did an approximate match. Add FALSE as the fourth argument.
Can VLOOKUP look to the left?
No. The search column must be the first column of the table. Use XLOOKUP or INDEX and MATCH to return a column to its left.
Lessons that use VLOOKUP
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.