Excel XLOOKUP Multiple Criteria Boolean Logic

Formulas & functions · Updated November 22, 2026

Yes, you can use XLOOKUP with multiple criteria by building a boolean (TRUE/FALSE) expression that multiplies comparison tests together, then using that expression as the lookup array. For example, =XLOOKUP(1, (A2:A20="North")*(B2:B20="Widget"), C2:C20) returns the first value in C2:C20 where column A equals “North” and column B equals “Widget”. This works because each comparison returns TRUE or FALSE, and multiplying them converts to 1 or 0, so the result is 1 only when all conditions are met.

How Boolean Logic Works in XLOOKUP

In Excel, TRUE and FALSE are not just labels—they are values that can be used in arithmetic. When you multiply TRUE by TRUE, Excel treats TRUE as 1 and FALSE as 0, so the product is 1 only when every multiplied condition is TRUE. In the formula above, (A2:A20="North") returns an array of TRUE/FALSE for each row, and (B2:B20="Widget") returns another array. Multiplying them element-wise gives an array of 1s and 0s. XLOOKUP then searches for the lookup value 1 in that array and returns the corresponding value from the return array.

This pattern is often called “boolean multiplication” or “AND logic.” If you need OR logic (any condition true), you would use addition + instead of multiplication, but then you must check for a result greater than 0, not exactly 1, because adding two TRUEs gives 2. For example, =XLOOKUP(1, --((A2:A20="North")+(B2:B20="Widget")>0), C2:C20) uses the double negative to coerce TRUE/FALSE to 1/0.

Setting Up Your Data for Multi-Criteria Lookups

Before writing the formula, ensure your data is structured in columns. For instance, suppose you have a sales table with columns: Region (A2:A20), Product (B2:B20), and Sales (C2:C20). You want the sales amount for the first row where Region is “North” and Product is “Widget.” The formula would be:

=XLOOKUP(1, (A2:A20="North")*(B2:B20="Widget"), C2:C20)

This returns the sales value from the first matching row. If no row matches, XLOOKUP returns the #N/A error by default. You can add a custom not-found message: =XLOOKUP(1, (A2:A20="North")*(B2:B20="Widget"), C2:C20, "Not found").

Choosing Between XLOOKUP, INDEX/MATCH, and FILTER

XLOOKUP is not the only way to handle multiple criteria. Here are the main options and when to use each:

  • XLOOKUP with boolean multiplication – Best for returning a single value from the first match. It’s simple and works in Excel 2021 and Microsoft 365.
  • INDEX/MATCH with boolean logic – Older but compatible with all Excel versions. Use =INDEX(C2:C20, MATCH(1, (A2:A20="North")*(B2:B20="Widget"), 0)) entered as an array formula (Ctrl+Shift+Enter in older versions).
  • FILTER function – Best when you need all matching rows, not just the first. For example, =FILTER(C2:C20, (A2:A20="North")*(B2:B20="Widget")) returns an array of all sales for that region/product combination.

If you need the last match, XLOOKUP has a search_mode argument: =XLOOKUP(1, (A2:A20="North")*(B2:B20="Widget"), C2:C20, , , -1) searches from the bottom.

Common Mistakes and How to Fix Them

Several pitfalls trip up users when using boolean logic with XLOOKUP:

  • Forgetting to multiply with parentheses – The comparison must be inside parentheses: (A2:A20="North")*(B2:B20="Widget"). Without parentheses, Excel may interpret the multiplication differently.
  • Using AND() or OR() functions – These functions return a single TRUE/FALSE, not an array, so they don’t work in array context. Use multiplication or addition instead.
  • Mismatched array sizes – All arrays must have the same number of rows. If you use entire columns like A:A, they are the same size, but be careful with ranges of different lengths.
  • Text vs. number mismatches – If your criteria are numbers stored as text, comparisons fail. Use VALUE() or ensure consistent data types.

Using Cell References for Criteria

Instead of hardcoding criteria, you can reference cells. Suppose you put “North” in cell E1 and “Widget” in E2. Then the formula becomes:

=XLOOKUP(1, (A2:A20=$E$1)*(B2:B20=$E$2), C2:C20)

Using absolute references (with $) lets you copy the formula without changing the criteria cells. This makes the lookup dynamic—change E1 or E2 and the result updates automatically.

Handling Errors and Edge Cases

When no match is found, XLOOKUP returns #N/A. You can handle this with the if_not_found argument, as shown earlier. For example, =XLOOKUP(1, (A2:A20="North")*(B2:B20="Widget"), C2:C20, "No sale") displays “No sale” instead of an error.

Another edge case is when multiple criteria include numeric comparisons, like sales greater than 100. You can add that condition: (C2:C20>100) in the multiplication. For example, to find the first product in the North region with sales over 100, use =XLOOKUP(1, (A2:A20="North")*(C2:C20>100), B2:B20).

If your data contains blank cells, comparisons like ="" can be used, but be careful: a blank cell compared to “” returns TRUE, which may cause unexpected matches. Use ISBLANK() if needed.

Frequently Asked Questions

Can I use XLOOKUP with three or more criteria? Yes, just multiply more conditions: (A2:A20="North")*(B2:B20="Widget")*(C2:C20>100). The lookup value remains 1.

Does XLOOKUP work in Excel 2019 or earlier? No, XLOOKUP requires Excel 2021 or Microsoft 365. For older versions, use INDEX/MATCH with array entry (Ctrl+Shift+Enter).

Why does my formula return #VALUE! instead of a result? This often happens when the ranges are not the same size or when you use AND() inside the array. Check that all ranges have the same number of rows and that you’re using multiplication, not AND.

How do I return all matches instead of just the first? Use the FILTER function: =FILTER(C2:C20, (A2:A20="North")*(B2:B20="Widget")). This returns an array that spills down.

Can I use wildcards in XLOOKUP with multiple criteria? XLOOKUP supports wildcards only when the lookup value is text and you set match_mode to 2. But with boolean multiplication, wildcards don’t work directly. Instead, use ISNUMBER(SEARCH("North*", A2:A20)) as a condition.

Performance and Alternatives for Large Data

Boolean multiplication with XLOOKUP is efficient for most datasets, but if you have tens of thousands of rows, it can slow down because Excel calculates the entire array each time. For very large data, consider using a helper column that concatenates criteria, then use a simple XLOOKUP on that column. For example, add a column D with =A2&B2, then use =XLOOKUP("NorthWidget", D2:D20, C2:C20). This reduces the calculation load. Alternatively, use Power Query to filter data before loading it into your workbook.

Another alternative is the SUMPRODUCT function, which can return a sum instead of a lookup: =SUMPRODUCT((A2:A20="North")*(B2:B20="Widget")*C2:C20). This is useful for aggregating matching values.

Remember, XLOOKUP is a lookup function, not a filter. For dynamic arrays, FILTER is often better. Choose based on whether you need a single value or a list of matches.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.