To use XLOOKUP with multiple criteria, the most reliable pattern is to build a temporary lookup array with Boolean logic and ask XLOOKUP to find the value 1. That sounds more technical than it is, but once you see the pattern, it becomes one of the most useful lookup techniques in Excel.
If you have not learned the basic version yet, start with XLOOKUP Function in Excel: Step-by-Step Guide for Beginners. This article assumes you already understand a normal one-column lookup and want to move to a more specific case.
Why a Normal XLOOKUP Is Not Enough
The basic XLOOKUP pattern searches one value in one range:
=XLOOKUP(A2,$E$2:$E$10,$G$2:$G$10)
That works when one lookup key is enough.
But sometimes the correct match depends on several conditions at once, such as:
- item + size + color,
- employee ID + month,
- region + product type,
- department + shift + status.
In those situations, there is no single natural lookup column unless you build one yourself.
The Boolean Logic Pattern
Suppose you want to return the price of a hoodie based on:
- item in
H5 - size in
H6 - color in
H7
and your source data looks like this:
- column
B: item - column
C: size - column
D: color - column
E: price
Use this formula:
=XLOOKUP(1,($B$5:$B$15=H5)*($C$5:$C$15=H6)*($D$5:$D$15=H7),$E$5:$E$15)
This works because each comparison produces TRUE or FALSE, and the multiplication converts those results into 1 and 0.
Only the row where all three conditions are true becomes 1, so XLOOKUP searches for 1 and returns the matching price.
Step by Step
Break the formula into pieces:
($B$5:$B$15=H5)
This checks item names.
($C$5:$C$15=H6)
This checks sizes.
($D$5:$D$15=H7)
This checks colors.
When you multiply those three arrays together, only the row that matches all three criteria returns 1.
Then XLOOKUP does the final step:
=XLOOKUP(1,combined_test,$E$5:$E$15)
That is the key idea. You are not telling XLOOKUP to look for a text value anymore. You are telling it to look for the first row that passed every test.
Why This Method Is Better Than a Helper Column
You can solve this kind of problem with a helper column too. For example, you could create a new column that joins together item, size, and color, then look up the combined text.
That is valid, but the Boolean logic method is often better because:
- it does not require changing the source table,
- it stays inside one formula,
- it scales better when criteria become more complex.
If you need a simpler alternative for a one-off workbook, concatenation can still work:
=XLOOKUP(H5&H6&H7,$B$5:$B$15&$C$5:$C$15&$D$5:$D$15,$E$5:$E$15)
This is easier to read at first glance, but it is less flexible. For more advanced cases, Boolean logic is the safer pattern.
What If You Need INDEX MATCH Instead?
Before XLOOKUP, many users solved this with INDEX and MATCH:
=INDEX($E$5:$E$15,MATCH(1,($B$5:$B$15=H5)*($C$5:$C$15=H6)*($D$5:$D$15=H7),0))
That still works, and older versions of Excel may require it. But if XLOOKUP is available, the XLOOKUP(1,tests,return_range) pattern is usually easier to explain and maintain.
If you need the broader legacy background, INDEX and MATCH Functions Together Again in Excel is still relevant.
Common Problems with Multiple-Criteria XLOOKUP
One of the criteria contains hidden spaces
If one field looks right but does not actually match, the problem may be spacing or imported text noise. That is especially common in item codes and copied data. If that sounds familiar, read How to Use XLOOKUP with Wildcards for Partial Matches and How to Fix Common XLOOKUP Errors in Excel.
The ranges do not have the same size
Your criteria ranges and return range need to cover the same rows. If one range ends at row 14 and another ends at row 15, the result may fail or return the wrong match.
You are expecting more than one result
This formula returns the first row that matches all the conditions. If you need all matching rows, FILTER is usually a better choice than XLOOKUP.
A Practical Rule for Beginners
Use the Boolean logic pattern when:
- you need one answer,
- the answer depends on several fields,
- you want to keep the data table unchanged.
Use concatenation when:
- the workbook is simple,
- readability matters more than flexibility,
- you are comfortable joining fields together.
For the official syntax, Microsoft's XLOOKUP documentation is the primary source. For a worked example with arrays, Exceljet's XLOOKUP with multiple criteria page is a good visual companion.
Verdict
XLOOKUP with multiple criteria looks advanced, but the pattern is consistent once you understand it:
- create one test for each condition,
- multiply the tests together,
- look for
1, - return the result you need.
That is the core idea. Master it once, and many "hard" lookup problems become much easier.