VLOOKUP is one of the most useful functions in Excel. It finds a value in one column and brings back related information from the same row, such as a product’s price, an employee’s department, or a customer’s phone number.
In this guide you’ll learn exactly how VLOOKUP works, with simple examples you can copy.
What VLOOKUP does
Think of VLOOKUP like looking up a name in a phone book. You find the name on the left, then read across the row to get the number. VLOOKUP does the same thing: it searches down the first column of a range, finds your value, and returns a value from a column to the right.
The “V” stands for vertical, because it searches down a column.
The VLOOKUP syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Argument | What it means |
|---|---|
| lookup_value | The value you’re searching for (for example, a product code) |
| table_array | The range that holds your data. The search column must be the first column. |
| col_index_num | Which column to return, counting from the left of the range (1, 2, 3…) |
| range_lookup | FALSE for an exact match, TRUE for an approximate match |
Example 1: Find a product price
Here is a simple product list in A1:C6:
| Code | Product | Price |
|---|---|---|
| P100 | Keyboard | 15 |
| P101 | Mouse | 8 |
| P102 | Monitor | 120 |
| P103 | USB Cable | 3 |
| P104 | Headset | 25 |
To find the price of the code typed in E2, use:
=VLOOKUP(E2, $A$2:$C$6, 3, FALSE)If E2 contains P102, the result is 120. Excel found P102 in column A, moved across to the 3rd column of the range (Price), and returned the value.
To return the product name instead, change the column number to 2:
=VLOOKUP(E2, $A$2:$C$6, 2, FALSE)Example 2: Look up data from another sheet
Your data doesn’t have to be on the same sheet. If the product list is on a sheet called Products, include the sheet name before the range:
=VLOOKUP(A2, Products!$A$2:$C$100, 3, FALSE)The easiest way to write this is to type =VLOOKUP(A2,, then click the Products sheet tab and select the range with your mouse. Excel adds the sheet name for you.
Exact match vs approximate match
The last argument matters more than most people think.
- FALSE (exact match): finds only an identical value. Use it for codes, names, IDs and emails. This is what you want 95% of the time.
- TRUE (approximate match): finds the closest value that is smaller than or equal to your lookup value. The first column must be sorted from smallest to largest.
Approximate match is useful for ranges, like converting scores into grades:
| Min score | Grade |
|---|---|
| 0 | F |
| 50 | D |
| 65 | C |
| 75 | B |
| 85 | A |
=VLOOKUP(B2, $G$2:$H$6, 2, TRUE)A score of 78 returns B, because 75 is the largest value that is not greater than 78.
Warning: if you leave out the last argument, Excel uses TRUE. That’s a common cause of wrong results, so always type FALSE when you want an exact match.
Lock your range with $ signs
When you copy a VLOOKUP formula down a column, the range moves with it unless you lock it. Select the range in the formula bar and press F4 to add $ signs:
$A$2:$C$6Now the range stays fixed in every row.
Common VLOOKUP mistakes
- The lookup value isn’t in the first column. VLOOKUP can’t look to the left. Rearrange your columns or use XLOOKUP.
- Wrong column number. The number counts from the first column of your range, not from column A of the sheet.
- Inserting a column breaks the result. If you add a column inside the range, the column number doesn’t update, so VLOOKUP returns the wrong data.
- #N/A errors. Usually caused by extra spaces or numbers stored as text. See our guide on how to fix #N/A in VLOOKUP.
Show a message instead of #N/A
If some values may be missing, wrap VLOOKUP in IFNA:
=IFNA(VLOOKUP(E2, $A$2:$C$6, 3, FALSE), "Not found")Should you use XLOOKUP instead?
If you have Excel 365, Excel 2021 or later, or Google Sheets, XLOOKUP is easier and more flexible. It can look left, uses exact match by default, and doesn’t break when you insert columns. The same lookup looks like this:
=XLOOKUP(E2, A2:A6, C2:C6)VLOOKUP is still worth learning because you’ll see it in almost every existing workbook, and it works in every version of Excel. Read our XLOOKUP beginner guide to learn the newer function.
Quick summary
- VLOOKUP searches the first column of a range and returns a value from the same row.
- Use FALSE as the last argument for an exact match.
- Lock the range with $ signs before copying the formula.
- The column number counts from the left of your range.
- Use IFNA to replace #N/A with a friendly message.