Write a basic VLOOKUP

=VLOOKUP("Apple", A2:C100, 2, FALSE)

Excel scans column A for “Apple,” then returns whatever sits in column 2 of that same row. The four arguments are the value to find, the table to search, the column number to return, and the match type.

Always use FALSE for an exact match

=VLOOKUP(A2, products!A:D, 4, FALSE)

Passing FALSE (or 0) demands an exact match. Using TRUE assumes your first column is sorted and returns an approximate match, which causes silent errors for most lookups.

Handle missing values with IFERROR

=IFERROR(VLOOKUP(A2, A:C, 3, FALSE), "Not found")

When the value does not exist, VLOOKUP returns #N/A. Wrapping it in IFERROR replaces that with friendly text.

Common mistakes / tips

  • Forgetting FALSE. Without it you get approximate matches and wrong results.
  • VLOOKUP only looks right. The lookup column must be to the left of the value you want returned.
  • Counting columns wrong. The column index is counted from the start of your range, not from column A of the sheet.
  • Lock your range. Use $A$2:$C$100 so the table does not shift when you copy the formula down.

Frequently asked questions

Why does VLOOKUP return #N/A?

The value was not found in the first column. Check for extra spaces, mismatched text versus numbers, or a typo, and confirm you used FALSE for an exact match.

Should I use VLOOKUP or XLOOKUP?

If you have a recent version of Excel, XLOOKUP is more flexible and can look left. VLOOKUP is still the most widely supported and the one most tutorials use.

Once you outgrow spreadsheet lookups, the same joins are far easier in code. Take the next step with our free Pandas course.