VLOOKUP

What it does

VLOOKUP searches down the first column of a table for a value, then returns what's in another column of that row. The column is a number, counted from the left edge of the table.

Syntax

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

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,

Arguments

lookup_value

What to look for, usually a cell.

table_array

The table. The values to search must be in its first column.

col_index_num

Which column to return, counting from 1 at the left of the table.

range_lookup

FALSE for an exact match. TRUE, or leaving it out, finds the closest smaller value in a sorted list, which is meant for bands such as discount levels.

Example

Which price belongs to product code P20?

The formula in F2:

=VLOOKUP(E2,A2:C4,3,FALSE)

Result: 7

P20 is in the second row of the table. Column 3 of the table is Price, so the result is 7.

Common mistakes

• Leaving out the last argument. Excel then does an approximate match and can return a wrong row without any error. Add FALSE for exact matches.
• Counting the column from column A of the sheet instead of from the table's first column.
• Trying to return a column to the left of the search column. VLOOKUP can only look right; use XLOOKUP or INDEX with MATCH.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• VLOOKUP: find a value in a tableModule 5 · Lookups
• VLOOKUP with ranges of valuesModule 5 · Lookups
• When a lookup finds nothingModule 5 · Lookups
• #N/A: nothing foundModule 13 · Finding and fixing errors

More from this module

• IFNA
• HLOOKUP
• XLOOKUP

All functions