VLOOKUP Function in Excel: Syntax, Examples and Mistakes

IntermediateLookup

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Arguments of VLOOKUP
Argument	What it means
`lookup_value`	What to find, such as a code in a cell.
`table_array`	The table. The lookup column must be its first column.
`col_index_num`	Which column of the table to return: 1 is the first, 2 the second.
`range_lookup`	FALSE for an exact match (what you almost always want). TRUE or left out finds the closest smaller value in a sorted list.

Examples with results

Every result below is calculated by the practice engine on the sample data shown, not typed in by hand.

Price list (fictional)

Row	A	B	C
1	Code	Product	Price
2	P-101	Desk lamp	24.9
3	P-102	Notebook	3.5
4	P-103	Mouse	15
5	P-104	Keyboard	32
6	P-105	Monitor stand	21

Price list (fictional) Row 1 holds the headings. All data is fictional. Dates are year-month-day.

Download CSV

=VLOOKUP("P-103",A2:C6,3,FALSE)15
Price of product P-103
=VLOOKUP("P-105",A2:C6,2,FALSE)Monitor stand
Product name for code P-105
=VLOOKUP("P-999",A2:C6,3,FALSE)#N/A "P-999" was not found in the first column.
A missing code gives #N/A

Try it yourself

Change the formula or switch to other sample data.

Sample data

Formula

Press Calculate to see the result.

The practice box runs in your browser with BDK’s own calculator, which covers about 60 common functions. Cell references work as in Excel (B2, B2:B9). Your formulas are not saved or sent anywhere. For anything unusual, check the result in Excel itself.

How people use VLOOKUP at work

Sales
Fill in a price from the price list.
=VLOOKUP("P-102",A2:C6,3,FALSE)3.5
Data: Price list (fictional)
Finance and accounting
Commission rate for a sales total, using a sorted table.
=VLOOKUP(12500,A2:B5,2,TRUE)0.06
Data: Commission tiers (fictional)

Common mistakes and tips

Always use FALSE for exact matches. Leaving it out can return wrong answers without warning.
VLOOKUP cannot look to the left of the lookup column. Use INDEX with MATCH or XLOOKUP for that.
Extra spaces in the data cause #N/A. TRIM the data first.
Fix the table with $ signs when you copy the formula down: $A$2:$C$6.

Works in

All versions of Excel, and Google Sheets.

Related formulas

XLOOKUPINDEXMATCHIFERROR

Lessons that use it

Tools that help

Need Something Different? Let’s Talk.

Looking for different features or something built around your workflow? Share your idea with us.

Let’s Talk All formulas

VLOOKUP function in Excel