In Excel, there's a powerful formula called XLOOKUP, which makes it easy to find exact values in your sheet. And what's surprising? It doesn't look for one value, it can also search using multiple criteria. In this article, we will show you how to use XLOOKUP multiple criteria to find an exact match for your data.

What are XLOOUP Multiple Criteria?

XLOOKUP is an Excel function that allows you to search for specific values within a range or array.

Further, this functionality, xlookup with multiple criteria can also help you find a value in both vertical and horizontal lookup places. This function allows you to find an exact match, an approximate match, or comparable data.

How to perform XLOOKUP with Multiple Criteria?

You can perform XLOOKUP with multiple criteria through concatenating expressions and Boolean expressions.

How to Concatenating the XLOOKUP with multiple conditions

Concatenating XLOOKUP with multiple criteria is very common. In the below example, we'll concatenate all criteria in one lookup value with their associated columns into a single lookup array.

• Now, concatenate the lookup of multiple columns.
• Then you can use the autofill command to fill the remaining rows. Concatenating the lookup columns allows you to look up all the criteria simultaneously.
• Create two columns, one for the lookup value and the other for the XLOOUP formula.
• Then, select a lookup value.
• After that, enter the XLOOKUP formula in the score column.
• Finally, you can combine the XLOOKUP function and concatenated values to get the same result.

How to perform XLOOKUP with multiple conditions using Boolean Expressions

Now we make a boolean expression to ensure the criteria are correct with their corresponding lookup columns.

To use the boolean expressions with XLOOKUP in multiple criteria, follow the steps below:

• Apply boolean logic with the appropriate criteria to the respective columns.
• Then, fill in the remaining empty rows using the auto-fill feature.
• After that, you must follow the same instructions for the Condition 2 column.
• Then, multiply the two columns' boolean arrays using the AND function on the entire column.
• Finally, look up "1" based on the lookup results.
• Alternatively, we can also use the array formula to merge all formulas.

Following the steps above, you can master the XLOOKUP multiple criteria by concatenating and using the boolean logical operators.

What are the Syntax and Uses of XLOOKUP Multiple Criteria?

Excel's XLOOKUP function searches a range or array for a specific value and returns the corresponding value from another column. It can look up vertically and horizontally and do exact (default), approximation (closest), or wildcard (partial) matches.

The syntax of the XLOOKUP function is as follows:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The first 3 arguments are required and the last three are optional.

• Lookup_value [required] - the value to search for.
• Lookup_array [required] - the range or array where to search.
• Return_array [required] - the range or array from which to return values.
• If_not_found [optional] - the value to return if no match is found. If omitted, an #N/A error is returned.
• Match_mode [optional] - the match type to perform:

0 or omitted (default) - exact match. If not found, an #N/A error is returned.

-1 - exact match or next smaller. If an exact match is not found, the next smaller value is returned.

1 - exact match or next larger. If an exact match is not found, the next larger value is returned.

2 - wildcard character match.

• Search_mode [optional] - the direction of search:

1 or omitted (default) - to search from first to last.

-1 - to search in reverse order, from last to first.

2 - binary search on data sorted ascending.

-2 - binary search on data sorted descending.

Microsoft provides binary search for advanced users. It is a special algorithm that determines the position of a lookup value within a sorted array by comparing it to the middle element. A binary search is faster than a regular search, however, it only works on sorted data.

How to use Multiple criteria XLOOKUP with logical operators?

Expanding the horizon of multiple criteria XLOOKUP, allows you to go beyond simple equality checks by including a variety of logical operators. These operators let you test conditions like greater than, less than, and not equal to specific numbers.

For instance, consider the scenario of getting the supplier for the item in G4, the region not matching G5, and a discount greater than G6. The formula to achieve this is as follows:

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

How to use Multiple criteria XLOOKUP approximate match?

The basic XLOOKUP formula can seek an exact or approximate match, controlled by the 5th argument, match_mode. When dealing with multiple conditions, the challenge arises in finding a value that approximately matches one of the criteria.

