How do I use VLOOKUP?

VLOOKUP finds a value in the leftmost column of a range and returns a value from another column in the same row. Use =VLOOKUP(lookup_value, table_array, col_index_num, FALSE) with FALSE for an exact match, which is what you want almost every time.

Formula (Excel and Google Sheets)

=VLOOKUP(A2, Sheet2!A:D, 3, FALSE)

Think of VLOOKUP as "look up this value in the first column of a table, then give me back a value from a column to the right of it, on the same row." A2 is the value you are searching for, Sheet2!A:D is the table you are searching in, 3 is which column (counting from the left of that table, starting at 1) you want returned, and FALSE tells it you want an exact match, not an approximate one.

The most common mistake is leaving off the FALSE. Without it, VLOOKUP assumes your table is sorted and returns the closest match instead of an exact one, which quietly returns wrong answers on unsorted data.

Step by step

1
Identify the value you are looking up
This is usually a cell reference, like a product ID or an email address, in your current sheet.
2
Identify the table you are searching
Select the full range that contains both the lookup column and the column you want returned. The lookup column must be the leftmost column of this range.
3
Count the column number
Count columns from the left of your selected range, starting at 1, to find which column holds the value you want back.
4
Add FALSE for an exact match
Add FALSE (or 0) as the fourth argument so VLOOKUP requires an exact match instead of an approximate one.

Common questions

Why does VLOOKUP return #N/A?

#N/A means it could not find an exact match. Check for extra spaces, mismatched text/number types, or a typo in the lookup value.

Can VLOOKUP look to the left?

No. VLOOKUP only returns values from columns to the right of the lookup column. Use INDEX/MATCH or XLOOKUP if you need to look left.

Related formulas

How to Use INDEX and MATCH Together in Excel and Google Sheets

How to Use XLOOKUP in Excel and Google Sheets

How to Use SUMIF and SUMIFS in Excel and Google Sheets

How to Use IFERROR to Replace a Formula Error with a Clean Result

From the blog

How to Make Sense of Your Spreadsheet Data Without Being an Analyst

Want a formula written for your exact spreadsheet, not a generic example?

Try the free formula generator