How to Use VLOOKUP in Excel, Step by Step

VLOOKUP is one of the most used functions in Excel, and one of the most asked about in job tests. The "V" stands for vertical: it searches down the first column of a table, finds the row you want, and returns a value from another column in that row.

We'll use this staff table in A1:E9. Notice the column numbers in the header row. VLOOKUP counts columns from the left edge of the table, starting at 1.

#The four VLOOKUP arguments

#Your first VLOOKUP

Who is employee E107? Search the IDs in the first column of A2:E9, and return the 2nd column, Name:

To get that person's salary instead, change only the column number. Salary is the 4th column of the table:

That's really all there is to it: find this, in this table, return column number N, exact match.

Your turn. This one runs in a real spreadsheet, so you can click cells and drag ranges into the formula.

#Always use FALSE (unless you mean it)

This is the mistake that catches almost everyone. If you leave out the last argument, VLOOKUP doesn't do an exact match. It does an approximate match, which assumes the first column is sorted from smallest to largest and returns the closest value that's not bigger than the one you asked for.

On unsorted data like an ID list or a list of names, that gives you wrong answers without any error to warn you. So unless you specifically want an approximate match, end every VLOOKUP with FALSE (or 0, which means the same thing).

#When TRUE is the right choice

Approximate match is exactly what you want for bands and tiers, like commission rates. Say this table is in A2:B5 and sorted from smallest to largest:

$31,500 isn't in the table, so VLOOKUP drops back to the largest value that's not bigger, $25,000, and returns its rate. This only works if the first column is sorted in ascending order.

#Copying VLOOKUP down a column

When you copy a VLOOKUP down to look up a whole list of IDs, the table reference moves with it unless you lock it. Add dollar signs to keep it fixed (press F4 after selecting the range to add them quickly):

Now G2 changes to G3, G4 and so on as you copy, but the table stays A2:E9.

#How to fix #N/A in VLOOKUP

#N/A means VLOOKUP couldn't find the value. Work through this list:

1. Is the value really there? Check the spelling, including extra spaces at the end. TRIM removes them.
2. Is it in the first column of table_array? VLOOKUP only searches the leftmost column of the range you give it.
3. Numbers vs text. 1001 typed as a number won't match "1001" stored as text. Look for the small green triangle in the cell.
4. Did the table move when you copied? Lock it with $A$2:$E$9.
5. Is the value genuinely missing? Then show a message instead of the error:

Two other errors you might meet: #REF! means col_index_num is bigger than the number of columns in the table, and #VALUE! means it's less than 1.

#What VLOOKUP can't do

• It can't look left. The column you search must be the first column of the table. To find an ID from a name, you'd have to move columns around.
• Inserting a column breaks it. The column number is fixed, so 3 points at the wrong column after you insert one inside the table.
• It only returns the first match. If a value appears twice, you get the top one.

INDEX with MATCH, and XLOOKUP, solve all three. If your Excel has XLOOKUP, it's the easier choice for new work. Read our XLOOKUP guide to see the difference.

#VLOOKUP vs HLOOKUP

HLOOKUP is the same idea turned sideways. It searches across the first row of a table and returns a value from a row number you choose. It's rarely needed, since most data is arranged in columns.

#Key takeaways

• VLOOKUP searches the first column of a table and returns column number N from the same row.
• End it with FALSE for an exact match. Leaving it out gives an approximate match.
• Lock the table with dollar signs before copying the formula down.
• For new workbooks in Excel 365 or 2021 and later, XLOOKUP is simpler and safer.