XLOOKUP finds a value in one column and returns what’s on the same row of another. It’s the newer, safer way to join two lists in Excel.
B2 = 89 XLOOKUP found CR-205 among the codes and brought back the price on the same row.
Say a customer sends you a list of part codes, and the names and prices are in your catalog. XLOOKUP takes each code, finds it in the catalog and brings back that part’s name or price, so nobody copies them across by hand. It does the job VLOOKUP has done for decades, and fixes most of what goes wrong with it.
Below you build one step by step, then see the formula taken apart one argument at a time, each with a small sheet to try it on. That includes what VLOOKUP never had: a fallback for missing values, a nearest match for tiers, and a search that starts from the bottom.
This is Witsheet, Witsy’s in-browser spreadsheet. It works like Excel and checks each step as you go.
XLOOKUP needs three arguments: what to find, the column to find it in, and the column to bring back. It searches down the first column, stops at the first match, and returns the cell on the same row of the second.
B2 = 89 XLOOKUP went down the codes to CR-205 in row 4, then across to the Price column on the same row.
It takes three more, all optional: what to show when nothing matches, how close a match will do, and which end to search from. Each has a section below.
XLOOKUP only returns a value it found exactly, capitals aside. There’s no FALSE to remember. Leave FALSE off a VLOOKUP and it returns the nearest code below instead, a wrong price that looks right; VLOOKUP practice shows that trap.
B2 = 89 B2 reads its code from A2, finds exactly that code and nothing else, and brings back its price. No FALSE to remember.
A #N/A on a value you can see in the catalog is the data, not the formula. Data cleaning practice covers the fixes.
The column to bring back is a range you point at, not a number you count. So it can sit on either side of the codes, and inserting a column into the catalog doesn’t throw it off. VLOOKUP can’t do the first and breaks on the second. What XLOOKUP does need is two ranges that line up, row for row.
B2 = Crankset The part name sits left of the codes. VLOOKUP can only read to the right of the column it searches; XLOOKUP reads either way.
So lock both ranges before you fill the formula down: $E$2:$E$7, with F4 on each (Fn + F4
on most Mac keyboards). Lock only one and, a line down, the other starts a row lower: the right code,
read from the wrong row. Lock neither and both slide until the top of the catalog drops out of reach; VLOOKUP practice shows that one. Or make the catalog an Excel table, whose column references don’t move when copied.
From an exact-match lookup, #N/A means the code isn’t there. The fourth argument, if_not_found, puts your own value in its place. It only steps in when nothing matches,
so a broken range still shows its error, which an IFERROR wrapped around a VLOOKUP would hide.
B2 = 89 CR-205 is in the catalog, so the fallback never comes into it. Try A3.
Some lookups aren’t meant to match exactly. A price that drops at 10, 25 and 50 units, a tax rate by
income, a grade by score: the value falls between two rows, and the answer is the row it falls into.
The fifth argument, match_mode, says what will do.
B2 = 10 30 falls between the 25 and 50 tiers. Exact or next smaller takes 25, the tier 30 belongs to. This is the mode for tiers, tax bands and grades.
Unlike VLOOKUP’s approximate match, it doesn’t need the table sorted. The last mode, 2, matches
wildcards: * stands for any run of characters and ? for one, so "CR*" finds the first code that starts with CR.
In a log that grows downward, a code can appear many times, and the row you want is usually its last.
XLOOKUP searches top down and stops at the first match. The sixth argument, search_mode, turns it around.
B2 = 32 Top down, XLOOKUP stops at the first CH-118 it meets: January’s 32, two price changes ago.
To reach the sixth argument you write the fourth and fifth too, as above: a fallback, and 0 for an
exact match. Search modes 2 and -2 are binary search, a faster way through a large sorted list. On a
list that isn’t sorted they return wrong answers, so leave them until you know you need them.
XLOOKUP is in Microsoft 365, Excel 2021 and later, Excel for the web and Google Sheets. Excel 2019 and older don’t have it: a formula using it shows #NAME? there, including when a colleague opens your file in one of those versions.
So where you have XLOOKUP, write XLOOKUP. You’ll still read plenty of VLOOKUPs: most workbooks you inherit are built on them, and many Excel tests for job applicants ask for VLOOKUP by name. VLOOKUP practice takes the older formula apart the same way.
To carry on in your own Excel, download the workbook. It’s the same kind of job at full size: 60 products on one sheet, 120 orders that record only the SKU on another, and five tasks to work through with XLOOKUP or VLOOKUP, with the answers on a sheet of their own.
A 60-product catalog and a separate order log that only records SKUs — built for lookup practice. Pull prices into the orders with VLOOKUP or XLOOKUP, then compute order totals.
180 rows · SKU · Product · Category · Unit Price · In Stock
Free · fictional data · opens in Excel and Google Sheets
Witsy’s Excel course teaches a few hundred small skills, called Wits, the way this page teaches XLOOKUP: in a real spreadsheet, a step at a time. The placement test finds the ones you already have.
Take a few minute test to see how good you actually are with spreadsheets. Witsy creates a map of the Wits you should learn next.
Take the placement test Or just start learning →