The solution involves first filtering out entries that don't meet the exact match condition, achieved through the IF or FILTER function. The filtered array is then served to XLOOKUP, prompting an approximate match - you choose between the closest smaller item (match_mode set to -1) or the closest larger one (match_mode set to 1).

In an example scenario with item names in column A, quantities in column B, and discounts in column C, aiming to find the discount for a specific item in cell F4 and a quantity in F5, the formula is constructed as follows:

=XLOOKUP(F5, IF(A3:A22=F4, B3:B22), C3:C22,, -1)

Breaking it down, the inner logic filters items matching F4 and their corresponding quantities:

IF(A3:A22=F4, B3:B22)

This results in an array consisting of quantity numbers for matching items and FALSE for non-matching ones:

{…;FALSE;FALSE;FALSE;20;50;100;150;200;250;FALSE;FALSE;FALSE;…}

With the target quantity of 75 in F5, XLOOKUP with match_mode set to -1, searches for the next smaller item in the above arrayfindsnd 50, and returns the corresponding discount from column C (3%).

Alternatively, you can do filtering using the FILTER function:

=XLOOKUP(F5, FILTER(B3:B22, A3:A22=F4), FILTER(C3:C22, A3:A22=F4),, -1)

In this version, you filter quantities (B3:B22) based on the target item (A3:A22=F4) for the lookup array, and for the return array, you filter discounts (C3:C22) for the same target item.

How to use XLOOKUP with multiple conditions (OR logic)?

Now, let's look at how to use XLOOKUP with OR logic to identify values that satisfy at least one of the conditions

Depending on whether your criteria are in the same or different columns, there are 2 variations of the formula.

XLOOKUP formula for multiple OR criteria in the same column

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

XLOOKUP(1, (lookup_array = lookup_value1) + (lookup_array = lookup_value2) + (…), return_array)

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

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:

XLOOKUP(1, --((lookup_array1 =lookup_value1) + (lookup_array2 =lookup_value2) + (…) > 0), return_array)

In this condition, 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.

How do I use Xlookup Multiple Criteria in Excel for multiple columns?

In Excel, the XLOOKUP function allows you to search a range or an array and returns an item corresponding to the first match found. When using XLOOKUP for multiple columns, you can specify an array as the return value. Here's how you can use XLOOKUP for multiple columns:

Let's say you have the following data:

A	B	C	D
Product	Category	Price	Quantity
Apple	Fruit	$1.00	10
Banana	Fruit	$0.75	15
Carrot	Vegetable	$0.50	20
Broccoli	Vegetable	$0.80	12

In this formula:

• "Banana" is the value you're searching for
• A2:A5 is the range you're searching within.
• B2:D5 is the array you want to return results from, which includes columns B, C, and D.

So, this formula will return an array with values from columns B, C, and D for the row where "Banana" is found.

Keep in mind that XLOOKUP will return the results in an array format. If you want to display these results in separate cells, you can either use the INDEX and MATCH functions or utilize the spill functionality of Excel (available in Excel 365).

For example, to extract the price and quantity into separate cells:

• For Price: =XLOOKUP("Banana", A2:A5, C2:C5)
• For Quantity: =XLOOKUP("Banana", A2:A5, D2:D5)

This will give you the price and quantity of the product "Banana" in separate cells.

What are the advantages of XLOOKUP Multiple Criteria?

The advantages of  XLOOKUP multiple criteria are:

• Easily find specific details: With XLOOKUP, it is easier to find the exact details you are looking for in your data, especially when using more than one condition. This means you can find very specific details.
• Flexibility in using criteria: You can apply as many criteria as you need, Just be sure that lookup arrays are the same size.
• Dynamic arrays: You can use XLOOKUP with dynamic arrays to distribute results across several cells, eliminating the need for traditional Ctrl + Shift + Enter formulae.
• Easy to understand: XLOOKUP formulas are easy to read and understand. This is helpful for you and for others who work on your sheet.

What are the limitations of XLOOKUP Multiple Criteria?

The limitations of XLOOKUP multiple criteria are:

