Excel XLOOKUP complete guide for beginners

if you have used Excel for more than a year, you have written a VLOOKUP that returned the wrong column after someone inserted a field. you have IFERROR-wrapped a lookup three layers deep just to hide a single #N/A. you have given up on a left-side lookup and rebuilt your data instead.

XLOOKUP fixes all of that in one function. Microsoft released it for Excel 365 and Excel 2021, and it is now the default lookup tool every Excel user should learn first. it works in any direction, handles errors with one extra argument, and replaces VLOOKUP, HLOOKUP, INDEX/MATCH, and most LOOKUP variants with cleaner syntax.

this tutorial walks through XLOOKUP from zero. we use a 500-row sales tracker (Order ID, Date, Salesperson, Region, Product, Units, Revenue) so every example mirrors real analysis work. by the end you will know the six patterns that cover 95% of business lookups and the error-handling shortcuts that keep your dashboards clean.

Excel XLOOKUP is a single-function lookup that replaces VLOOKUP, HLOOKUP, and INDEX/MATCH. Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). It looks left or right, handles two-way lookups via nesting, returns a custom value when the match fails, and supports approximate, wildcard, and reverse search. For Excel 365 and Excel 2021 users, XLOOKUP should be the default lookup tool for new workbooks.

why XLOOKUP exists and which Excel versions support it

VLOOKUP shipped with Excel in 1985 and ruled lookups for 35 years. it works fine for simple right-side lookups, but it has structural limits: it cannot look left, breaks when columns are inserted, defaults to approximate match if you forget the FALSE flag, and runs slowly on large datasets. INDEX/MATCH solved the flexibility problem but at the cost of nested syntax that confuses every junior analyst on first encounter.

XLOOKUP, released in 2019 for Office 365 and folded into Excel 2021, combines the simplicity of VLOOKUP with the flexibility of INDEX/MATCH. one function, six arguments, every lookup case covered.

version compatibility matters before you commit:

Excel version	XLOOKUP support
Excel 365 (Microsoft 365 subscription)	yes
Excel 2021 (perpetual)	yes
Excel for the web	yes
Excel for Mac (current channel)	yes
Excel 2019	no
Excel 2016	no
Excel 2013 and older	no

if your workbook will be opened by anyone on Excel 2019 or older, the formula will display as _xlfn.XLOOKUP(...) and return #NAME? on their machine. for cross-version compatibility, fall back to INDEX/MATCH. see our Excel INDEX/MATCH tutorial for the legacy-safe pattern.

XLOOKUP syntax explained argument by argument

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

three required arguments, three optional arguments. the optionals are where XLOOKUP earns its keep.

lookup_value: what you are searching for. usually a cell reference like H2.
lookup_array: the column or row to search inside. select the entire column you want to scan, like C2:C501.
return_array: the column or row that holds the answer. select the entire column to return from, like G2:G501.
if_not_found (optional): what to return when the match fails. defaults to #N/A. set it to “Not found” or 0 to keep dashboards clean.
match_mode (optional): 0 for exact match (default), -1 for exact-or-next-smaller, 1 for exact-or-next-larger, 2 for wildcard.
search_mode (optional): 1 for first-to-last (default), -1 for last-to-first, 2 for binary ascending, -2 for binary descending.

90% of XLOOKUPs use the first three arguments plus if_not_found. learn those four first.

the most common XLOOKUP pattern

problem: given a salesperson name in H2, return their region.

=XLOOKUP(H2, C2:C501, D2:D501, "Not found")

read it left to right: look up the value in H2, search column C, return the matching value from column D, and if no match show “Not found” instead of #N/A.

[SCREENSHOT: Excel sheet with salesperson name in H2, formula bar showing the XLOOKUP, and the region appearing in the result cell]

compare that to the VLOOKUP equivalent (which only works if Salesperson is in the first column of the range, which it is not in our tracker):

=VLOOKUP(H2, C2:D501, 2, FALSE)

VLOOKUP needs the lookup column to be leftmost. XLOOKUP does not care about column order.

six XLOOKUP patterns that cover 95% of business lookups

pattern 1: simple lookup with custom error message

=XLOOKUP(H2, A2:A501, G2:G501, "Order not found")

the fourth argument is the cleanest improvement over VLOOKUP. no more =IFERROR(VLOOKUP(...), "Not found") wrappers. one argument does the work.

pattern 2: left-side lookup

problem: given a Region in column D, return the Order ID in column A.

=XLOOKUP("APAC", D2:D501, A2:A501)

VLOOKUP cannot do this without rearranging the data. XLOOKUP does not care which column is left or right.

pattern 3: two-way lookup (row and column)

problem: a pivot-style summary with months in column A and products across row 1. find the revenue at the intersection of “March” and “Widget Pro”.

=XLOOKUP("March", A2:A13, XLOOKUP("Widget Pro", B1:F1, B2:F13))

the inner XLOOKUP returns the column for “Widget Pro”. the outer XLOOKUP finds “March” in that column and returns the cell value.

pattern 4: approximate match for tiered lookups

problem: a discount table where customers spending $0-499 get 0%, $500-999 get 5%, $1,000-4,999 get 10%, and $5,000+ get 15%. given a customer spend, return the matching discount tier.

=XLOOKUP(H2, A2:A5, B2:B5, , -1)

the fifth argument -1 means “exact match or next smaller”. so a spend of $750 lands on the $500 tier. note the empty fourth argument (use commas to skip if_not_found and reach match_mode).

pattern 5: wildcard match for partial strings

