VLOOKUP vs XLOOKUP cheat sheet
The two most-searched lookup functions, aligned on one page: syntax, differences, and the errors everyone hits. Then write each one in an interactive exercise.
Syntax side by side
VLOOKUP (all versions)
=VLOOKUP(key, table, col, FALSE) key: the value to find in the first column · table: the range holding keys and results · col: the column index inside the table · FALSE: exact match
XLOOKUP (Excel 2021+/365)
=XLOOKUP(key, lookup_array, return_array) key: the value to find · lookup_array: the column holding keys · return_array: the column holding results (left or right)
Behavior differences
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Lookup direction | Left to right only | Any direction (including left) |
| Result column | Column index (a number) | Return range passed directly |
| Default match | Approximate (exact needs FALSE) | Exact |
| Column inserted into table | Index can shift — results go stale | Unaffected |
| No match found | Returns #N/A | if_not_found argument returns a fallback inline |
| Availability | All versions | Excel 2021+/365, Google Sheets |
Common errors and fixes
| Symptom | Cause | Fix |
|---|---|---|
#N/A | Key truly absent, or the table drifted because ranges were not locked | Verify the data exists; lock the table with $ before filling; add IFNA/IFERROR fallback |
Wrong row returned | FALSE omitted — formula fell into approximate match | Always end VLOOKUP with FALSE (XLOOKUP is exact by default) |
#REF! | Column index exceeds the table's width | Count the index from the table's first column, not the worksheet column |
Whole column wrong after filling down | Relative references let the table drift as you fill | Lock the table as $A$2:$C$6; leave only the key relative |
Write one now
Both functions have an interactive exercise in the practice hall: locked mini sheet, instant grading.
FAQ
Should I use VLOOKUP or XLOOKUP?
Use XLOOKUP when your Excel version supports it and you need to look left: no column index, exact match by default, safe against inserted columns. Use VLOOKUP for older versions or shared files — and always end it with FALSE.
VLOOKUP returns #N/A — how do I fix it?
Wrap it: =IFNA(VLOOKUP(…), "Not found") catches only missing matches; IFERROR hides every error. Returning a meaningful fallback beats leaving #N/A in a report.
Does XLOOKUP replace INDEX+MATCH?
Functionally yes: =XLOOKUP(E2, C2:C6, A2:A6) equals =INDEX(A2:A6, MATCH(E2, C2:C6, 0)). Interviews still ask for INDEX+MATCH, so learn both forms.