Excel XLOOKUP with multiple criteria

Formula 1. Boolean logic

The easiest way to use XLOOKUP with multiple criteria is to apply the Boolean logic. This term simply says things are either true or false. In our XLOOKUP, this means:

Here's the scoop: XLOOKUP hunts for the number 1 while creating a temporary lookup array filled with 0’s (no match) and 1’s (match). First, you check each lookup value against all values in the corresponding lookup array, creating an array of TRUE and FALSE values. And then, you multiply these arrays, turning TRUE and FALSE into 1 and 0 and forming a single lookup array. This final array has 1 for the items meeting all criteria, and XLOOKUP returns the first found match.

For example, to find the supplier of the target item in the target region, the generic formula would be:

=XLOOKUP(1, (Items=Target_Item) * (Regions=Target_Region), Suppliers)

Formula 2. Concatenation

Another approach involves combining all the target values (conditions) into a single lookup_value using the concatenation operator (&). Then, search for that value in the concatenated lookup_array:

For instance, to get the supplier of a particular product based on its name and region, you can use the formula:

=XLOOKUP(Target_Item & Target_Region , Items & Regions, Suppliers)

While this formula shines in simplicity, it might stumble in more complex scenarios, especially when dealing with logical operators or OR logic. Therefore, we recommend the Boolean logic approach for its versatility and reliability.

Multiple criteria XLOOKUP formula: Boolean logic

For our sample dataset, use the following formula to get the supplier based on 3 criteria in cells G4, G5 and G6:

=XLOOKUP(1, (A3:A22=G4) * (B3:B22=G5) * (C3:C22=G6), D3:D22)

Here's a breakdown of how this formula works:

1. Testing individual conditions
   First, the formula compares the target item in cell G4 against all items in the range A3:A22. Similarly, it checks the region in G5 against all regions in B3:B22 and the delivery type in G6 against all delivery services in C3:C22. These comparisons generate three arrays of TRUE and FALSE values like this:

   {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;…} * {FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;… } * {FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;…}

2. Multiplication operation
   The multiplication operation converts TRUE and FALSE values to 1’s and 0’s, respectively, forming a single lookup array. Multiplying by 0 ensures only items meeting all the criteria are represented by 1.

   {0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0}

3. XLOOKUP in action
   This array becomes the lookup_array for XLOOKUP, where it’s searching for the number 1. The 10th value in the array being 1 corresponds to the 10th entry in the dataset. XLOOKUP finds it and returns the 10th value in the return_array (D3:D22), which is "Elijah."

XLOOKUP formula with multiple criteria: concatenation

The same task can be accomplished with this formula:

=XLOOKUP(G4 & G5 & G6, A3:A22 & B3:B22 & C3:C22, D3:D22)

Here's the breakdown for this approach:

1. Concatenating lookup values
   Concatenate all three lookup values (G4, G5, and G6) into a single lookup_value using the concatenation operator. In simple terms, we're creating a combined string to look for: "OrangesWestExpedited".
2. Concatenating lookup arrays
   Concatenate the respective ranges A3:A22, B3:B22, and C3:C22 to create a single lookup_array such as:

   {"ApplesEastStandard";"ApplesEastExpedited";"ApplesEastOvernight";"ApplesWestStandard";"ApplesWestExpedited";"ApplesWestOvernight";"OrangesEastStandard";"OrangesEastExpedited";"OrangesWestStandard";"OrangesWestExpedited"; …}

3. XLOOKUP at your service
   XLOOKUP searches for the concatenated lookup value in the combined lookup array. When it identifies the matching row, it returns the corresponding value from the return array (D3:D22).

XLOOKUP formula for multiple OR criteria in the same column

This formula employs Boolean logic with the addition operation (+) representing OR logic:

In simple terms, when you multiply arrays of TRUE and FALSE values from individual criteria tests, multiplying by 0 ensures that only items meeting all the criteria end up with the number 1 in the final lookup array (AND logic). On the other hand, using the addition operation ensures that items meeting any single criteria are represented by 1 (OR logic). As a result, an XLOOKUP formula with the lookup value set to 1 effectively fetches the value for which any condition is true.

For example, to retrieve the first record in the below dataset where the region is either G4 or I4, the formula is:

=XLOOKUP(1, (B3:B22=G4) + (B3:B22=I4), A3:D22)

XLOOKUP formula for multiple OR criteria in different columns

When dealing with several OR criteria in a single column, the test results are clear-cut - only one test can return TRUE. This simplicity allows adding up the elements of the resulting arrays, yielding a final array with only 0s (none of the criteria is true) and 1s (one of the criteria is true), perfectly aligning with the lookup value 1.

However, when testing multiple columns, things get trickier. The tests are not mutually exclusive as more than one column can meet the criteria, resulting in more than one logical test returning TRUE. Consequently, the final array may contain values greater than 1.

To address this, adjust the formula as follows:

In this adaptation, you add up the intermediate arrays, and then check if the values in the resulting array are greater than 0. This gives us a new array comprised of only TRUE and FALSE values. The double negation (--) changes these TRUEs and FALSEs into 1s and 0s, making sure our lookup value of 1 still does its job smoothly.

For example, to fetch the first record from A3:B22 that has “Yes” in either column C or D, or in both columns, you can use a formula like this:

=XLOOKUP(1, --((C3:C22 = "Yes") + (D3:D22 = "Yes") >0), A3:B22)

Naturally, you are free to adjust the logic as needed to target your desired data.