Table of Contents

SYNTAX

To utilize the XLOOKUP function effectively, understanding its syntax and parameters is essential. The syntax for XLOOKUP in Excel is as follows:
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

• Lookup_value: The item you are searching for.
• Lookup_array: The cell range where the search will be conducted.
• Return_array: The cell range from which the result will be retrieved.
• [if_not_found]: The value returned if no match is found. If omitted, unmatched searches will return #N/A.
• [match_mode]: Specifies the type of match (exact or approximate). This is optional, with a default setting of 0 (exact match).
  • 0 – Exact match. If no match is found, it returns #N/A.
  • -1 – Exact match or the next smaller item.
  • 1 – Exact match or the next larger item.
  • 2 – A wildcard match where *, ?, and ~ have special meanings.
• [search_mode]: Determines the search direction (left to right or right to left). This is optional, with a default of 1 (left to right).
  • 1 – Search starts from the first item.
  • -1 – Reverse search starting from the last item.
  • 2 – Binary search requiring the lookup_array to be sorted in ascending order. If unsorted, it may yield incorrect results.
  • -2 – Binary search requiring the lookup_array to be sorted in descending order. If unsorted, it may yield incorrect results.

Now that you're acquainted with the XLOOKUP syntax, let's delve into using XLOOKUP with multiple criteria.

• Method 1 – Boolean
• Method 2 – Concatenate

Be sure to download this Excel Workbook to follow along and learn how to use XLOOKUP with multiple criteria:

download excel workbookXLOOKUP-with-multiple-criteria.xlsx

METHOD 1 – BOOLEAN

XLOOKUP stands out due to its capability to handle multiple criteria simultaneously. The most effective approach to achieve this is by employing XLOOKUP with multiple criteria using Boolean logic. Despite the term sounding complex, it essentially deals with a simple condition – either true or false.

In this example, we aim to find the price of a product that meets three conditions:

• Product Category is Pants
• Size is L
• Color is Blue

Let's break down the formula step by step!

STEP 1: Input the first argument – Lookup_value. Here, our lookup value is 1. This is because test arrays will only return 1 when all criteria are satisfied.

=XLOOKUP(1,

STEP 2: Input the second argument – Lookup_array. Here, we're applying XLOOKUP with multiple criteria, which we'll refer to as our test arrays.

• First, the product category must be Pants. We search for "Pants" in the entire array A2:A13.
• Second, the size should be L. We search for "L" in the entire array B2:B13.
• Finally, the color should be Blue. We search for "Blue" in the entire array C2:C13.

=XLOOKUP(1,(A2:A13=G1)*(B2:B13=G2)*(C2:C13=G3),

STEP 3: Input the third argument – Return_array. Here, we select the entire array containing all the prices, i.e., D2:D13.

=XLOOKUP(1,(A2:A13=G1)*(B2:B13=G2)*(C2:C13=G3),D2:D13)

We are constructing a set of conditions to meet in our search. We aim for an exact match by using the “=” operator.

=XLOOKUP(1,(A2:A13=G1)*(B2:B13=G2)*(C2:C13=G3),D2:D13)

The test arrays will yield TRUE or FALSE as outcomes, where TRUE signifies that the condition is met and FALSE indicates that it is not.

=XLOOKUP(1,{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}*{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}*{FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE},D2:D13)

The multiplication operator converts TRUE and FALSE to 1s and 0s.

=XLOOKUP(1,{0;0;0;0;0;0;1;0;0;0;0;0},D2:D13)

We can also view this in a more straightforward manner.

Cell G1 contains the product category to be searched in the array A2:A13. The function will search for “Pants” in each row of the selected array. It will return the value 1 for every row where a match is found, and 0 for no matches.

Similarly, you input arguments to search for the required Size (in cell G2) and Color (in cell G3) in the corresponding columns B2:B13 and C2:C13.

Finally, we multiply each argument using the “*” operator. This ensures that only the row returning a value of 1 meets all the specified criteria.

In the result, you can see that only Row 7 has TRUE matches in all three columns (Lookup arrays), hence it returns the value 1. When multiplying, only Row 7 will return the value 1, as all others will have at least one 0 in their formula, resulting in 0 as well.

METHOD 2 – CONCATENATION

When using Boolean logic, you test different criteria and use TRUE or FALSE results to extract the desired value. Alternatively, you can combine or join your lookup values and the lookup arrays to search for multiple criteria simultaneously.

You can use the Ampersand (&) sign for concatenation. The & sign acts as a concatenation operator and joins texts from different cells.

Click here to discover three different ways to concatenate in Excel!

Let's use the same example to see how to use the & sign and make XLOOKUP work with multiple criteria.

STEP 1: Input the first argument – Lookup_value. Here, our lookup value is the concatenation of the three conditions in cells G1, G2, and G3.

=XLOOKUP(G1&G2&G3,

We have combined all the criteria we want to look up. Our three criteria are in cells G1, G2, and G3, namely Product type, size, and color. Using the “&” operator, we have joined the cells to look up these values simultaneously.

STEP 2: Input the second argument – Lookup_array.

=XLOOKUP(G1&G2&G3,A2:A13&B2:B13&C2:C13,

We have similarly combined the corresponding lookup arrays for Product Category, Size, and Color using the “&” operator. Ensure the sequence of lookup values matches the corresponding lookup arrays.

STEP 3: Input the third argument – Return_array. Finally, we select the Price column D2:D13 as we want to extract the price of the product.

=XLOOKUP(G1&G2&G3,A2:A13&B2:B13&C2:C13,D2:D13)

Using the & sign, you can easily apply XLOOKUP with multiple criteria and achieve the desired result.

Conclusion

You can use either Boolean logic or the & sign to apply XLOOKUP with multiple criteria in Excel. The concatenation method might be simpler, but Boolean logic offers greater flexibility and power. Choose the method that best fits your needs and application.

Please Note – XLOOKUP is exclusively available for Microsoft 365 users. Ensure you upgrade your account to utilize XLOOKUP with multiple criteria.

Click here to explore how to use XLOOKUP in Excel through 11 different examples and applications.

The above is the detailed content of 2 Best Ways on How to Use XLOOKUP with Multiple Criteria. For more information, please follow other related articles on the PHP Chinese website!