Excel comparison
XLOOKUP vs VLOOKUP: which lookup formula should you use?
VLOOKUP is still common in older spreadsheets, but XLOOKUP is easier to read, more flexible, and safer for many modern lookup tasks.
The main difference
VLOOKUP searches the first column of a table and returns a value from a numbered column to the right.
XLOOKUP separates the lookup array from the return array, so it can look left, right, vertically, or horizontally without counting columns.
Why this comparison matters
Lookup formulas are often used in pricing sheets, employee lists, inventory tables, and financial models. A fragile lookup can quietly return the wrong answer.
Choosing the clearer function reduces errors when columns move or the workbook grows.
Practical examples
VLOOKUP price lookup
=VLOOKUP(A2,$F:$H,3,FALSE)Looks for A2 in the first column of F:H and returns the third column.
XLOOKUP price lookup
=XLOOKUP(A2,F:F,H:H,"Not found")Looks for A2 in F:F and returns the matching value from H:H.
Recommended Excel functions
Common mistakes
- 01Using approximate match in VLOOKUP by accident.
- 02Counting the wrong return column after inserting or deleting columns.
- 03Wrapping lookup errors without checking whether the lookup key is actually valid.
Practice
Practice this skill with interactive exercises on Calcu.
Use Calcu to practice lookup formulas in realistic spreadsheet exercises with instant feedback.
FAQ
Is XLOOKUP always better than VLOOKUP?
For new workbooks, XLOOKUP is usually clearer and more flexible. VLOOKUP is still useful when maintaining older files.
Can XLOOKUP replace INDEX MATCH?
Often yes for simple lookups, but INDEX MATCH is still useful in older Excel versions and some advanced models.