Skip to content

Excel function · Lookup & reference

Excel VLOOKUP function

VLOOKUP finds a value in the first column of a table and returns a value from another column in the same row: a price from a product code, a name from an employee ID.

In Turkish Excel: DÜŞEYARA

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

English Excel=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Turkish Excel=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

ArgumentRequiredDescription
lookup_valueRequiredThe 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_arrayRequiredThe 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_numRequiredThe 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_lookupOptionalFALSE 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.

F2fx=VLOOKUP(E2,$A$2:$C$6,3,FALSE)
Get a price by product code: example table
ABCDEF
1Product CodeProductUnit PriceCodePrice
2KBL-1525Copper cable (m)52.00PRZ-021092.50
3KBL-1540Armoured cable (m)71.20
4PRZ-0210Grounded socket92.50
5SGT-0016Circuit breaker132.00
6KNL-2040Cable duct68.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.

C2fx=IFERROR(VLOOKUP(A2,$F$2:$G$5,2,FALSE),"Not in list")
Show "Not in list" for codes that are missing: example table
ABCDEFG
1Order CodeQtyUnit PriceProduct CodePrice
2KBL-152512052.00KBL-152552.00
3LMB-091840Not in listPRZ-021092.50
4PRZ-02102592.50SGT-0016132.00
5SGT-001610132.00KNL-204068.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.

C2fx=VLOOKUP(B2,$E$2:$F$5,2,TRUE)
Find a commission rate with approximate match: example table
ABCDEF
1RepSalesRateFromRate
2Emma8,5000%00%
3Liam31,0004%10,0002%
4Olivia50,0006%25,0004%
5Noah24,9992%50,0006%
  • 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 FALSE or 0 as the last argument.

Good to know

  • Write the table range as an absolute reference such as $A$2:$C$6 so 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

Type this in Nexell

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 table

Bring 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.

Can VLOOKUP return a column to the left of the lookup column?

No; VLOOKUP only returns columns to the right of the lookup column. For a value on the left, use XLOOKUP or INDEX with MATCH.

Related functions

Step-by-step guide: Excel automation without macros: lookups, pivots and formatting

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free