XLOOKUP case-sensitive

By itself, the XLOOKUP function is not case-sensitive. A lookup value of "RED" will match "red", "RED", or "Red". We can work around this limitation by constructing a suitable lookup array for XLOOKUP with a logical expression.

Working from the inside out, to give XLOOKUP the ability to match case, we use the EXACT function like this:

EXACT(B5:B15,"RED") // test for "RED"

Since there are 11 values in the range E5:D15, EXACT returns an array with 11 TRUE FALSE results like this:

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

Notice the position of TRUE corresponds to the row where the color is "RED".

For brevity (and to allow the logic to be easily extended with boolean logic), we force the TRUE FALSE values to 1s and 0s with the double negative:

--EXACT(B5:B15,"RED") // convert to 1s and 0s

which yields an array like this:

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

Notice the position of 1 corresponds to the row where the color is "RED". This array is returned directly to the XLOOKUP function as the lookup array argument.

We can now simply the formula to:

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

With a lookup value of 1, XLOOKUP finds the 1 in the 5th position, and returns the 5th row in the return array, B9:D9.

Extending the logic

The structure of the logic can be easily extended. For example, to narrow the match to "RED" in the month of April, you can use a formula like this:

=XLOOKUP(1,EXACT(B5:B15,"RED")*(MONTH(C5:C15)=4),B5:D15)

Here, because each of the two expressions returns an array of TRUE FALSE values, and because these arrays are multiplied together, the math operation coerces the TRUE and FALSE values to 1s and 0s. It is not necessary to use the double-negative.

As the lookup value remains 1, as in the formula above.

First and last match

Both formulas above will return the first match of "RED" in a data set. If you need the last match, you can perform a reverse lookup by setting the search mode argument for XLOOKUP to -1:

=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15,,,-1) // last match

If you need to return results from multiple matches, see the FILTER function.