Excel How-To

How to Use VLOOKUP in Excel

VLOOKUP searches the first column of a range for your key and returns a value from a column to the right. Always end with FALSE for an exact match.

Step by step

1
Start the formula
In the result cell type =VLOOKUP( and select the value you want to look up.
2
Pick the table range
Select the table where the key is in the first column. Press F4 to lock it with $.
3
Set the column number
Count from the table's first column to the column you want to return, then enter that number.
4
Force an exact match
Add , FALSE) to finish. Press Enter.

=VLOOKUP(A2, Sheet2!$A$2:$D$100, 4, FALSE)

Approximate match needs the first column sorted in ascending order — otherwise the result can be wrong without any error.
Use a whole-column range such as $A:$D so that new rows are included automatically.

Can VLOOKUP look to the left?

No — use XLOOKUP or INDEX/MATCH when the return column is left of the key.

What does the FALSE at the end of VLOOKUP mean?

It forces an exact match. With TRUE or nothing, VLOOKUP returns the closest smaller value, which is only right for sorted ranges such as tax bands.

How do I return a blank instead of #N/A?

Wrap the formula in IFNA: =IFNA(VLOOKUP(A2, $D:$F, 3, FALSE), "").

Can VLOOKUP return values from several columns?

In Excel 365 pass an array of column numbers: =VLOOKUP(A2, $D:$G, {2,3,4}, FALSE) spills three values across.

Functions

Terms

Errors