One misconception about the XLOOKUP function in Microsoft Excel is that you can only input a single condition when searching for a value in your dataset, where, in fact, you can look up values based on multiple criteria. What’s more, there are two methods you can use, each differing in complexity and versatility.

If you’re working in the Excel desktop app on a PC or Mac, you need to be using Excel 2021 or later (including Excel for Microsoft 365) to use the XLOOKUP function. It’s also readily available inExcel for the weband on the Excel tablet and mobile apps.

Article image

Microsoft 365 Personal

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.

The XLOOKUP Syntax and Single-Criterion Example

Although the syntax forthe XLOOKUP functioninitially looks quite complicated, it actually follows a logical order:

where

XLOOKUP being used in Excel to return a score based on a selected ID.

So, in the example below, after choosing an ID in thedata validation drop-down listin cell E1, typing:

into cell E2 returns a score of 51.

This is because:

I could have omitted the final two arguments from the above formula, as an exact match and a first-to-last search are the default.

Example 1: Multiple XLOOKUP Criteria With Boolean Logic

How you use the XLOOKUP function changes when you have more than one criterion.

Imagine you own a brand that sells six drinks in six different countries, and you have assigned a manager to each drink in each country.

An Excel table with drinks in column A, countries in column B, managers in column C, and sales in column D.

Your aim is to create a lookup that lets you input a drinkanda country, and returns the manager’s name and the number of sales.

However, as I showed you above, XLOOKUP typically works with only one lookup value, so this initially seems an impossible task. To overcome this apparent limitation, you need to use Boolean logic to create a temporary lookup array.

A lookup table in Excel that is ready to pull a manager’s name and the total sales when a drink and country are selected.

Here’s the formula that will do the trick. I’ve split it into separate lines so it’s easier to read:

Let’s first look at argumentb, the lookup array:

First, the formula checks the Drink column of the T_Managers table for a match against the value in G1, which is Coffee. As a result, it returns the following temporary array:

because the first six values in the Drink column are apple juice (FALSE), the next six drinks are coffee (TRUE), and the remaining rows in the column are other beverages (FALSE). This screenshot visualizes the process going on behind the scenes:

An XLOOKUP formula that uses two criteria in the lookup array.

Then, it checks the Country column of the same table for a match against the value in G2, which is Spain, temporarily returning:

since the fifth and eleventh values match the Country criterion. Here’s what Excel is calculating in the background:

A visualization of a temporary array in Excel that shows all instances of coffee returning TRUE.

Now, because we multiplied the two temporary arrays, this operation turns TRUE values into 1s and FALSE values into 0s:

and

A visualization of a temporary array in Excel that shows all instances of Spain returning TRUE.

This creates a single lookup array, where the first numbers from each array are multiplied, the second numbers from each array are multiplied, and so on.

Now, let’s go back to argumenta, the lookup value:

Here, we’re telling Excel to find the number 1 in the lookup array. As we saw previously, the eleventh value in the combined array is the first that matches this lookup value, so XLOOKUP finds the eleventh value in each column of the return array (argumentc):

In this example, the eleventh value in the Manager column is Olivia, and the eleventh value in the Sales column is 346.

A visualization of a temporary array in Excel that shows all instances of coffee returning 1 and all instances of Spain returning 1.

The final argument (d), which is the text to return if the lookup value isn’t found in the array, doesn’t come into play because there’s a match. In fact, if youuse data validation to create a drop-down listof choices, you’re able to omit the no-match argument altogether.

Also, since you wanted to return an exact match while looking from the top to the bottom, you didn’t need to include argumentseorfin the formula.

A visualization of a temporary array in Excel that multplies the result from two arrays to return either zero or 1.

In the example above, we’ve used the equal (=) symbol to generate the criteria. However, if you’re looking up numerical values, you’re able to also use comparison operators, such as > (greater than). What’s more, instead of creating a multiplication (*) between each temporary array to return a result that matchesallthe criteria, you can use the addition (+) symbol to return the first result that meetsat least oneof the criteria.

Example 2: Multiple XLOOKUP Criteria With Concatenation

Another way to input multiple criteria into your XLOOKUP formula is to concatenate (combine) all the lookup values using the ampersand (&) symbol, and do the same with all the lookup arrays.

Using the same scenario as Example 1, your aim is to return a manager’s name and the sales total when you input a drink and a country.

A visualization of a temporary array in Microsoft Excel that shows the first instance of coffee and Spain returning 1.

Here’s the formula you’re aiming for, split up into separate rows for easier understanding:

First, argumenta, the lookup value, contains two cell references, separated by an ampersand:

In effect, this generates the lookup string of CoffeeSpain, as Coffee and Spain are the values in the referenced cells, and theampersand links them togetherwithout any spaces.

Then, for argumentb, the lookup array, the same logic applies, with each array being joined by the ampersand symbol:

Thus, we’re telling Excel to find the CoffeeSpain string in the DrinkCountry array of the T_Managers table, where the first string is Apple juiceAustralia, the second string is Apple juiceCanada, and so on.

Next, argumentc, the return array, works the same way as inany XLOOKUP formula, telling Excel to return the corresponding values from the Manager and Sales columns.

Finally, if the lookup string doesn’t exist in the T_Managers table, argumentdtells Excel to return the words “No result.”

However, in this case, it does exist, so Excel returns Olivia from the Manager column and 346 from the Sales column.

Since each drink-country combination only appears once in the table, you don’t need to include argumentseorf. Indeed, the XLOOKUP function defaults to an exact match, and it doesn’t matter whether you search from the top or the bottom of the table.

While the concatenation method is a simpler approach to looking up more than one value with XLOOKUP in Excel, the Boolean logic approach is more flexible and can handle more complex scenarios, such as when you use the + symbol to return the first result that matches at least one of the criteria. As a result, which method you choose depends on the type of lookup you want to execute.