Lookup
VLOOKUP in Excel:syntax, examples and errors
VLOOKUP looks up a value in the first column of a table and returns a value from the same row. In Spanish Excel it is called BUSCARV. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
lookup_value | Required. What you are looking for, almost always a cell holding a code. Example: F2. |
table_array | Required. The table you search. The lookup value has to be in its first column. Example: $A$2:$D$6. |
col_index_num | Required. Which column of that table you return, counting from 1 (the first column of the table, not column A of the sheet). Example: 4. |
range_lookup | Optional, but always type it. FALSE (or 0) looks for the exact value. TRUE looks up bands in a sorted table. If you leave it out, Excel uses TRUE. |
How do you use VLOOKUP? A step-by-step example
VLOOKUP pulls a value from a table based on a code. In this example you have a stationery catalog in A1:D6 and you want the price of the code you type in F2.
| A | B | C | D | F | ||
|---|---|---|---|---|---|---|
| 1 | Code | Product | Category | Price | Look up | |
| 2 | P-101 | Stapler everyone borrows | Office | 4.95 | P-103 | |
| 3 | P-102 | A5 notebook | Stationery | 2.30 | ||
| 4 | P-103 | Markers (12 colors) | Stationery | 6.80 | ||
| 5 | P-104 | Office chair | Furniture | 89.00 | ||
| 6 | P-105 | Desk lamp | Furniture | 24.50 |
The formula is =VLOOKUP(F2,A2:D6,4,FALSE) and it returns 6.8, the price of the markers.
F2is what you look for: the code P-103.A2:D6is the table. It starts in column A because that is where the codes are.4is the column you want back: Price is the fourth column of the table.FALSEasks for the exact code. Without it, VLOOKUP can bring back the price of another product.
If you change the 4 to a 2, =VLOOKUP(F2,A2:D6,2,FALSE) returns Markers (12 colors). To use it on an order with 30 lines, lock the table with $ before you drag: $A$2:$D$6.
FALSE or TRUE in the last argument?
FALSE almost always. It asks for an exact match: if the code is not there, you get #N/A instead of an invented value.
TRUE is for looking up bands, like a commission table. The first column has to be sorted from smallest to largest:
| H | I | |
|---|---|---|
| 1 | From | Commission |
| 2 | 0 | 0% |
| 3 | 1000 | 2% |
| 4 | 5000 | 5% |
| 5 | 10000 | 8% |
=VLOOKUP(7200,H2:I5,2,TRUE) returns 0.05 (5%): it looks for the highest band that does not go over 7200, which is the 5000 one. With 800 in sales it returns 0, and with 12,500 it returns 0.08.
VLOOKUP or XLOOKUP?
Use XLOOKUP if your Excel has it. If you have Excel 2019 or earlier, or the test runs on a machine whose version you do not know, use VLOOKUP.
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Versions | All | Microsoft 365, Excel 2021 and later, Excel for the web |
| Look to the left | No | Yes |
| If it does not find the value | #N/A (you need IFERROR) | It has an argument for the message |
| Default match | Approximate, if you leave out the last argument | Exact |
| How you pick what to return | A column number: if you insert a column in the table, the number no longer points to the same one | A range: it adjusts on its own |
The same lookup with XLOOKUP is =XLOOKUP(F2,A2:A6,D2:D6) and it returns 6.8. To look to the left in an Excel without XLOOKUP, use INDEX and MATCH: =INDEX(D2:D6,MATCH("P-104",A2:A6,0)) returns 89.
How does VLOOKUP show up in an Excel test?
In a job interview you get an order with codes in column A and the catalog on another tab: "Bring in the price of each line." The answer they expect is =VLOOKUP(A2,Catalog!$A$2:$D$200,4,FALSE), dragged to the last line. What they look for: that you type FALSE and that you lock the table with $.
In a skills test the question is often theoretical:
What does
=VLOOKUP("P-999",A2:D6,4,FALSE)return if P-999 is not in A2:A6? a) 0 · b) #N/A · c) #REF! · d) An empty cell
Answer: b. With FALSE, if the value does not exist, VLOOKUP gives #N/A.
Practice VLOOKUP
The English exercises are on the way. Until then, copy the catalog into a blank sheet, look up a few codes, and then try to break the formula on purpose with the mistakes listed above. Each one produces the error it names. For counting or adding the rows that match a condition, see COUNTIF and SUMIF.
Available in: all versions of Excel, on Windows, Mac and Excel for the web. Source: Microsoft: VLOOKUP function. Results calculated with Formulo's engine.
Common mistakes
- #N/A: the value is not in the table
- If F4 holds P-999 and that code does not exist, =VLOOKUP(F4,A2:D6,4,FALSE) gives #N/A. Check the code or show a message with IFERROR: =IFERROR(VLOOKUP(F4,A2:D6,4,FALSE),"Not found") returns Not found.
- #N/A with a code you can see in the table
- Almost always a trailing space ("P-103 ") or a number stored as text. To Excel, "P-103 " and "P-103" are different values. Remove the space with TRIM: =VLOOKUP(TRIM(F3),A2:D6,4,FALSE) returns 6.8 again. If it is a number stored as text, convert it with Data > Text to Columns.
- #N/A because the table starts in another column
- =VLOOKUP(F2,B2:D6,3,FALSE) gives #N/A: the table starts in B, so VLOOKUP searches for the code among the product names. The range has to start in the column of the lookup value.
- #REF!: you ask for a column that does not exist
- The table A2:D6 has 4 columns. =VLOOKUP(F2,A2:D6,5,FALSE) asks for the fifth and gives #REF!. Widen the range or lower the number.
- #VALUE!: the column number is 0
- col_index_num starts at 1. =VLOOKUP(F2,A2:D6,0,FALSE) gives #VALUE!.
- It returns data from another row without an error
- FALSE is missing at the end. Without it, VLOOKUP does an approximate lookup and, if the table is not sorted, returns the wrong value without warning. It is the most dangerous mistake because you cannot see it.
- It works in the first row, but not when you drag it
- When you copy the formula down, A2:D6 becomes A3:D7 and the table moves. Lock it with $: $A$2:$D$6.
Frequently asked questions
What is VLOOKUP called in Spanish?
BUSCARV. The arguments are the same, but a Spanish Excel separates them with a semicolon: =BUSCARV(F2;A2:D6;4;FALSO).
Can VLOOKUP look to the left?
No. It only returns columns to the right of the first one. To look to the left, use XLOOKUP or INDEX with MATCH.
Is FALSE the same as 0?
Yes. =VLOOKUP(F2,A2:D6,4,0) and =VLOOKUP(F2,A2:D6,4,FALSE) return the same: 6.8 in the example on this page.
Is VLOOKUP case-sensitive?
No. =VLOOKUP("p-103",A2:D6,4,FALSE) finds P-103 and returns 6.8.
What does VLOOKUP return if the code is repeated?
The value from the first row where it appears. If you need the last one, use XLOOKUP with search mode -1.
How do I use VLOOKUP with a table on another sheet?
Write the sheet name before the range: =VLOOKUP(A2,Catalog!$A$2:$D$200,4,FALSE). If the sheet name has spaces, it goes in single quotes: `'Price list'!$A$2:$D$200`.
How do I remove the #N/A from VLOOKUP?
Wrap the formula in IFERROR and say what to show: =IFERROR(VLOOKUP(F4,A2:D6,4,FALSE),"Not found"). First check that the #N/A is not an extra space.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.