If XLOOKUP already feels like spreadsheet magic, using it with multiple criteria is the upgraded wand. Instead of looking up one value, like an order number or a name, you can tell Excel to match two or more conditions at the same time.

This is useful when one lookup value is not unique enough. Maybe your sales sheet has the same product sold in different regions, or the same employee appears in multiple departments. A regular XLOOKUP may grab the first match it sees. A multiple-criteria XLOOKUP lets you be much more specific.

When to Use XLOOKUP with Multiple Criteria

Use this trick when you need Excel to find a row based on more than one column. For example, imagine this table:

Region	Product	Price
East	Notebook	4.99
West	Notebook	5.49
East	Planner	9.99

If you only search for “Notebook,” Excel has two possible answers. But if you search for Region = East and Product = Notebook, there is only one correct row. Much better. The spreadsheet goblin approves.

The Formula

Here is the basic formula for XLOOKUP with two criteria:

=XLOOKUP(1,(A2:A10=F2)*(B2:B10=G2),C2:C10)

In this example:

• A2:A10 contains the region list.
• F2 contains the region you want to find.
• B2:B10 contains the product list.
• G2 contains the product you want to find.
• C2:C10 contains the value you want returned, such as the price.

The 1 at the beginning is the key. Excel checks each condition and turns TRUE/FALSE results into 1s and 0s. When both conditions are true, the multiplication creates a 1. XLOOKUP searches for that 1 and returns the matching result.

Step-by-Step Example

1. Put your lookup criteria somewhere easy to see. For example, type East in F2 and Notebook in G2.
2. Click the cell where you want the answer to appear.
3. Enter this formula: =XLOOKUP(1,(A2:A10=F2)*(B2:B10=G2),C2:C10)
4. Press Enter.

If the table has East + Notebook in the same row, Excel returns the price from column C. That is the whole spell.

How to Add a Third Criterion

Need three conditions? Add another criteria test and multiply it with the others:

=XLOOKUP(1,(A2:A10=F2)*(B2:B10=G2)*(C2:C10=H2),D2:D10)

This version checks three columns and returns a value from column D. Just make sure every criteria range has the same number of rows. Excel gets cranky when ranges do not line up.

Add a Friendly “Not Found” Message

To avoid a harsh #N/A error, add the optional not-found argument:

=XLOOKUP(1,(A2:A10=F2)*(B2:B10=G2),C2:C10,"No match found")

Now, if Excel cannot find a row matching both criteria, it returns “No match found” instead of an error. This is especially helpful if someone else will use your spreadsheet.

Common Mistakes to Check

• Your ranges are different sizes. A2:A10, B2:B10, and C2:C10 should cover the same rows.
• Extra spaces are hiding in your data. “Notebook” and “Notebook ” are not the same to Excel. Try TRIM if imported data looks suspicious.
• The formula returns the first match. If more than one row matches all criteria, XLOOKUP returns the first one it finds.
• You are using an older Excel version. XLOOKUP is available in Microsoft 365 and newer Excel versions. It is not available in older perpetual versions like Excel 2016.

Quick Recap

To use XLOOKUP with multiple criteria in Excel, use Boolean logic inside the lookup array. The pattern is:

=XLOOKUP(1,(criteria_range1=criteria1)*(criteria_range2=criteria2),return_range)

Once you learn this pattern, you can reuse it for prices, names, dates, departments, inventory, invoices, and all kinds of spreadsheet mischief. Tiny formula, big magic.