problem: a salesperson list has full names (“Sarah Lee”, “Sarah Tan”) and you want to find any row that starts with “Sarah”.

=XLOOKUP("Sarah*", C2:C501, G2:G501, "Not found", 2)

match_mode 2 enables wildcards. * matches any characters, ? matches one character. great for fuzzy lookups in customer or supplier lists.

pattern 6: last-match lookup

problem: find the most recent order for a given customer. data is sorted oldest to newest.

=XLOOKUP(H2, B2:B501, A2:A501, "No orders", 0, -1)

the sixth argument -1 means search bottom-up. the first match XLOOKUP finds is the last order for that customer. without this, XLOOKUP returns the oldest order.

numbered walkthrough: build your first XLOOKUP

we will build pattern 1 from scratch on the sales tracker.

open your data. confirm Salesperson is in column C, Region in column D, rows 2-501.
click into an empty cell, say I2.
type =XLOOKUP(.
for lookup_value, click cell H2 (which holds the salesperson name to find). type a comma.
for lookup_array, select C2:C501. type a comma.
for return_array, select D2:D501. type a comma.
for if_not_found, type "Not found". close the parenthesis.
press Enter. you see the region for the salesperson in H2.
drag the formula down to I3, I4, I5 to apply to a list of names.
notice the ranges shifted as you dragged. fix them with absolute references: change C2:C501 to $C$2:$C$501 and D2:D501 to $D$2:$D$501.

final formula:

=XLOOKUP(H2, $C$2:$C$501, $D$2:$D$501, "Not found")

[SCREENSHOT: column H listing salesperson names, column I showing region results, formula bar showing the absolute-referenced XLOOKUP]

XLOOKUP with structured Tables

if your data is a Table (Ctrl+T to convert), the formula becomes self-documenting:

=XLOOKUP(H2, SalesData[Salesperson], SalesData[Region], "Not found")

the formula auto-expands when new rows arrive. structured references replace cell ranges, so a future reader can read the formula as English.

every analysis tutorial on this site recommends Tables for the same reason. see our Power Query Excel tutorial 2026 for the broader workflow that leans on Tables hard.

XLOOKUP vs VLOOKUP vs INDEX/MATCH at a glance

capability	VLOOKUP	INDEX/MATCH	XLOOKUP
look right	yes	yes	yes
look left	no	yes	yes
survives column insert	no	yes	yes
built-in if-not-found	no	no	yes
approximate match (tiered)	yes (default, risky)	needs +1/-1 logic	yes (clear arg)
wildcard match	yes (limited)	yes	yes
last-match search	no	reverse via INDEX/ROWS	yes (clear arg)
two-way lookup	no	yes	yes (nest two XLOOKUPs)
recalc speed on 100k rows	slow	fast	fast
Excel 2016 and older	yes	yes	no
syntax simplicity	medium	complex	simplest

XLOOKUP wins on every dimension except backwards compatibility. for new workbooks on a modern Excel install, default to XLOOKUP and only fall back to INDEX/MATCH when the file will travel to older versions.

handling XLOOKUP errors and edge cases

even with the if_not_found argument, XLOOKUP can throw errors. here are the three you will see most.

#N/A — match not found and no if_not_found set

cause: lookup_value does not exist in lookup_array, and you did not pass the fourth argument.

fix: always set if_not_found, even if it is just an empty string "".

=XLOOKUP(H2, C2:C501, D2:D501, "")

#VALUE! — array dimensions do not match

cause: lookup_array and return_array are different lengths. lookup_array has 500 rows but return_array has 499.

fix: select both ranges with identical dimensions. quickest way is to use Tables, which always keep columns the same length.

#SPILL! — return value would overlap an existing cell

cause: XLOOKUP can return an entire row or column when return_array is multi-column. if a cell next to your formula has data, the spill fails.

fix: clear cells to the right of your formula, or wrap in @XLOOKUP(...) to force a single-cell return. example: =@XLOOKUP(H2, C2:C501, A2:G501) returns just the first cell of the row.

advanced pattern: returning multiple columns at once

XLOOKUP can return an entire row by passing a multi-column return_array.

problem: given an Order ID, return Date, Salesperson, Region, Product, Units, and Revenue all at once.

=XLOOKUP(H2, A2:A501, B2:G501)

this spills six values across one row. great for quick “show me everything about this order” lookups in a dashboard.

combine with sort or filter for even more leverage, but that crosses into FILTER and SORT territory. for that workflow, see our Google Sheets QUERY function complete guide — Sheets has stronger built-in filter functions.

conclusion: make XLOOKUP your default lookup function

XLOOKUP is the cleanest lookup function Excel has ever shipped. one function replaces VLOOKUP, HLOOKUP, and most INDEX/MATCH cases, with built-in error handling that removes a layer of formula clutter from every workbook you build.

start with pattern 1 — simple lookup with custom error message — and use it for the next ten lookups you write. once that flow is muscle memory, move into left-side lookups (pattern 2) and approximate-match tiers (pattern 4). by week two of consistent use, you will reach for XLOOKUP without thinking.

if you support older Excel versions, keep INDEX/MATCH in your toolkit for those workbooks. if every machine in your org runs Excel 365 or 2021, you can quietly retire VLOOKUP forever.

next action: open a workbook you maintain regularly. find the gnarliest VLOOKUP or nested INDEX/MATCH in it. rewrite it as a single XLOOKUP. the formula will be shorter, the error handling will be clearer, and any future column insert will not break it.

Excel XLOOKUP Complete Guide for Beginners (2026)