VLOOKUP

How to use VLOOKUP in Excel (with examples)

VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row. In Russian-language Excel it is called ВПР.

Syntax

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

lookup_value — The value to find (a code, name or ID).
table_array — The table to search. The lookup value must be in its first column.
col_index_num — The position of the column within the table to return the result from (starting at 1).
range_lookup — FALSE (0) — exact match, TRUE (1) — approximate match.

Example

	A	B	C
1	Code	Product	Price
2	P01	Notebook	8000
3	P02	Pen	3000
4	P03	Pencil	2000

Formula:

=VLOOKUP("P03",A2:C4,3,FALSE)

→ result: 2000

Exact match (FALSE)

Always use FALSE when looking up by code, ID or name. Otherwise VLOOKUP may return the wrong row.

Approximate match (TRUE)

Use it for lookups by ranges: score → grade, amount → discount percentage. The first column must be sorted in ascending order.

Locking the range with $

To keep the table from shifting when you drag the formula down, make it an absolute reference: $A$2:$C$100 (press F4).

VLOOKUP limitations

VLOOKUP only searches to the right, and the column number breaks when a column is inserted. INDEX+MATCH or XLOOKUP solve these problems.

Put it into practice

Do interactive exercises on this topic in the Excel simulator.

Start exercise

Frequently asked questions

VLOOKUP returns a #N/A error. What should I do?

The value wasn't found. Check for extra spaces (TRIM) and make sure number and text formats match. To show a friendlier message: =IFERROR(VLOOKUP(...),"Not found").

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

No. Use INDEX+MATCH or XLOOKUP for that.

Are ВПР and VLOOKUP the same function?

Yes, ВПР is the name of VLOOKUP in Russian-language Excel. The syntax is the same, except arguments are separated by ; instead of ,.