• Unique criteria combinations: You must have a unique set of conditions for your lookup values; otherwise, XLOOKUP will return an error or the first match.
• Consistent array dimensions. The lookup and return arrays must share the same amount of rows or columns. A mismatch in dimensions will cause an error.

Xlookup with Complex Multiple Criteria: combining AND and OR logic

In more complex cases, you might need a combination of AND as well as OR logic. For example, to get the supplier for the item in G4 and the region either in G5 or I5, use this formula:

=XLOOKUP(1, (A3:A22=G4) * ((B3:B22=G5) + (B3:B22=I5)), D3:D22)

Where:

• (A3:A22=G4) checks if the item in the lookup range matches the target item name in cell G4.
• ((B3:B22=G5) + (B3:B22=I5)) implements OR logic by checking if the region is either G5 or I5.
• (A3:A22=G4) * ((B3:B22=G5) + (B3:B22=I5)) implements AND logic for the item name and region.
• D3:D22 returns the corresponding supplier from this range.

The overall formula successfully locates the first match where both the item and region criteria are met, applying AND and OR logic to different criteria.

How XLOOKUP Multiple Criteria is better than VLOOKUP Multiple Criteria?

XLOOKUP is a powerful function that is better than VLOOKUP in many ways. It is more versatile and efficient. Here are the features that make it a better lookup function in Excel:

• Look in any direction: XLOOKUP has no limitations as it can look in any direction either left, right, top, or bottom whereas VLOOKP can only search in the leftmost column.
• Vertical and horizontal lookup: The XLOOKUP function can look up both vertically as well as horizontally.
• Exact match by default: In most situations, you will search for an exact match and XLOOKUP returns exact matches by default. You can XLOOKUP to perform an exact match. Partial match using wildcards. When you just know a portion of the lookup value, a wildcard match is useful.
• Search with multiple criteria: XLOOKUP performs a lookup with multiple criteria.
• Better Performance: VLOOKUP slows down your worksheets since it performs computations on the entire table whereas XLOOKUP only handles the lookup and return arrays on which it relies.

Conclusion

XLOOKUP is a useful tool for reducing the process of looking up data using several criteria. Compared to VLOOKUP, XLOOKUP may be faster and more reliable. Henxe, by reading this blog you will be able to use XLOOKUP Multiple Criteria. 

Frequently Asked Questions

Can you implement Xlookup multiple criteria in Excel?

XLOOKUP formula with multiple OR criteria in the same column

As a result, an XLOOKUP formula with a lookup value of 1 effectively returns the value for which any condition is true. Note. If two or more entries satisfy any of the conditions, the algorithm returns the first found match.

How does Xlookup function when there are several matches?

If your lookup array contains several matches of the lookup value and you want to retrieve all matches, XLOOKUP cannot do so. In this scenario, you can use the FILTER function or a combination of the INDEX, SMALL, and ROW functions, as shown in the tutorial How to Vlookup Multiple Matches in Excel.

Can you use Xlookup on multiple columns?

At first glance, this appears to be a difficult challenge because XLOOKUP only supports one value for lookup_value and lookup_array. How can we set up XLOOKUP to consider values from several columns? The idea is to use Boolean logic to create the required lookup array, and then configure XLOOKUP to look for the number 1.

Can Xlookup return several values?

Use the XLOOKUP function in an array formula to return multiple values or columns. Here's how you can accomplish this: Choose the cells you want to return multiple values or columns. Type the XLOOKUP function, containing the criteria and the array that will be returned.

Why does XLOOKUP perform better than VLOOKUP?

XLOOKUP can search for values to the left and right of the lookup array, whereas VLOOKUP can only seek values to the right of the lookup value column. XLOOKUP allows you to edit text when a valid match is not discovered, whereas VLOOKUP merely shows a #N/A (error indication).

How can I match three columns in Excel?

Ensure that the columns you want to compare are properly aligned and have the same amount of rows. Add a new column to your Excel sheet that will display the comparison results. This formula compares the numbers in columns A, B, and C of the first row.

Read Another Article - What Causes Spill Error Excel and How To Fix