XLOOKUP vs VLOOKUP is the Google Sheets showdown most teams should settle in 2025. If you’ve ever broken a dashboard by inserting a new column, or spent hours debugging “#N/A” in a client report, this guide is for you. Below, you’ll get a plain‑English comparison, copy‑paste formulas, and a 30‑minute migration plan so you can finally switch from VLOOKUP to XLOOKUP with confidence.
XLOOKUP vs VLOOKUP in Google Sheets: speed, stability, and flexibility in 2025.
XLOOKUP vs VLOOKUP: the quick answer
XLOOKUP is more flexible and safer than VLOOKUP. It looks left or right, returns multiple columns, supports exact/closest match modes, and won’t break when you insert columns.
VLOOKUP is older, one‑directional (right only), fragile with column insertions, and limited match control.
If you build anything long‑lived (dashboards, ops sheets, client reports), switch to XLOOKUP.
Cheat sheet: why XLOOKUP replaces VLOOKUP for most use cases.
What XLOOKUP can do that VLOOKUP can’t
Look left or right: No more rearranging tables just to match on a left column.
Return multiple columns at once: Spill a whole row of results in one go.
Robust to structure changes: Column inserts don’t break the formula.
Built‑in not‑found handling: Friendly messages instead of raw #N/A.
Match modes: Exact match, next smaller, or next larger—powerful for pricing tiers and ranges.
Wildcard matching: Use ? and * to match patterns (handy for SKUs).
Return multiple columns with a single XLOOKUP—no extra helper formulas.
Where VLOOKUP still works (and when to keep it)
One‑off quick checks in small ad‑hoc tables.
Legacy sheets where changing formulas is risky today.
Team members who only know VLOOKUP and need a short‑term fix.
However, for any sheet you’ll share, scale, or maintain—prefer XLOOKUP.
XLOOKUP syntax (Google Sheets) with examples
Syntax:
=XLOOKUP(search_key, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])
Parameters to remember:
search_key: the value you’re matching (e.g., an email or SKU)
lookup_range: the column or row that contains the key
return_range: the column(s) or row(s) to return
if_not_found (optional): message or value when no match is found
match_mode (optional): 0 exact (default), -1 next smaller, 1 next larger, 2 wildcard
Can only return from a column to the right of the search column.
Relies on a numeric index; insert a column and your index becomes wrong.
is_sorted TRUE needs sorted data and returns approximate matches—often not what you want.
Typical exact‑match pattern (wrapped):
=IFERROR(VLOOKUP(A2, Products!A:E, 4, FALSE), "Missing product")
Compare this to the equivalent XLOOKUP that’s easier to read and harder to break:
=XLOOKUP(A2, Products!A:A, Products!D:D, "Missing product")
Convert your VLOOKUPs to XLOOKUPs (step‑by‑step)
Identify the search_key column used in VLOOKUP (first column of the range).
Replace the VLOOKUP range with two ranges: lookup_range and return_range.
Remove the VLOOKUP index; XLOOKUP uses explicit return ranges.
Set [if_not_found] to keep user‑friendly messages.
Optional: choose a [match_mode] if you need tiered or wildcard matching.
Example migration:
Before (VLOOKUP): =IFERROR(VLOOKUP(A2, SKUs!A:H, 6, FALSE), "No match")
After (XLOOKUP): =XLOOKUP(A2, SKUs!A:A, SKUs!F:F, "No match")Map VLOOKUP parts to XLOOKUP: search key → lookup range → return range.
Advanced patterns you’ll actually use
Multi‑criteria lookup (email + date)
Build a helper key inline using & and use wildcard if needed.
=XLOOKUP(A2 & "|" & TEXT(B2, "yyyy-mm-dd"),
FILTER(Table!A:A & "|" & TEXT(Table!B:B, "yyyy-mm-dd"), LEN(Table!A:A)),
Table!E:E, "No record")
2D lookup (row & column headers)
Combine two XLOOKUPs: one to find the column, one to find the row.
=XLOOKUP(A2, Items!A:A,
XLOOKUP(B1, Items!1:1, Items!2:999),
"Missing")
Partial text matches (contains)
=XLOOKUP("*" & A2 & "*", Descriptions!B:B, Descriptions!C:C, "No match", 2)
Return a whole record (spill)
=XLOOKUP(A2, Leads!A:A, Leads!B:G, "Not found")
Error handling, arrays, and performance tips
Friendly errors: Use [if_not_found] to avoid nested IFERROR in most cases.
ARRAYFORMULA: XLOOKUP can spill across columns; for down‑column fill, prefer placing XLOOKUP at the top of a results block and letting it spill rather than dragging.
Volatile helpers: Minimize repeated expensive ranges (e.g., use named ranges for large tables).
Data types: Ensure numbers are numbers; stray text digits cause misses. Use VALUE() to coerce if needed.
INDEX MATCH vs XLOOKUP (do you still need it?)
INDEX MATCH used to be the flexible alternative to VLOOKUP. XLOOKUP now consolidates that power with clearer syntax and extras like [if_not_found] and match modes. Keep INDEX MATCH in your toolkit for niche array gymnastics, but for day‑to‑day business lookups, XLOOKUP wins.
INDEX MATCH example for context:
=INDEX(Products!D:D, MATCH(A2, Products!A:A, 0))
Equivalent XLOOKUP:
=XLOOKUP(A2, Products!A:A, Products!D:D, "Missing product")
Implementation guide: migrate a sheet in 30 minutes
Audit: Find your top 5 VLOOKUP formulas (use Find for “VLOOKUP(”).
Map: For each, write down search key, lookup column, return column(s), and desired not‑found text.
Replace: Convert one formula at a time to XLOOKUP. Test with 3–5 known cases.
Spill where helpful: Replace chains of VLOOKUPs with one XLOOKUP that returns multiple columns.
Document: Add a note near the header: “Uses XLOOKUP, exact match.”
Lock: Protect key ranges to prevent accidental column inserts that could confuse teammates.
Scope → Replace → Test → Spill → Document → Protect. That’s your migration loop.
Premium spreadsheet UI kits and dashboards: Envato
Deals on productivity add‑ons and templates: AppSumo
Disclosure: Some links are affiliate links. If you click and purchase, we may earn a commission at no extra cost to you. We only recommend tools we’d use ourselves.
