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

Need it for your exact data?

Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.

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: