XLOOKUP Guide in 2026 (The Function That Replaced VLOOKUP)

The complete guide to XLOOKUP — why it's better than VLOOKUP, the syntax that trips up new users, and the advanced tricks that make it Excel's most powerful function.

Microsoft introduced XLOOKUP in 2019 to fix everything VLOOKUP got wrong. Five years later, it's clearly the right default for every new Excel formula in modern versions, and the migration from VLOOKUP-heavy spreadsheets is worth the muscle memory adjustment. This guide covers what XLOOKUP actually does, the syntax that trips up VLOOKUP veterans, the advanced cases (two-way lookup, approximate match, reverse search), and when you should still reach for an older function.

Why XLOOKUP Exists

VLOOKUP shipped in 1985 with three baked-in limitations: it could only search left-to-right, it referenced columns by position (so inserting a column broke every formula), and the default match type was approximate (so forgetting one argument caused silent wrong results). Microsoft tried to fix this with HLOOKUP and INDEX/MATCH, but each had its own awkwardness. XLOOKUP was the clean-slate redesign — one function that handles every lookup case, with safer defaults and cleaner syntax.

The practical effect is that XLOOKUP feels like Excel finally caught up to how people actually want to use it. Searching backward through a column. Returning a whole row of data. Handling missing values gracefully. All of these required workarounds before; in XLOOKUP they're just arguments. If you have Excel 365 or 2021+, there's no good reason to use VLOOKUP for new formulas.

The Syntax in Plain English

XLOOKUP takes up to six arguments. The first three are required, the last three are optional and rarely needed. The required three handle 90% of lookups.

Argument	What it does	Notes
lookup_value	What you're searching for	Cell reference or literal value
lookup_array	The column/row to search in	Single column or row, any direction
return_array	What to return when found	Can be a single column, row, or whole range
[if_not_found]	What to return when no match	Replaces IFERROR wrapping
[match_mode]	0 = exact (default), -1/1 = approximate, 2 = wildcard	Almost always leave at default
[search_mode]	1 = first to last (default), -1 = last to first, ±2 = binary search	Use -1 to find the most recent match

The simplification compared to VLOOKUP is significant. No counting columns. No remembering to pass FALSE. No fighting with helper columns when the lookup column isn't leftmost. Just say what you're looking for, where to look, what to return.

Basic Examples That Cover 90% of Cases

Three patterns handle the majority of XLOOKUP work. Find a salary by name where names are in column A and salaries in column B:

Same lookup but with a friendly message instead of #N/A when the name isn't found:

The case VLOOKUP physically can't handle — searching right-to-left. Names are in column D, IDs in column A; find the ID for a given name:

This last example is the killer feature. Any time you'd reach for INDEX/MATCH because VLOOKUP can't search backwards, XLOOKUP replaces both functions with cleaner syntax.

XLOOKUP vs VLOOKUP, Side by Side

The differences aren't subtle. Across every dimension that matters in real Excel work, XLOOKUP is better.

Factor	XLOOKUP	VLOOKUP
Default match type	Exact	Approximate (dangerous!)
Search direction	Any (left, right, up, down)	Left-to-right only
Column reference	By array (insert-safe)	By number (breaks on insert)
Error handling	Built-in if_not_found argument	Requires IFERROR wrapper
Speed on large data	Faster	Slower
Excel versions	365, 2021+	All versions

The only legitimate reason to keep using VLOOKUP is sharing files with people on older Excel versions. Within your own organization on modern Excel, every new formula should be XLOOKUP.

Advanced Cases That Earn the Function

The basic XLOOKUP cases are easy. The advanced cases are where the function really separates from its predecessors. Five patterns are worth knowing once you're comfortable with the basics.

The two-way lookup pattern alone makes the migration worth it. INDEX/MATCH could do this, but the syntax was harder to read. XLOOKUP nesting feels natural.

XLOOKUP with Multiple Criteria

The native XLOOKUP, like VLOOKUP, takes a single lookup column. For multiple criteria — find the salary for "John" in the "North" region — there are two clean approaches. The concatenation method works in all cases:

For Excel 365 users, the FILTER alternative is sometimes cleaner — especially if you might need multiple matches:

Use whichever your team finds more readable. Both work; neither is dramatically faster.

Common Errors and Fixes

XLOOKUP has fewer error modes than VLOOKUP because it's been designed with safer defaults. The three errors you'll see all have specific fixes.

Error	Cause	Fix
#N/A	Lookup value not found	Add if_not_found argument: XLOOKUP(...,..., "Not found")
#VALUE!	Mismatched array sizes	Make sure lookup_array and return_array have the same number of cells
#NAME?	XLOOKUP not available in your Excel version	Upgrade to 365/2021+, or use INDEX/MATCH for compatibility

The #VALUE! error catches most beginners. If your lookup_array is A2:A100 (99 cells) and your return_array is B1:B100 (100 cells), XLOOKUP returns #VALUE!. The fix is making sure both arrays cover the same rows.

Performance Notes

XLOOKUP is meaningfully faster than VLOOKUP on large datasets — typically 2-3x faster on 50,000+ row tables, with the gap widening at larger scales. The speed comes from a better internal algorithm that doesn't rely on column position. For most spreadsheets under 10,000 rows the speed difference is invisible, but for serious data work the performance gain is one more reason to migrate.

Two practices help XLOOKUP run as fast as possible. Use exact match (the default) — it's faster than approximate match for unsorted data. Avoid full-column references (A:A) when you can — pointing at A2:A1000 is faster than A:A even though both work.

When INDEX/MATCH Still Wins

For Excel 2019 and earlier, XLOOKUP isn't available — INDEX/MATCH is the right tool. For files you'll share with users on older versions of Excel, also INDEX/MATCH. For very specific cases involving multi-criteria lookup with complex boolean logic, INDEX/MATCH sometimes reads more cleanly than XLOOKUP. Otherwise, XLOOKUP wins on every dimension.

The pragmatic rule: write all new formulas in XLOOKUP. Keep INDEX/MATCH around for compatibility. Migrate VLOOKUPs only when you're already editing the file for other reasons — bulk migration isn't worth the risk of introducing bugs.

Migration Strategy from VLOOKUP

Don't bulk-rewrite VLOOKUPs in production spreadsheets. The risk of introducing bugs is real, and the performance gain rarely justifies the work. Instead, replace VLOOKUPs incrementally as you edit existing formulas for other reasons. Within 6-12 months of normal Excel use, most of your active spreadsheets will have migrated naturally.

For new spreadsheets, default to XLOOKUP from the start. Train your muscle memory on the new syntax. After two weeks of consistent use, the simpler syntax becomes obviously preferable to VLOOKUP's column-counting awkwardness.

Common Mistakes

The mistakes new XLOOKUP users make cluster around two patterns. The first is overusing the optional arguments — most lookups need only the three required arguments and maybe if_not_found. Forcing match_mode and search_mode parameters when you don't need them creates harder-to-read formulas. The second pattern is sticking with VLOOKUP-style thinking — manually concatenating helper columns, wrapping with IFERROR when if_not_found exists, fighting with absolute references that XLOOKUP handles naturally. Embrace the new syntax fully.

Frequently Asked Questions

The Bottom Line

XLOOKUP is what VLOOKUP should have been from the start. Safer defaults, cleaner syntax, better performance, and the flexibility to handle every lookup case in one function. If you have Excel 365 or 2021+, default to XLOOKUP for every new formula. Migrate VLOOKUPs incrementally when you're already editing the file. Within a few months of consistent use, the new syntax becomes obviously preferable, and you'll wonder why you ever counted columns.