Excel Xlookup Formula With Multiple Criteria: How to Use it

Excel functions can be incredibly powerful, and many are wondering if it’s possible to use Excel XLOOKUP with multiple criteria. In this guide, we’re going to answer this question.

How can I use the Excel XLOOKUP function with multiple criteria?

1. Use concatenation

1. Create a table with multiple columns, like this. We’ll use F2 and G2 as lookup values.
   
2. Click the cell where you want to have your data and type the following: =XLOOKUP(F2&G2, A2:A5&B2:B5, C2:C5)
   
3. Press Enter.

Let’s explain what this formula does:

• F2&G2 – Represents cells that we use for lookup values. Cells are separated by & indicating that we’re looking for multiple values.
• A2:A5&B2:B5 – These are the ranges we’re searching for these two values. In this example, we’re searching columns A and B and rows 2 to 5 in each of these columns.
• C2:C5 – Represents the range that will give us the return data.

Since Blue and Jeans are located on row number 3, the return value is 40.

2. Use Boolean expressions

1. We’ll use the same table from the previous example.
2. Select the cell where you want to show the data and paste the following:
   =XLOOKUP(1,(A2:A5=F2)*(B2:B5=G2), C2:C5)
   
3. Press Enter.

Let’s explain the formula:

• 1 – Indicates that the formula will run if the following statement is true.
• (A2:A5=F2)*(B2:B5=G2) – Searches column A from rows 2 to 5 for a value that matches data from cell F2. It does the same in column B for the G2 value. * indicates that both conditions need to have a match to proceed.
• C2:C5 – Returns the value from C from rows 2 to 5 that matches both values.

How does XLOOKUP work if there are multiple matches?

If there are multiple matches, XLOOKUP will always return the first match.

Read more about this topic

• Solved: Excel Error Initialization of the Data Source Failed
• Excel to Word Document: How to Convert & Copy Data
• Fix: Excel Error Personal.xlsb is Locked for Editing

Can an XLOOKUP return multiple values?

No, this function can only return a single value. If you want to return multiple values, you might want to try using the FILTER function.

Is XLOOKUP better than VLOOKUP?

Yes, XLOOKUP is superior to VLOOKUP in almost every way. To learn about the difference between the two, head to our XLOOKUP vs VLOOKUP guide.

How to apply XLOOKUP on multiple columns?

As shown in the solutions above, you need to use the & or * operator to scan multiple columns.

It’s pretty simple to use XLOOKUP with multiple lookup values as long as you follow the steps from this guide. To learn more about Excel, we have a guide on how to use INDEX and MATCH functions in Excel, so don’t miss it.

Now that you’re familiar with formulas, did you know that you can quickly apply a formula to the entire column in Excel? That’s right, and we have a guide that shows you how to do it.