VLOOKUP vs XLOOKUP in Google Sheets — The Full Comparison (2026)
Home› VLOOKUP vs XLOOKUP in Google Sheets
Google Sheets Comparison

VLOOKUP vs XLOOKUP in Google Sheets

Google Sheets added XLOOKUP in 2023, bringing it to feature parity with Excel 2019+. That means every Google Sheets user now has both — and it's worth understanding when each one wins. XLOOKUP is cleaner, more flexible, and handles left lookups natively. VLOOKUP is still installed on every Sheet worldwide and works fine for most jobs. This guide draws the line.

⚡ Quick Answer

For new formulas, use XLOOKUP — cleaner syntax, left lookups, built-in not-found handling. For existing VLOOKUP formulas that work, no need to migrate. VLOOKUP still ships with every Google Sheet and won't be deprecated. Both functions coexist and get ongoing support.

The syntax difference

VLOOKUP

=VLOOKUP(search_key, range, index, [is_sorted])

Four arguments. You give it what to look up, the table to look in, which column number to return, and whether the data is sorted (usually FALSE for exact match).

XLOOKUP

=XLOOKUP(search_key, lookup_range, return_range,
         [if_not_found], [match_mode], [search_mode])

Three required arguments (search key, lookup range, return range) plus three optional ones. Instead of a column index, you point directly at the range to return. Instead of wrapping with IFERROR, you pass a not-found message as the 4th argument.

What XLOOKUP does that VLOOKUP can't

1. Look up in any direction

VLOOKUP only searches the leftmost column and can only return values to the right. XLOOKUP searches any column and returns from any column — left, right, or the same column.

VLOOKUP — impossible
=VLOOKUP("Alice",
  Employees!C:D, -2, FALSE)

Can't return column A if lookup is in column C.

XLOOKUP — works
=XLOOKUP("Alice",
  Employees!C:C,
  Employees!A:A)

Search column C, return from column A. Direction irrelevant.

2. Built-in not-found handling

VLOOKUP returns #N/A when no match. You wrap with IFERROR to show a friendly message. XLOOKUP's 4th argument is the not-found value — no wrapper needed.

VLOOKUP
=IFERROR(
  VLOOKUP(A2, table, 2, FALSE),
  "Not found")
XLOOKUP
=XLOOKUP(A2,
  table_col_1, table_col_2,
  "Not found")

3. Resilient to column inserts

VLOOKUP uses a hardcoded column index. Insert a new column before your return column and VLOOKUP still returns from the original index number — silently returning the wrong data.

XLOOKUP references the return column by range, not by index. Column inserts move the range with them; the formula still works.

4. Reverse search direction

XLOOKUP's 6th argument controls search direction. Set it to -1 to find the LAST matching value instead of the first. VLOOKUP has no equivalent — you'd need to sort the data descending and hope.

=XLOOKUP("Widget", A:A, D:D, "Not found", 0, -1)

Searches from the bottom of column A for "Widget". Useful for "latest order for this SKU" or "most recent record for this customer."

5. Exact match by default

VLOOKUP defaults to approximate match if you don't pass FALSE as the 4th argument. This has caused decades of silent bugs. XLOOKUP defaults to exact match — you have to opt into approximate.

Side-by-side comparison

AspectVLOOKUPXLOOKUP
Available sinceGoogle Sheets launchAug 2023 in Google Sheets
Lookup directionLeftmost column onlyAny column
Return directionRight of lookup columnAny direction
Default match typeApproximate (danger)Exact
Column referenceHardcoded index (fragile)Range reference (resilient)
Not-found handlingRequires IFERROR wrapperBuilt-in 4th argument
Search directionTop-to-bottom onlyTop-to-bottom OR bottom-to-top
WildcardsWith approximate match tricksYes (match_mode = 2)
Approximate matchRequires sort ASCModes for next-larger AND next-smaller
Formula lengthShorter for simple casesSlightly longer, but self-documenting
Excel compatibilityUniversal (all versions)Excel 2019+ / M365 only
Best forLegacy files, quick simple lookupsAll new work

Same problem, both approaches

Basic lookup by SKU

VLOOKUP
=VLOOKUP("C305",
  Products!A:E, 4, FALSE)

Look up in column A, return column 4 (D). Remember to pass FALSE.

XLOOKUP
=XLOOKUP("C305",
  Products!A:A,
  Products!D:D)

Explicit lookup and return ranges. Exact match by default.

With not-found handling

VLOOKUP
=IFERROR(
  VLOOKUP(F1, Products!A:E, 4, FALSE),
  "SKU not found")
XLOOKUP
=XLOOKUP(F1,
  Products!A:A, Products!D:D,
  "SKU not found")

