Can VLOOKUP return a value from a column to the left?

No. It always searches the first column of the table and returns from columns to the right. Use INDEX with MATCH, or XLOOKUP, to look left.

What does the last argument of VLOOKUP do?

FALSE (or 0) means exact match. TRUE (or omitted) means approximate match on sorted data, which suits tiers and bands but not IDs.

Why does VLOOKUP return #N/A when the value is visible?

Usually a hidden difference: a trailing space, a number stored as text, or a different spelling. Compare with LEN on both cells, then clean the data.

Is VLOOKUP case sensitive?

No. "abc" and "ABC" are treated as the same key.

← All functions

Lookup & reference

VLOOKUP

Looks in the first column and returns a value from a column to the right.

Syntax

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

Example: =VLOOKUP(A2,PriceList!$A$2:$C$100,3,0)

Available in: All versions

VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row. Use =VLOOKUP(lookup_value, table, column_number, FALSE) for an exact match, for example to pull a price from a product list by SKU.

When to use it

You have a lookup key (SKU, employee ID, email) in the leftmost column of a table and need one of the columns to its right.
You want to grab a value from a reference list, such as a price, a department or a tax rate, based on a code typed in another sheet.
You need tiered values such as commission rates or shipping bands, where a number falls into a range (approximate match).
Your file must open in older versions of Excel. If everyone has Microsoft 365, XLOOKUP is easier and safer.

Arguments explained

lookup_value. The value to find, such as an ID or a name. It is searched for in the first column of table_array only, and a cell reference is the usual choice.
table_array. The range that holds the data. The column you search must be its leftmost column, and the column you want back must lie inside it. Lock it with $ signs when you copy the formula down.
col_index_num. The position of the result column counted from the left of table_array, starting at 1. Inserting a column inside the table does not update this number, which is a classic source of wrong results.
range_lookup. FALSE (or 0) demands an exact match, which is what you want in almost every case. TRUE (or omitted) finds the closest smaller value and needs the first column sorted ascending.

Worked examples

Exact match: price by SKU

	A	B	C	E	F
1	SKU	Product	Price	Look up	Price
2	A101	Desk lamp	24.99	A103
3	A102	Monitor stand	39
4	A103	USB hub	18.5
5	A104	Webcam	54.9

Formula: F2: =VLOOKUP(E2,A2:C5,3,FALSE)

Result: 18.5

Finds A103 in column A and returns the 3rd column of A2:C5. FALSE demands an exact match.

Approximate match: commission tier

	A	B	D	E
1	Sales from	Rate	Sales	Rate
2	0	0.02	27500
3	10000	0.04
4	50000	0.06

Formula: E2: =VLOOKUP(D2,A2:B4,2,TRUE)

Result: 0.04

27,500 is not in the list, so TRUE returns the last row whose start is not above it (10,000). The first column must be sorted ascending.

Wildcard: partial product name

	A	B	C
1	SKU	Product	Price
2	A101	Desk lamp	24.99
3	A102	Monitor stand	39
4	A103	USB hub	18.5

Formula: E2: =VLOOKUP("Mon*",B2:C4,2,FALSE)

Result: 39

The asterisk matches any text, so "Mon*" finds "Monitor stand". The table starts in column B because the key must be the first column.

Try it

A tiny spreadsheet with example data. Edit the formula or any number and see the result.

Formula in F2: =VLOOKUP(103,A2:D6,3,FALSE)

Example spreadsheet for VLOOKUP
	A	B	C	D	E	F
1	ID	Item	Price	Stock		Result
2	101	Pen	2.5	120		25
3	102	Notebook	4	80
4	103	Lamp	25	15
5	104	Mug	7.5	40
6	105	Backpack	39	12

Result in F2: 25

Practice this in a lesson: The VLOOKUP Function

Common errors and how to fix them

#N/A. The key is not in the first column, or it differs by a trailing space or text-versus-number format. Clean it with TRIM, or wrap the formula in IFERROR to show a friendly message.
Wrong result with TRUE. The last argument is optional and defaults to TRUE (approximate). On unsorted data it returns a wrong row without any error. Always type FALSE for IDs and names.
#REF!. The column number is larger than the number of columns in the table. Count columns from the first column of the table, not from column A of the sheet.
Formula breaks when copied down. The table reference moves with the formula. Lock it with $, for example $A$2:$C$5, or use a named range or an Excel Table.

Related functions

Frequently asked questions

Can VLOOKUP return a value from a column to the left?: No. It always searches the first column of the table and returns from columns to the right. Use INDEX with MATCH, or XLOOKUP, to look left.
What does the last argument of VLOOKUP do?: FALSE (or 0) means exact match. TRUE (or omitted) means approximate match on sorted data, which suits tiers and bands but not IDs.
Why does VLOOKUP return #N/A when the value is visible?: Usually a hidden difference: a trailing space, a number stored as text, or a different spelling. Compare with LEN on both cells, then clean the data.
Is VLOOKUP case sensitive?: No. "abc" and "ABC" are treated as the same key.