VLOOKUP wants the lookup column on the left, a hard-coded column number, and a quiet prayer that nobody inserts a column upstream. XLOOKUP drops all three demands. =XLOOKUP(value, lookup_range, return_range) looks left or right, survives inserted columns, and — with one optional argument — returns a clean message instead of #N/A when nothing matches. If your Excel is 2021 or Microsoft 365, this is the lookup to reach for first, and this guide walks the syntax, the everyday lookups, and the three optional arguments that make switching worth it.
The syntax, and the arguments you’ll actually use
XLOOKUP takes up to six arguments, but the first three do the work and the rest are optional refinements you add only when a specific job calls for them.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| Argument | What it does |
|---|---|
| lookup_value | What you’re searching for |
| lookup_array | The column (or row) to search in |
| return_array | The column to return a result from |
| if_not_found | What to return when there’s no match |
| match_mode | Exact, approximate, or wildcard |
| search_mode | Search top-down or bottom-up |
Three required, three optional
The contrast with VLOOKUP lives in those first three arguments. VLOOKUP bundles the search and the return into one table plus a column number you count by hand; XLOOKUP names the search column and the return column separately. That separation is the whole reason it doesn’t care about column order or which side the answer sits on — there’s no “third column from the left” to drift out of position when someone edits the sheet. The last three arguments are there when you need them and invisible when you don’t, which keeps the everyday formula short.
Your first XLOOKUP
Put a small product table on a sheet and follow along — SKU in column A, product in B, price in C:
| A | B | C | |
|---|---|---|---|
| 1 | SKU | Product | Price |
| 2 | SKU-101 | Notebook | $4.20 |
| 3 | SKU-102 | Pencil | $2.80 |
=XLOOKUP("SKU-102", A2:A3, C2:C3)This searches for SKU-102 in the SKU column and returns the matching price — $2.80. No column counting, no table-array width to get wrong, no fourth argument needed for a basic exact match. You read the formula left to right and it says exactly what it does: look up this value, in this column, return from that column.
Why inserted columns stop breaking it
Insert a “Category” column between B and C tomorrow and the formula still returns the right price. VLOOKUP’s col_index_num would now point one column off and silently return the wrong field — the single most common lookup bug in shared workbooks. XLOOKUP references the price column directly, so it moves with the data instead of breaking against it. On a sheet several people edit, that durability matters more than any single feature.
Return more than one column at once
=XLOOKUP("SKU-102", A2:A100, B2:C100)Point return_array at two or more adjacent columns and XLOOKUP returns all of them, spilling the product name and price into neighboring cells from a single formula. This retires the old habit of writing one VLOOKUP per field, each with its own column number to keep straight. Here a single lookup pulls several fields at once, and they stay aligned because they all came from the same matched row. It’s especially useful when you’re building a summary card for a record — one XLOOKUP, and the name, price, and stock all appear together, updating as a group whenever the source changes.
Stop the #N/A with if_not_found
By default, a lookup that finds nothing returns #N/A — fine when you’re the only reader, ugly in a report, and poison to any formula that consumes the result. The fourth argument handles it inline, with no IFERROR wrapper around the whole thing:
=XLOOKUP("SKU-999", A2:A3, C2:C3, "Not found")Now an unmatched lookup reads “Not found” instead of throwing an error that ripples into every cell downstream. This is the difference between a dashboard that degrades gracefully and one that lights up red the first time a code is mistyped.
The fallback can be anything — a label, a 0, or an empty string "" so unmatched rows simply stay blank. Returning blank keeps charts and SUM formulas that read the column from choking on #N/A, so pick the fallback the next formula downstream can swallow without special handling. A column of “Not found” reads well to a human; an empty string reads better to another formula.
=IFERROR(VLOOKUP(...), "Not found") — two functions to do one job. XLOOKUP folds the fallback into the lookup itself, which reads cleaner and leaves one less place for a typo to hide.
Look left, and search from the bottom
Two things VLOOKUP simply cannot do, XLOOKUP does with no special effort. Both come up often enough to be worth committing to memory.
Reading a value to the left
=XLOOKUP("Pencil", B2:B3, A2:A3)Here the lookup column (B, product) sits to the right of the return column (A, SKU), and XLOOKUP returns SKU-102 without complaint. VLOOKUP would require physically rearranging the columns so the key sits leftmost — often impossible on a shared sheet other formulas depend on. Because XLOOKUP names both columns independently, “left” and “right” simply stop being a constraint.
Returning the last match instead of the first
=XLOOKUP("SKU-101", A2:A100, C2:C100, , , -1)Setting search_mode to -1 searches from the bottom up and returns the last match rather than the first. That’s exactly what you want when a key appears several times and the most recent entry is the one that counts — the latest price for a SKU, the most recent transaction for an account, the current status after several updates. The two empty arguments hold the default if_not_found and match_mode so you can reach the sixth argument.
Match modes: exact, approximate, and wildcard
The fifth argument decides how strict the match is. The default is exact, which is almost always right, but two other modes earn their place.
| match_mode | Behavior | Use when |
|---|---|---|
| 0 (default) | Exact match only | IDs, codes, names |
| -1 / 1 | Exact, else next smaller / larger | Tax brackets, tiered pricing |
| 2 | Wildcard match | Partial text with * or ? |
Wildcards for partial text
=XLOOKUP("SKU-1*", A2:A100, B2:B100, "No match", 2)With match_mode 2, the asterisk matches any run of characters, so this finds the first SKU beginning with “SKU-1”. A question mark matches a single character. Wildcards are useful when you only know part of the value — a partial order number, a name you’re not sure how to spell in full. Approximate mode (-1 or 1) powers a different job: tax-bracket and shipping-tier lookups, where you want the band a value falls into rather than an exact hit. Reach for both deliberately and leave the default exact for anything identity-like, where a near-miss is worse than no answer at all.
One subtlety with approximate mode: the data needs to be sorted for the next-smaller and next-larger logic to land where you expect. For a tax table sorted ascending by threshold, match_mode -1 returns the correct bracket for any income between thresholds — the workhorse behind tiered pricing and commission schedules without a nest of IF statements. Unsorted data with approximate mode returns confident nonsense, which is worse than an error because nothing flags it.
Mistakes, and when the older functions still fit
XLOOKUP is forgiving, but a few things still trip people up. Walk these before assuming a result is wrong.
- ✓ lookup_array and return_array are the same height — mismatched ranges throw #VALUE
- ✓ You’re on Excel 2021 or 365 — XLOOKUP doesn’t exist in 2019 or earlier
- ✓ match_mode is left at exact unless you specifically need approximate or wildcard
- ✓ The result feeds cleanly downstream — use if_not_found rather than letting #N/A propagate
That compatibility point is the real catch. If a workbook has to open in Excel 2019 or older, XLOOKUP isn’t there and INDEX MATCH remains the portable choice that runs everywhere. For multi-criteria summaries rather than single lookups, SUMIFS and COUNTIFS are the right tools, and XLOOKUP sits comfortably alongside the rest of the essential Excel formulas every analyst leans on. Microsoft’s own XLOOKUP reference documents every argument in full.
One last habit: when a lookup gets copied across a row or down a column, lock the lookup and return arrays with absolute references ($A$2:$A$100) so the search window doesn’t drift. XLOOKUP removes VLOOKUP’s column-counting fragility, but it inherits the same relative-reference behavior every Excel formula has — drag it unlocked and each copy quietly searches a shifted range, returning answers that look plausible and are wrong.
The short version
=XLOOKUP(value, lookup, return) — and add if_not_found by habit. Those two cover the overwhelming majority of real lookups.
For a single lookup on a modern Excel, XLOOKUP is the default: three arguments for the common case, a built-in fallback for misses, and native support for left lookups, reverse search, and wildcards when a job needs them. Keep INDEX MATCH in your pocket for files that must run on older Excel, and reach for XLOOKUP everywhere else. Write a handful of them and the column-counting and IFERROR-wrapping of the VLOOKUP era quietly stop being things you miss.