Lessons Lesson 36

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

By Ali, Founder, Excel Lessons

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.

Read more about this

Where this comes up again

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 and returns something from that row.

In E2, type:

=VLOOKUP(F2,A1:D6,3,FALSE)

F2 is the code, A1:D6 the table, 3 the Unit price column, FALSE an exact match.

E2
Row A B C D E F G
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. 1

    VLOOKUP finds a value in the first column of a table and returns something from that row. In E2, type: =VLOOKUP(F2,A1:D6,3,FALSE). F2 is the code, A1:D6 the table, 3 the Unit price column, FALSE an exact match.

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

  2. 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. 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. 4

    The lookup value does not have to be a cell. Text in quotation marks works too. In E5, get the unit price for PR-560. Shape: =VLOOKUP("text",A1:D6,3,FALSE).

    Hint. "PR-560" in quotation marks.

  5. 5

    A lookup is just a value, so it can go into a calculation. In E7, find 3 times the price of the item in F2. Shape: =VLOOKUP(lookup,table,col,FALSE)*n.

    Hint. The step 1 lookup, times 3.

  6. 6

    TRUE instead of FALSE finds the nearest lower match, not an exact one. Read =VLOOKUP("PR-400",A1:D6,3,TRUE) and say what it returns. There is no PR-400 on the list.

    Hint. Which code comes just before PR-400?