VLOOKUP in Excel: how to use it
With VLOOKUP, Excel looks something up in a table: it finds your code on the left and reads the price or name next to it. End with FALSE for an exact match.
Try the first exercises for freeIn the lesson Looking up with VLOOKUP
What does VLOOKUP do?
VLOOKUP looks up a value in the first column of a table and gives you something from the same row. You tell Excel what you're looking for, in which table, which column you want the answer from, and whether it has to be an exact match.
The V stands for vertical, the way it searches: Excel goes down the first column from top to bottom until it finds the value. Then it moves right along that row, to the column you gave.
When do you use VLOOKUP?
- The price for a product code from a price list: =VLOOKUP(A2,Prices!$A$2:$C$50,3,FALSE).
- The name and town for a customer number: =VLOOKUP(B2,$A$2:$D$500,2,FALSE).
- Linking two lists, such as orders to a price list, with one formula that you fill down.
- The discount for an amount in a table of bands: =VLOOKUP(B2,$E$2:$F$5,2,TRUE).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =VLOOKUP("P-204",A2:C5,3,FALSE)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
VLOOKUP | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | VLOOKUP |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
lookup_value | What you're looking for, usually a cell with a code or name. | "P-204" |
table_array | The table. The lookup column must be the first column. | A2:C5 |
col_index_num | Which column of your table you want back. Count from 1. | 3 |
[range_lookup] | Optional. FALSE for an exact match, TRUE for a banded table. | FALSE |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
VLOOKUP step by step
E1 contains =VLOOKUP("P-204",A2:C5,3,FALSE). Finds the code in column A and reads the third column. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click the cell where the answer should go, here E1, and type the name of the function with a bracket.
=VLOOKUP( - Say what you're looking for: the code P-204. It goes in quotation marks, because it's text. Usually you'd just click a cell with the code here. Then type a comma.
=VLOOKUP("P-204", - Select the table, from the codes in A2 to the prices in C5. The column you search in has to be the first column of your selection. Type another comma.
=VLOOKUP("P-204",A2:C5, - Count the columns of your table from the left: Code is 1, Product is 2 and Price is 3. You want the price, so type 3 and a comma.
=VLOOKUP("P-204",A2:C5,3, - Type FALSE, because the code has to match exactly. Close the bracket and press Enter: you get 219.
=VLOOKUP("P-204",A2:C5,3,FALSE)
More examples
The name for a code
E1 contains =VLOOKUP("P-102",A2:C5,2,FALSE). With column 2 you get the name of the product instead of the price.
Looking up with a cell
F2 contains =VLOOKUP(F1,A2:C5,3,FALSE). The code is in F1. Type another code there, such as P-101, and the price updates straight away.
A table of bands with TRUE
D1 contains =VLOOKUP(75,A2:B4,2,TRUE). With TRUE, Excel looks for the largest value that isn't greater than 75, and that's 50. So put the lower limit of each band in the first column, from small to large.
Why do I get #N/A?
What you're looking for isn't in the leftmost column of your table, or it only looks the same. Check for hidden spaces with LEN, and for numbers stored as text.
Lock your table
If you fill the formula down, lock the table with dollar signs: $A$2:$C$5. Otherwise the table shifts along and the lookup goes wrong.
Looking up in another worksheet
If your table is on another sheet, put the sheet name in front with an exclamation mark: =VLOOKUP(E2,Prices!$A$2:$C$50,3,FALSE). If the name contains a space, it goes in single quotation marks: 'Prices 2026'!$A$2:$C$50.
When do you use TRUE as the last argument?
FALSE is for codes and names, where it has to match exactly. TRUE is for banded tables: Excel then takes the largest value that isn't higher than what you're looking for. That's how you find the discount for an order amount or the grade for a score. The first column must then be sorted from small to large.
A tidy message instead of #N/A
If not every code is in your table, catch the error: =IFERROR(VLOOKUP(E2,$A$2:$C$5,3,FALSE),"Unknown"). Only do that once your formula works. Otherwise you also hide the errors you actually want to see.
Where it often goes wrong
- If you forget FALSE, Excel settles for something roughly right, without warning you.
- The function can't look to the left.
- If you add a column to your table, your column number is no longer right.
Practise this in the lesson Looking up with VLOOKUP. The first exercises are free.
Frequently asked questions
Should I switch to XLOOKUP?
If everyone using your file has Excel 2021 or 365, XLOOKUP is simpler. VLOOKUP is still needed for older versions and still comes up a lot in tests.