VLOOKUP

About this lesson

VLOOKUP searches the first column of a table for a value. Then it returns a value from the same row, in a column you choose.

By the end you can

look up a value with VLOOKUP and return any column from its row.
search for a code read from a cell or typed into the formula.
say what FALSE does, and what leaving it off costs.

Practises VLOOKUP XLOOKUP HLOOKUP INDEX MATCH

The idea

It takes four arguments: what to find, where to look, which column to return, and whether the match must be exact. People often leave off the fourth one, and it costs them. Without it, VLOOKUP assumes the first column is sorted. It returns the closest value below your search value. On an unsorted list it does not fail. It gives a wrong answer with no warning.

The third argument needs care too. It counts columns from the left edge of the table you gave it, not from column A. Give it B1:D6 and column 1 is B, not A.

The mistake to watch for

Leaving off the fourth argument. Without FALSE, VLOOKUP assumes the first column is sorted and returns the nearest smaller value. For codes, names and IDs, that is a wrong answer that looks right. Write FALSE every time, unless the table is a band table on purpose.

Where this comes up again

XLOOKUP: Looking Left, Not Found and Next Larger Advanced Lookups
Two-Way Lookup: INDEX with Two MATCHes Advanced Lookups
Choosing a Lookup: Putting It Together Advanced Lookups
Approximate Match: Bands and Brackets Advanced Lookups

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

VLOOKUP finds a value in the first column of a table. Then it returns something from the same row. It takes four arguments: what to find, the table, which column to return, and FALSE for an exact match.

In `E2`, type `=VLOOKUP(F2,A1:D6,3,FALSE)`.

F2 holds a code. A1:D6 is the table. 3 is the third column (Unit price). FALSE means the code must match exactly.

Row	A	B	C	D	F
1	Code	Item	Unit price	Lead days
2	PR-100	Business cards	34.5	3	PR-340
3	PR-220	A5 flyers	62	5	PR-455
4	PR-340	Roll banner	128.75	7	PR-100
5	PR-455	Vinyl sticker	18.2	2
6	PR-560	Foam board	41.9	4
7
8

Every step

1

VLOOKUP finds a value in the first column of a table. Then it returns something from the same row. It takes four arguments: what to find, the table, which column to return, and FALSE for an exact match. In E2, type =VLOOKUP(F2,A1:D6,3,FALSE). F2 holds a code. A1:D6 is the table. 3 is the third column (Unit price). FALSE means the code must match exactly.

Hint. F2, A1:D6, 3, FALSE.
2

Only the third argument decides which column comes back. Read =VLOOKUP(F3,A1:D6,4,FALSE) and say what it returns. F3 holds PR-455, and the fourth column is Lead days.

Hint. PR-455's row, fourth column.
3

In E4, return the item name for the code in F4. It is the same lookup, with the second column of the table.

Hint. Column 2 this time.
4

The first argument does not have to be a cell. Text typed into the formula in quotation marks works too. In E5, get the unit price of PR-560 by typing the code straight into the formula.

Hint. "PR-560" in quotation marks.
5

A lookup is just a value, so it can go into a calculation. In E7, write a formula with VLOOKUP that works out what 3 of the item in F2 would cost. That is its unit price times 3.

Hint. The step 1 lookup, times 3.
6

The fourth argument is the one people leave off. Here is what it costs. With TRUE instead of FALSE, VLOOKUP does not need an exact match. It takes the nearest code below the one you asked for. There is no PR-400 on the list. Read =VLOOKUP("PR-400",A1:D6,3,TRUE) and say what it returns.

Hint. Which code comes just before PR-400?