Cellwise Try it free

Lookup function

Excel VLOOKUP function

VLOOKUP looks down the first column of a table for a value, then returns something from the same row in a column you choose. Use it to pull a price, name, or status from a product list or ID table.

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

Arguments

lookup_value
The value to find, like a product ID.
table_array
The table to search. The lookup value must be in its first column.
col_index_num
Which column of the table to return, counting from 1.
range_lookup
FALSE for an exact match. Leave it out and Excel uses an approximate match.

VLOOKUP examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1IDItemPrice
2P-101Desk lamp34.99
3P-102Monitor arm89
4P-103USB hub24.5
5P-104Webcam59.95
  1. =VLOOKUP("P-103",A2:C5,2,FALSE)

    Result: USB hub

    Finds P-103 and returns the item name from column 2.

  2. =VLOOKUP("P-102",A2:C5,3,FALSE)

    Result: 89

    Same lookup, but column 3 returns the price.

  3. =VLOOKUP("P-999",A2:C5,2,FALSE)

    Result: #N/A

    An ID that is not in the table gives #N/A.

  4. =IFERROR(VLOOKUP("P-999",A2:C5,2,FALSE),"Not found")

    Result: Not found

    Wrap it in IFERROR to show a friendly message instead.

How to use VLOOKUP

  1. Click the cell where the answer should go.
  2. Type =VLOOKUP( and the value to find.
  3. Select the table, starting with the column that holds that value.
  4. Type the column number to return, then FALSE for an exact match.

Common VLOOKUP mistakes

Leaving out FALSE

Without it Excel does an approximate match and can return the wrong row. Add FALSE as the last argument.

The lookup column is not first

VLOOKUP only searches the leftmost column. Rearrange the table or use XLOOKUP.

Column number breaks when you insert a column

A hard-coded 3 does not move. XLOOKUP or INDEX/MATCH point at the column itself.

VLOOKUP FAQ

Why does VLOOKUP return #N/A?

The value was not found. Check for extra spaces, numbers stored as text, or a missing FALSE argument.

Can VLOOKUP look to the left?

No. It always returns from a column to the right of the lookup column. Use XLOOKUP or INDEX with MATCH to look left.

Should I use VLOOKUP or XLOOKUP?

Use XLOOKUP if your Excel has it. It looks in any direction, defaults to an exact match, and has a built-in "not found" value.

Practice VLOOKUP in these lessons

Guides that use VLOOKUP

Related functions

Updated