FormulaGym
XP 0
Streak 0

The VLOOKUP function in Excel

VLOOKUP finds a value in the first column of a table and returns something from the same row.

What VLOOKUP does

You give it the value to find, the table to search and the number of the column to return. It always searches the first column of the table, so the thing you look up has to sit on the left. Use FALSE as the last argument to ask for an exact match.

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value
What to find, such as an ID in cell E2.
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 at the left.
range_lookup
Use FALSE for an exact match. Leaving it out can return wrong answers.

Worked example

ABCDEF
1IDProductPriceLook up IDPrice
21001Notebook8100740
31004Desk lamp25
41007Backpack40
51010Headphones60
F2 =VLOOKUP(E2,A2:C5,3,FALSE)

ID 1007 is found in the first column. Price is the third column of the table, so VLOOKUP returns 40.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Sales & Ops

Pull a price or a customer name from a list using an ID.

Finance

Match account codes to account names.

HR

Look up an employee's department from their ID.

Common mistakes

  • Leaving out FALSE. Without it Excel looks for the nearest match, which gives wrong answers on unsorted data.
  • The lookup value is not in the first column of the table. VLOOKUP cannot look to the left.
  • Inserting a column into the table, which can change which column number you needed.
  • Spaces or numbers stored as text that stop two values matching, which gives #N/A.

The VLOOKUP course

  1. 1
    Look up a price by IDFree practice below
    Practise
  2. 2
    Approximate match for tax bandsComing soon
  3. 3
    Look up across sheetsComing soon
  4. 4
    Fix #N/A errorsComing soon
  5. 5
    Return several columnsComing soon
  6. 6
    Common VLOOKUP trapsComing soon

Common questions

Why does VLOOKUP return #N/A?

The value was not found. Common causes are a typo, a trailing space, a number stored as text, or the lookup value not being in the first column.

Should I use XLOOKUP instead?

If your version of Excel has it, yes, because it is more flexible. VLOOKUP is still everywhere in older workbooks, so it is worth knowing.

Related functions