Left lookup (impossible for VLOOKUP)

VLOOKUP
-- Can't do it directly.
-- Need INDEX-MATCH:
=INDEX(A:A,
  MATCH(F1, C:C, 0))
XLOOKUP
=XLOOKUP(F1, C:C, A:A)

One clean formula.

Find LATEST record (reverse search)

VLOOKUP
-- No native way.
-- Requires MAX + INDEX-MATCH
-- or sorting data descending
XLOOKUP
=XLOOKUP(F1,
  Orders!C:C, Orders!G:G,
  "Not found", 0, -1)

6th argument -1 = search bottom-up.

Migrating VLOOKUP to XLOOKUP

Straightforward pattern for converting simple VLOOKUP formulas:

-- Before
=VLOOKUP(A2, Data!A:E, 3, FALSE)

-- After
=XLOOKUP(A2, Data!A:A, Data!C:C)

Rules:

  1. The lookup key stays the same.
  2. The "range" argument in VLOOKUP becomes two arguments in XLOOKUP: the lookup column (leftmost of the VLOOKUP range) and the return column (offset by the index number).
  3. Drop the FALSE — XLOOKUP defaults to exact match.
  4. If you were wrapping with IFERROR, move that message into XLOOKUP's 4th argument.
Don't migrate en masse without testing

If VLOOKUP formulas are working, the risk-reward of converting is usually not worth it. Migrate when you're editing the surrounding formula anyway, or when a column insert breaks the VLOOKUP. Otherwise, "if it ain't broke..." applies here.

When VLOOKUP still wins

Three genuine reasons to keep using VLOOKUP:

1. Cross-platform compatibility with older Excel. If your workbook is opened in Excel 2016 or earlier, XLOOKUP will show #NAME?. VLOOKUP works in every Excel version ever released.

2. Familiarity for legacy users. A team that's used VLOOKUP for a decade may find XLOOKUP's syntax alien. There's no shame in sticking with what everyone knows if the formulas work.

3. Simple leftmost-column lookups. For a quick =VLOOKUP(A2, Table, 2, FALSE), VLOOKUP is fewer keystrokes. XLOOKUP requires typing the lookup range and return range separately. In simple cases, VLOOKUP is arguably faster to write.

The verdict

🎯 Bottom line

For new formulas, prefer XLOOKUP. Better default behavior, more flexibility, self-documenting syntax, cleaner error handling. There's no significant downside for Sheets users since 2023.

Keep VLOOKUP formulas that work. No urgent need to migrate. VLOOKUP isn't going anywhere — Google supports both indefinitely.

Learn both, teach XLOOKUP. Understanding VLOOKUP is essential for reading legacy files. Teaching new users XLOOKUP first is the right call — cleaner mental model, fewer footguns.

Frequently asked questions

Is XLOOKUP available in Google Sheets?

Yes. Google Sheets added XLOOKUP in 2023, bringing it to parity with Excel 2019+/Microsoft 365. All XLOOKUP arguments and behaviors work identically to Excel's version, including left lookups, built-in not-found handling, and reverse search direction. XLOOKUP is available on all Google Sheets tiers including free accounts.

Should I switch from VLOOKUP to XLOOKUP in Google Sheets?

For new formulas, yes. XLOOKUP is cleaner, handles left lookups natively, has built-in not-found messages, and doesn't break when columns are inserted. For existing VLOOKUP formulas that work, there's no urgency — VLOOKUP still functions normally. The main reason to migrate would be if you're modifying the surrounding formula anyway.

Does VLOOKUP still work in Google Sheets?

Yes, VLOOKUP is fully supported and won't be deprecated. Millions of workbooks rely on it, and Google has no plans to remove it. XLOOKUP is a modern alternative, not a replacement — both functions coexist and receive ongoing support.

What can XLOOKUP do that VLOOKUP can't in Google Sheets?

XLOOKUP has four capabilities VLOOKUP lacks: (1) look up in any column and return from any column — including left of the lookup column, (2) built-in not-found handling as a 4th argument, (3) reverse search direction (last-to-first via the 6th argument), and (4) exact match by default without needing to specify FALSE. VLOOKUP is limited to leftmost-column search returning rightward, uses hardcoded column indexes that break on inserts, and needs IFERROR wrapping for missing values.

Is XLOOKUP faster than VLOOKUP in Google Sheets?

For most datasets, they're equivalent — both are fast enough that any difference is imperceptible. On very large tables with many columns, XLOOKUP tends to be marginally faster because it doesn't scan the entire table width like VLOOKUP does. But performance shouldn't be the primary reason to choose between them; syntax clarity should.