How to Use VLOOKUP in Excel (Step-by-Step for Beginners)

VLOOKUP looks something up in a list and brings back matching information. Give it a product code, and it'll return the price. Give it an employee ID, and it'll return their department. It's one of the most searched-for Excel skills, and it's easier than it looks.

Using Microsoft 365 or Excel 2021? XLOOKUP does the same job with fewer limitations. See our XLOOKUP guide. VLOOKUP is still worth knowing because it works in every version and you'll see it in other people's spreadsheets.

=VLOOKUP(what to find, where to look, which column to return, FALSE)

1. What to find: the value you're looking up, usually a cell such as E2.
2. Where to look: the table containing the data. The value you're looking for must be in the first column of this range.
3. Which column to return: a number. 1 is the first column of your range, 2 the second, and so on.
4. FALSE: means "exact match only". Always include it.

Your price list is in A2:C100: product code in column A, product name in B, price in C. You type a product code into E2 and want the price in F2.

In F2, type:

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

VLOOKUP finds the code from E2 in column A, moves across to the 3rd column of the range (C) and returns the price.

Want the product name instead? Change the 3 to a 2.

If you leave off the last argument, or use TRUE, VLOOKUP does an approximate match. It can return a result from the wrong row without any warning. That's only useful for things like tax bands, where the list is sorted and you want "the nearest value below".

For everyday lookups, always end with FALSE (or 0, which means the same thing).

If you copy the formula down, the table range moves too (A3:C101, A4:C102…) and eventually misses rows. Lock it with $ signs:

=VLOOKUP(E2, $A$2:$C$100, 3, FALSE)

Click on the range in the formula and press F4 to add them. Or select whole columns, A:C, which never needs locking.

• It can't look left. The lookup column must be the first column of the range. If the price is to the left of the code, use INDEX and MATCH or XLOOKUP.
• Inserting a column breaks it. The column number is fixed, so adding a column in the middle makes it return the wrong data.
• It only returns the first match. If a code appears twice, you'll only get the first one.

#N/A. VLOOKUP couldn't find the value. The most common causes:

• An extra space at the end of a value. Use TRIM to clean it up.
• A number stored as text in one list and as a real number in the other. Look for left-aligned numbers or green triangles.
• The value genuinely isn't in the list.

To show a friendly message instead of #N/A, wrap it in IFERROR: =IFERROR(VLOOKUP(E2, $A$2:$C$100, 3, FALSE), "Not found").

#REF! The column number is larger than the number of columns in your range. A range of A:C only has 3 columns.

It returns the wrong value. Check you ended the formula with FALSE.

Remember the four parts: what, where, which column, FALSE. Lock your table with F4, and if you need to look left or you have Microsoft 365, it's worth moving on to XLOOKUP.