FormulaCraft

XLOOKUP vs VLOOKUP: which to use in Excel and Google Sheets

Topic:VLOOKUP
Excel & Google Sheets
=XLOOKUP(D2, A:A, B:B, "Not found")

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Runs server-side · free · no signup

Step by step

  1. 1XLOOKUP(D2, A:A, B:B) searches column A and returns from column B — in any direction.
  2. 2The 4th argument, "Not found", replaces #N/A automatically.
  3. 3VLOOKUP can only look right and needs a column-index number, which breaks when columns move.

Tips

Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.

Frequently asked

Should I switch from VLOOKUP to XLOOKUP?

If your version supports XLOOKUP, yes — it’s more flexible and avoids VLOOKUP’s common pitfalls. Keep VLOOKUP only for compatibility with older Excel files.

Why does XLOOKUP show #NAME? in my Excel?

XLOOKUP isn’t available in your version. Upgrade to Microsoft 365 / Excel 2021, or use INDEX/MATCH instead.

More on VLOOKUP

See all →
Reference

VLOOKUP

Look up a value in the first column of a range and return a value from another column in the same row.

How-to

How to VLOOKUP from another sheet

Pull a value from a different tab with VLOOKUP by prefixing the range with the sheet name. Works in Excel and Google Sheets.

Error fix

Fix #N/A

Fix #N/A errors in VLOOKUP in Excel and Google Sheets — covers missing matches, extra spaces, data-type mismatches, and range mistakes.

How-to

How to VLOOKUP with multiple criteria

Look up a value that matches two or more conditions using INDEX/MATCH with joined keys. Works in Excel 365 and Google Sheets.

Error fix

Fix Wrong value

VLOOKUP returns an incorrect value in Excel and Google Sheets — fix wrong column index, approximate match mode, unsorted data, and duplicate lookup keys.

How-to

How to fix VLOOKUP returning #N/A

VLOOKUP shows #N/A when it can’t find a match. Here are the real causes — exact-match, spaces, text-vs-number — and how to fix each.

Formulas used

XLOOKUPVLOOKUPINDEX / MATCH

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: