VLOOKUP in Excel
Last updated
VLOOKUP looks for a value in the first column of a table and returns the value from another column in the same row. For example, =VLOOKUP(E2,A2:C9,3,FALSE) finds the product code in E2 and returns its price from the 3rd column. Use FALSE for an exact match.
VLOOKUP syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value
- The value to find, such as a product code. It must be in the first column of the table.
- table_array
- The table to search, e.g.
A2:C9. Lock it with$($A$2:$C$9) before copying the formula. - col_index_num
- Which column of the table to return: 1 is the first column of the range, not column A.
- range_lookup
FALSE(or 0) for an exact match.TRUE(or omitted) for approximate match on a sorted first column, used for bands and brackets.
Practice VLOOKUP here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Find a product’s price
In F2, write a VLOOKUP that returns the price of the code in E2, using the table in A1:C9.
| Code | Product | Price ($) | Code | Price ($) | |
| P-101 | Wireless mouse | 24.90 | P-104 | ||
| P-102 | USB-C cable | 9.50 | |||
| P-103 | Keyboard | 49.00 | |||
| P-104 | 24" monitor | 189.00 | |||
| P-105 | Laptop stand | 35.00 | |||
| P-106 | HD webcam | 59.90 | |||
| P-107 | Headset | 79.00 | |||
| P-108 | Desk lamp | 32.50 |
How to do a VLOOKUP, step by step
- Click the cell where you want the result and type
=VLOOKUP(. - Click the cell that holds the value to find, then type a comma.
- Select the table, starting with the column that contains the value to find. Press F4 to lock it with $.
- Type the column number to return, a comma, then
FALSE, and press Enter.
VLOOKUP examples
Price from a product code
=VLOOKUP(E2,$A$2:$C$9,3,FALSE)
Returns the price of the code in E2.
Look up from another sheet
=VLOOKUP(A2,Products!$A:$C,3,FALSE)
The table can live on another sheet: prefix the range with the sheet name and an exclamation mark.
Commission by sales band
=VLOOKUP(D2,$A$2:$B$6,2,TRUE)
With TRUE, VLOOKUP returns the largest band that is less than or equal to D2. The first column must be sorted ascending.
Show a message instead of #N/A
=IFERROR(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"Not found")
IFERROR replaces any error with your own text.
Common VLOOKUP errors and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #N/A | The value isn’t in the first column, has extra spaces, or is a number stored as text. Or the range moved when you copied the formula. | Check the value, use TRIM, convert text to numbers, and lock the range with $. |
| #REF! | col_index_num is larger than the number of columns in the table. | Widen the range or lower the column number. |
| #VALUE! | col_index_num is less than 1. | Use a column number of 1 or more. |
| Wrong value, no error | range_lookup is missing or TRUE on an unsorted table. | Add FALSE as the last argument. |
VLOOKUP vs XLOOKUP
XLOOKUP (Excel 2021 and Microsoft 365) can look to the left, defaults to exact match and doesn’t break when you insert a column. VLOOKUP still works in every version and in most files you’ll receive at work, so you need to know both.
Frequently asked questions
Why does my VLOOKUP return #N/A?
The lookup value isn’t found in the first column of the range. The usual causes are extra spaces, numbers stored as text, or a range that shifted when the formula was copied. Lock it with $.
Can VLOOKUP look to the left?
No. VLOOKUP only returns columns to the right of the lookup column. Use XLOOKUP or INDEX and MATCH to look left.
What does FALSE mean in VLOOKUP?
FALSE asks for an exact match. Without it, VLOOKUP uses approximate match and can return a wrong row without any error.
How do I use VLOOKUP between two sheets?
Add the sheet name before the range: =VLOOKUP(A2,Sheet2!$A:$C,3,FALSE).
Keep practising
All 10 VLOOKUP exercises, with explanations and progress tracking.
Open the VLOOKUP practice set