## Explanation

Normally, the XLOOKUP function is configured to look for a value in a lookup array that exists on the worksheet. However, when the criteria used to match a value becomes more complex, you can use Boolean logic to create a lookup array on-the-fly composed only of 1s and 0s, then look for the value 1. This is the approach used in this example:

```
=XLOOKUP(1,boolean_array,result_array)
```

In this example, the required criteria is:

*account begins with "x" AND region is "east", and month is NOT April.*

For each of the three separate criteria above, we use a separate logical expression. The first expression uses the LEFT function to test if Account begins with "x":

```
LEFT(B5:B16)="x" // account begins with "x"
```

Because we are checking 12 values, the result is an array with 12 TRUE FALSE values like this:

```
{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}
```

The second expression tests if Region is "east" using the equal to (=) operator:

```
C5:C16="east" // region is east
```

As before, we get another array with twelve TRUE FALSE values:

```
{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}
```

The third expression needs to exclude the month of April. The easiest way to do this is to test for the month of April directly with the MONTH function:

```
MONTH(D5:D16)=4 // month is April
```

Then use the NOT function to to reverse the result:

```
NOT(MONTH(D5:D16)=4) // month is not April
```

which creates an array correctly describing "not April":

```
{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
```

Next, all three arrays are multiplied together, and the math operation coerces the TRUE and FALSE values to 1s and 0s:

```
{1;0;1;1;1;0;0;0;1;1;0;1}*
{0;0;1;1;1;0;1;0;0;1;0;1}*
{0;0;0;1;1;1;1;1;1;1;1;1}
```

In Boolean arithmetic, multiplication works like the logical function AND, so the final result is a single array like this:

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

The formula can now be rewritten like this:

```
=XLOOKUP(1,{0;0;0;1;1;0;0;0;0;1;0;1},B5:E16)
```

With 1 as a lookup value, and default settings for *match_mode* (exact) and *search_mode* (first to last), XLOOKUP matches the first 1 (fourth position) and returns the corresponding row in the result array, which is B8:E8.

### Last match

By setting the optional search mode argument to -1, you can locate the "last match" with the same criteria like this:

```
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16,,,-1)
```