XLOOKUP with boolean logic

Boolean logic is an elegant way to apply multiple criteria.

In this worksheet we have 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 theMONTH function.

Since we only want dates in March, I simply need to compare this result to the number 3.

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 the color is Red return TRUE.

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.

Forlookup_value, we use one.

Forlookup_array, we use our last helper column.

Forreturn_array, we use the full set of data.

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 thelookup_arrayargument.

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