XLOOKUP Multiple Criteria: 8 Examples and a Free Practice File

To use XLOOKUP with multiple criteria, multiply one TRUE or FALSE test per column and look up 1:

The three patterns, on the practice file’s own ranges:

• AND, multiply with *: store in column B, order number in C, amount in G (lesson 2). For EU and 1002 it returns 45.90.
• OR, add with +: color in column C, price in F (lesson 3). It returns the price on the first row that is black or navy. Adding works here because one cell cannot hold both colors.
• Join with & and a "|" separator: UK and 1003 become the key UK|1003, which returns 96.00. The separator keeps pairs such as TS|XL and TSX|L apart.

In Google Sheets, wrap the multiply and join formulas in ARRAYFORMULA, as shown further down.

In the practice file, order 1003 exists in the US, UK and EU stores. A plain XLOOKUP on the order number stops at the first 1003 and returns 210.50 from the US store. With the store as a second test, the formula returns the UK amount, 96.00.

Below are 8 worked examples on the same made-up store: the basics, two and three criteria, a blank instead of 0, two lists on two sheets, a two-way lookup, the latest match and six common errors. Each one is a lesson in a free practice file that shows a ✓ as soon as your answer is right.

Download the free XLOOKUP practice file (Excel, 8 lessons)

Every lesson uses one made-up online store that sells in the US, UK and EU, with the data on the left of each sheet and the tasks on the right. Every example on this page uses this file’s data.

It is a plain .xlsx workbook with formulas only and no macros, last updated 2 November 2026. Open the Start here sheet first, and click Enable Editing if Excel opens the file in Protected View. The sheets are protected except for the cells the lessons use, so try the extra examples on this page in a new workbook.

Watch the XLOOKUP walkthrough

The walkthrough opens with the VLOOKUP trap (insert a column and VLOOKUP quietly returns the wrong column), explains what XLOOKUP is and where VLOOKUP fails, then works through all eight lessons in order, building or fixing each formula and showing the ✓ it earns.

• 0:00 Why XLOOKUP beats VLOOKUP
• 0:20 Start here: the free practice file
• 0:39 What is XLOOKUP?
• 1:11 Where VLOOKUP fails
• 2:43 XLOOKUP basics: look left, 3 columns, not found
• 3:59 XLOOKUP with two criteria: join or multiply
• 5:22 XLOOKUP multiple criteria: product, color, size
• 6:43 XLOOKUP returns 0? Show a blank
• 7:37 Compare two lists on two sheets
• 8:26 Two-way lookup: row and column
• 9:02 Find the latest match
• 10:02 Fix 6 common XLOOKUP errors
• 11:00 Practice file with answers

XLOOKUP syntax: the 6 arguments

XLOOKUP finds a value in one column or row and returns the matching item from another, stopping at the first match. Three arguments are required and three are optional:

Coming from VLOOKUP? XLOOKUP needs no column number, matches exactly by default and can look to the left. Our VLOOKUP, XLOOKUP and INDEX MATCH comparison covers the rest.

XLOOKUP basics: four formulas to start with

Lesson 1 has 12 products with a SKU (product code), name, category, price and stock in columns B to F. Type these four formulas in J6 to J9:

• Price by SKU: BAG-020 returns 64.90.
• Look to the left: Desk Lamp returns LMP-450 from column B, left of the product column, which VLOOKUP cannot do.
• Several columns: a three-column return range makes BOT-075 spill Kitchen, 24.00 and 310 into three cells, so keep the two cells to the right empty.
• Your own message: CAP-999 is missing, so the fourth argument shows “Not found” instead of #N/A.

The $ signs lock each range, so the ranges stay in place when you copy the formula down. Without them, the ranges move down as you copy, and the top rows drop out of the search.

XLOOKUP with 2 criteria: join or multiply

Lesson 2 lists 12 orders from three stores. Each store numbers its own orders, so order 1003 appears three times. Look up the UK order by number alone and XLOOKUP stops at the first 1003:

That returns 210.50 from the US store instead of the UK amount, 96.00, and nothing warns you. There are two ways to match both the store and the order number.

Method 1: join the criteria with & and a separator

Join the two values into one key, and the two columns the same way, so UK|1003 is compared with US|1001, UK|1001 and so on down the list:

It returns 96.00. Keep the "|" separator: without it, different pairs can produce the same key. Product TS in size XL and product TSX in size L both become TSXL, and XLOOKUP returns whichever comes first. With it they stay TS|XL and TSX|L. Use a character that never appears in your data.

Method 2: multiply the conditions and look up 1

Write one test per criterion in parentheses, multiply them, and look up 1:

Each test returns 12 TRUE or FALSE values. Multiplying turns TRUE into 1 and FALSE into 0, so a row scores 1 only when both tests are true (TRUE*TRUE=1). For EU and 1002 the result is {0;0;0;0;0;1;0;0;0;0;0;0}, so XLOOKUP finds the single 1 and returns 45.90. Change the return range to get another column:

