Exceljet

Quick, clean, and to the point

XLOOKUP with boolean logic

In this video we'll look how to use the XLOOKUP function with Boolean logic.

Boolean logic is an elegant way to apply multiple criteria.

In this worksheet we have some sample order data in a table called "data". 

Let's use the XLOOKUP function to find the first order in March where the color is red.

To make things clear, I'm going to work out the logic in helper columns first. Then, I'll move that logic into the XLOOKUP function, to make an all-in-one formula.

First, we'll test for dates in March with the MONTH function. When we give MONTH the full set of dates in a range, we get a month number for each date in a dynamic array.

Since we only want dates in March, I simply need to compare this result to the number 3. When I update the formula, we get TRUE for all dates in March, and FALSE for all other dates.

Next, I'll test for the color red. This is just a simple expression that compares values in the color column to the string "red". Again, we get a list of TRUE and FALSE values. Only orders where color is Red return TRUE.

Now, since we want Red and March, we need to use AND logic, which means we use multiplication.

When I multiply these two helper columns together, the math operation automatically coerces the TRUE and FALSE values to 1s and 0s.

This will become our lookup array.

Notice this array is dynamic . If I temporarily change a color in March, the results update.

We now have everything we need to configure the XLOOKUP function.

For lookup value, we use one.

For lookup array, we use our last helper column.

For return array, we use the full set of data.

When I enter the formula, we get details for the first order in March where the color is Red.

By default XLOOKUP will find the first match. In other words, the first 1 in the array.

Now to move this into an all in one formula, I'll need to replicate this logic inside the lookup array argument. 

To do this, I need to add parentheses, and use the same expressions we used in columns I and J, multiplied together.

When I enter the formula, we get the same result.

And if I check the LOOKUP array with F9 key, you can we have exactly the same array we have in column K.

I can now delete the helper columns, and everything keeps working. 

Dynamic Array Formulas are available in Office 365 only.

Related shortcuts

Author 
Dave Bruns