XLOOKUP with logical criteria

XLOOKUP can handle arrays natively, which makes it a very useful function when constructing criteria based on multiple logical expressions.

In the example shown, we are looking for the order number of the first order to Chicago over $250. We are constructing a lookup array using the following expression and boolean logic:

(D5:D14="chicago")*(E5:E14>250)

When this expression is evaluated, we first get two arrays of TRUE FALSE values like this:

{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}*
{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE}

When the two arrays are multiplied by one another, the math operation results in a single array of 1's and 0's like this:

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

We now have the following formula, and you can see why we are using 1 for the lookup value:

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

XLOOKUP matches the 1 in the 8th position and returns the corresponding 8th value from B5:B14, which is 0347.

With a single criteria

As seen above, math operations automatically coerce TRUE and FALSE values to 1's and 0's. Therefore, when using multiple expressions, a lookup value of 1 makes sense. In cases where you have only a single condition, say, "amount > 250", you can look for TRUE instead like this:

=XLOOKUP(TRUE,E5:E14>250,B5:B14)

Alternatively, you can force the TRUE FALSE values to 1's and 0's, and use 1 like this.

=XLOOKUP(1,--(E5:E14>250),B5:B14)

Dynamic Array Formulas are available in Office 365 only.