Which one to use: for XLOOKUP with 2 criteria and exact matches, the join is easier to read. Multiplying works better for three or more criteria and accepts any test, such as ($G$6:$G$17>50) for amounts over 50. On very large tables, a helper column that joins the criteria once can be faster.

XLOOKUP with multiple criteria (3 or more)

Lesson 3 lists 12 variants of a T-shirt, a hoodie and a cap by color and size, each with a SKU, price and stock. A lookup on the product alone returns the first T-shirt row, black in small at 19.99, when you want black in large at 21.99. Add one test per column:

Only the T-Shirt, Black, L row multiplies to 1, so the formula returns 21.99. Each extra criterion is one more test and one more *. To return another column, change only the return range:

There is no navy hoodie in small, so the last task adds a message as the fourth argument:

XLOOKUP multiple results: use FILTER instead

XLOOKUP returns one match. To list every match, give the same tests to FILTER. This one lists the black T-shirt sizes in stock, with a greater-than test on the stock column:

It spills S and L, since the medium has 0 in stock. For OR logic, add tests instead of multiplying them: ($C$6:$C$17="Black")+($C$6:$C$17="Navy") keeps either color.

XLOOKUP multiple criteria in Google Sheets

Google Sheets has XLOOKUP too, and the multiply method works there once you wrap it in ARRAYFORMULA. Press Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac) while you edit the formula, and Sheets adds the wrapper for you:

The join method needs the same wrapper. The practice file is made and tested for Excel, so practice there and copy the pattern to Sheets.

XLOOKUP returns 0? Show a blank instead

Lesson 4 lists 8 orders, and some tracking numbers and discounts are still empty. Order 2002 has no tracking number yet, but a plain XLOOKUP shows 0:

The lookup worked: it returned the order’s tracking cell, which is empty, and a formula that returns an empty cell shows 0, just as =D7 does when D7 is empty. That is why if_not_found does not help here. There are two fixes.

For text: add an empty string

Joining "" turns the result into text, so the empty cell shows as a blank while order 2003 still shows TRK-77314. Use it for text such as tracking numbers or notes.

For numbers and dates: LET and IF

LET names the result d, so the lookup runs once, and IF shows a blank only when d is empty. Order 2006 keeps its real 0% discount as a number, and order 2005, with no discount, shows a blank. With &"" instead, that 0% would become the text “0”, which SUM and AVERAGE skip.

XLOOKUP returns 0 but the value exists?

Check these four causes in order:

• The return cell is empty. It shows as 0, or as a date such as 1/0/1900 or 00-Jan-1900 in a date column. Use one of the two fixes above.
• The ranges do not line up. If the return range starts a row lower than the lookup range, every answer comes from the next row, as lesson 8 shows. Start both ranges on the same row.
• The value is in the list twice. XLOOKUP stops at the first match, as with order 1003 in lesson 2, and that row may hold the empty cell. Add a second criterion.
• The 0 is your own. In lesson 5, if_not_found is 0, so a 0 means the order is not on the Shipments sheet.

The number format 0;-0;;@ hides zeros on screen, but it hides real zeros too, and it rounds 21.99 to 22. For prices, use #,##0.00;-#,##0.00;;@ instead.

XLOOKUP from another sheet: compare two lists

Lesson 5 checks 8 orders against a Shipments sheet that lists only 6 of them, in a different order. This formula returns each shipped quantity, or 0 for orders that are not on that sheet:

Click the Shipments tab while you type to pick its ranges. Excel adds the sheet name and an exclamation mark, plus single quotes if the name has a space, as in 'Shipments 2026'!$B$5:$B$10. Press F4 (Fn+F4 on some laptops, Cmd+T on a Mac) to lock the ranges, fill down, then compare the two quantities in column F:

Two orders are short and two have not shipped. You can also look up all 8 order numbers at once. With E7:E13 empty, type this in E6, and the results spill down to E13:

Two-way XLOOKUP: criteria in rows and columns

Lesson 6 prices parcels from a rate table with four zones down the side and five weight bands across the top. Each band is an upper limit, so a 1.4 kg parcel falls in the band up to 2 kg:

One XLOOKUP inside another finds the column, then the row:

The inner XLOOKUP uses match_mode 1, exact or next larger, to pick the weight band and return its column of four rates. The outer one picks the zone from that column, so zone 2 at 0.3 kg returns 8.90, zone 3 at 1.4 kg returns 22.00, zone 1 at 4.2 kg returns 11.50 and zone 4 at 7.5 kg returns 66.00.

Without match_mode, 1.4 kg returns #N/A, because 1.4 is not in the header row. With -1 it returns 17.50, the 1 kg rate, which undercharges. Use -1 when each limit is a starting point, such as a discount that starts at 10 units.

The inner XLOOKUP is a horizontal lookup: it searches the header row C5:G5, so XLOOKUP replaces HLOOKUP too. When your criteria are in header rows across the top, multiply one test per header row and return a row of the same width:

XLOOKUP last match: find the latest order

