What it does
VLOOKUP connects two lists. You have a key (a product code, a customer number, an invoice ID) and want to pull a piece of information about it from another table. The function searches the leftmost column of the range you give it from top to bottom and returns the column you asked for from the first matching row.
The last argument sets the match type. FALSE (or 0) accepts only an identical value. Leaving it empty or writing TRUE makes Excel take the last value that is less than or equal to the lookup value in a sorted list. When you look up codes you almost always want FALSE; for band tables (commission rates, tax brackets, grade boundaries) TRUE is the right choice.
In Microsoft 365 and Excel 2021, XLOOKUP does the same job with fewer restrictions: the lookup column does not have to be on the left, and exact match is the default. For files shared with older versions, use VLOOKUP or INDEX with MATCH.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])=DÜŞEYARA(aranan; tablo; sütun_no; [aralık_bak])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
lookup_value | Required | The value you are looking for, or the cell that holds it (e.g. E2). It can be text, a number or a date; the search is not case-sensitive. |
table_array | Required | The range to search. The lookup value must be in the first column of this range. Lock it with dollar signs, like $A$2:$C$6, if you will copy the formula. |
col_index_num | Required | The position of the column to return, counted inside the range: the first column of the range is 1. If the range starts in column B, column D is number 3. |
range_lookup | Optional | FALSE or 0: exact match. TRUE, 1 or omitted: approximate match, which needs the first column sorted in ascending order. |
Examples
1Get a price by product code
The price list is in columns A:C. The price of the code typed in E2 should appear in F2. Price is the 3rd column of the range, so col_index_num is 3; we look up a code, so the last argument is FALSE.
=VLOOKUP(E2,$A$2:$C$6,3,FALSE)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product Code | Product | Unit Price | Code | Price | |
| 2 | KBL-1525 | Copper cable (m) | 52.00 | PRZ-0210 | 92.50 | |
| 3 | KBL-1540 | Armoured cable (m) | 71.20 | |||
| 4 | PRZ-0210 | Grounded socket | 92.50 | |||
| 5 | SGT-0016 | Circuit breaker | 132.00 | |||
| 6 | KNL-2040 | Cable duct | 68.00 |
PRZ-0210 is in the third row of the list, so the formula returns the price from that row: 92.50. Change the code in E2 and the price follows.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Show "Not in list" for codes that are missing
Order lines are in A:B, the price list is in F:G. The formula in C2 is filled down. LMB-0918 is not in the price list, so VLOOKUP returns #N/A and IFERROR turns it into a message.
=IFERROR(VLOOKUP(A2,$F$2:$G$5,2,FALSE),"Not in list")| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order Code | Qty | Unit Price | Product Code | Price | ||
| 2 | KBL-1525 | 120 | 52.00 | KBL-1525 | 52.00 | ||
| 3 | LMB-0918 | 40 | Not in list | PRZ-0210 | 92.50 | ||
| 4 | PRZ-0210 | 25 | 92.50 | SGT-0016 | 132.00 | ||
| 5 | SGT-0016 | 10 | 132.00 | KNL-2040 | 68.00 |
- C2 (filled down to C5)
=IFERROR(VLOOKUP(A2,$F$2:$G$5,2,FALSE),"Not in list")
C3 shows Not in list; the other rows return their price. Once LMB-0918 is added to the price list, C3 picks up its price automatically.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Find a commission rate with approximate match
The commission table is in E:F, with the lower bounds sorted in ascending order. With TRUE as the last argument, VLOOKUP finds the largest lower bound that is less than or equal to the sales amount and returns its rate.
=VLOOKUP(B2,$E$2:$F$5,2,TRUE)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Sales | Rate | From | Rate | |
| 2 | Emma | 8,500 | 0% | 0 | 0% | |
| 3 | Liam | 31,000 | 4% | 10,000 | 2% | |
| 4 | Olivia | 50,000 | 6% | 25,000 | 4% | |
| 5 | Noah | 24,999 | 2% | 50,000 | 6% |
- C2 (filled down to C5)
=VLOOKUP(B2,$E$2:$F$5,2,TRUE)
Liam's 31,000 falls between 25,000 and 50,000, so his rate is 4%; Olivia sold exactly 50,000 and gets 6%. Never use approximate match on an unsorted table: the result can come from the wrong row.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #N/A
Cause: The lookup value was not found in the first column of the table. Usually it really is missing; sometimes a cell has an invisible trailing space, or one list stores the code as a number and the other as text.
Fix: Check that the key exists; clean spaces with TRIM; convert numbers stored as text into numbers. To show a message instead of the error, wrap the formula in IFERROR.
- #REF!
Cause: col_index_num is larger than the number of columns in table_array, for example column 4 of the three-column range
$A$2:$C$6.Fix: Widen the range so it includes the column you want, or correct the column number.
- #VALUE!
Cause: col_index_num is less than 1 or is not a number.
Fix: Use a whole number of 1 or more.
- Wrong result
Cause: The last argument was left empty, so Excel used approximate match and returned a value from a different row of an unsorted list.
Fix: When looking up codes, always pass
FALSEor 0 as the last argument.
Good to know
- Write the table range as an absolute reference such as
$A$2:$C$6so it does not shift when you fill the formula down. F4 toggles the dollar signs on the selected reference. - Inserting a column inside the table does not update col_index_num. For tables that change often, XLOOKUP or INDEX + MATCH are safer.
- If the lookup value appears in several rows, VLOOKUP returns only the first one.
In Nexell
Get Nexell to do it from a sentence
Add the unit price from the price list to each order by product code, and write "Not in list" where the code is missing
Convert to tableBring the order list and the price list into the tool and type this. Nexell builds the match with a real Excel lookup formula and first shows a preview of how many rows matched. You do not have to write the formula or count columns.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
Can VLOOKUP pull data from another sheet?
Yes. Put the sheet name and an exclamation mark before the range: =VLOOKUP(A2,Prices!$A$2:$C$50,3,FALSE). If the sheet name contains spaces, wrap it in single quotes: 'October Prices'!$A$2:$C$50.
Related functions
Step-by-step guide: Excel automation without macros: lookups, pivots and formatting
- XLOOKUPÇAPRAZARA · Searches an array and returns the matching item; the fourth argument if not found.
- INDEXİNDİS · Returns the value at a given row and column of a range.
- MATCHKAÇINCI · Position of a value in a list (0 = exact match).
- HLOOKUPYATAYARA · Looks up in the first row and returns from the same column.
- IFERROREĞERHATA · Returns a fallback if the value is an error.