Lesson 7 is an order log of 12 rows in date order, and some customers appear more than once. XLOOKUP returns the first match, so a lookup on Sofia Rossi returns her oldest order date, 2 October 2026. Set the sixth argument, search_mode, to -1 and it searches from the bottom up:

Now it returns 18 October 2026, her latest order. The empty argument between the commas skips if_not_found, and 0 keeps the exact match. On the product column, Liam Johnson’s latest purchase is the 65W USB-C Charger; on the amount column, Emma Clarke’s latest order is 19.99.

Sort the list first. A reverse search returns the last match in the list, which is the latest only when rows run from oldest to newest. If yours do not, find the latest date with MAXIFS and use it as a second criterion:

On the practice data it also returns the 65W USB-C Charger, and it works even when the rows are not sorted. Type it in I8 and it earns the same ✓.

XLOOKUP not working? 6 errors and fixes

Lesson 8 opens with six broken formulas, each a common XLOOKUP problem. Each fix turns a ✗ into a ✓:

Shifted ranges are the risky one. They show 0 here only because the row under the list is empty. For other SKUs they return the next row’s value with no error at all: BAG-020 gets 24.00, the price of the bottle below it.

If the space or the text is in the list instead, clean the range inside the formula, as in =XLOOKUP(TRIM(H6),TRIM($B$6:$B$11),$D$6:$D$11), or look up H7&"" when the list holds numbers stored as text.

Do not hide errors with IFERROR. It swaps the error for a 0 or a blank that looks like a real answer, and the practice file’s checks reject it. Fix the cause, and keep if_not_found for values that are truly missing.

#NAME? with XLOOKUP spelled right usually means your Excel has no XLOOKUP. Excel 2019 and older show it as _xlfn.XLOOKUP in files made in newer versions. Text typed without quotes, such as Not found in the fourth argument, gives the same error.

What is inside the practice file

The colors mean the same thing on every lesson sheet, and Start here has the key:

• Yellow cells: type your formula here.
• Blue cells: the values to look up. Change one to test your formula: its ✓ or ✗ disappears, and comes back when you change the value back.
• Pink rows: an example of the problem, in lessons 2 and 4. Nothing to type there.
• ✓ and ✗: ✓ means right, ✗ means try again. In lessons 2 and 3 the formula must also use every blue cell in its row.

Which Excel versions have XLOOKUP (and Google Sheets)

XLOOKUP works in Excel for Microsoft 365 on Windows, Mac, the web and mobile, and in Excel 2021 and Excel 2024. Microsoft says it is not available in Excel 2016 or Excel 2019: those versions open the file, but the formula shows as _xlfn.XLOOKUP and returns #NAME? once it recalculates.

Google Sheets has had XLOOKUP since 2022. The arguments work the same way under different names, and the lookup range must be a single row or column. Formulas from this guide that multiply or join ranges need ARRAYFORMULA in Sheets, as shown above. The practice file is built and tested for Excel, so its checks need Excel.

Excel in another language translates the name: XLOOKUP is BUSCARX in Spanish, PROCX in Portuguese (Brazil), XVERWEIS in German, RECHERCHEX in French, CERCA.X in Italian and X.ZOEKEN in Dutch. Japanese Excel keeps the English name, XLOOKUP. Where the comma is the decimal separator, Excel usually expects semicolons between arguments, as in =XLOOKUP(I6;$B$6:$B$17;$E$6:$E$17), or =PROCX(I6;$B$6:$B$17;$E$6:$E$17) in Portuguese. Formulas already in the practice file switch by themselves.

Who this practice file is for

It is for anyone who keeps products, orders, prices or stock in Excel and wants more than a one-column lookup: store and operations staff, analysts, students and self-learners. It needs Excel for Microsoft 365, Excel 2021, Excel 2024 or Excel for the web, so it does not work in Excel 2019 or older, and it is not made for Google Sheets. It does not cover VLOOKUP or INDEX MATCH.

Related guides

• Excel formulas for finance: VLOOKUP vs XLOOKUP, INDEX MATCH and what each Excel error means.
• VLOOKUP in 6 minutes (video): for files that must still open in Excel 2019.
• AI prompts for Excel: 20 tested prompts that write and explain formulas.
• Bank reconciliation template: matching two lists of transactions, a bigger lesson 5.
• Excel financial dashboard template: ten designs on one data table.
• Bank statement to Excel: statement data in clean rows you can look up.

Please note: Excel functions and the versions that include them change, and there can be a gap between a change and our update. Check Microsoft’s documentation for your version before you rely on a formula. This guide and the practice file are for learning, not professional advice, and ReconScribe is not responsible for decisions made on them.

XLOOKUP multiple criteria FAQ

Sources

• Microsoft Support, XLOOKUP function
• Microsoft Support, LET function
• Microsoft Support, Excel functions (alphabetical)
• Microsoft Support, Dynamic array formulas and spilled array behavior
• Microsoft Support, How to correct a #SPILL! error
• Microsoft Support, How to correct a #NAME? error
• Microsoft Support, Display or hide zero values
• Google Docs Editors Help, XLOOKUP function
• Google Workspace Updates, Adding more flexibility to functions in Sheets