AND in an array function?

V

Vince

Does anyone know how to nest an AND function into an array. Suppose
I'm trying to find the highest column B value that coresponds to a
column A value between 92.05 and 92.25

A B
92 2.89
92.01 7.03
92.02 4.67
92.06 2.76
92.11 5.11
92.13 3.25
92.21 5.58
92.27 7.47
92.29 7.5
92.3 7.04

I've tried this array formula
{=MAX(IF(AND($A$1:$A$10>=92.05,$A$1:$A$10<92.25),($B$1:$B$10)))}
but it returns a 0. If I take out the AND function and only compare
column A values above or below one threshold it works. How do I make
this search work?

Thanks
 
B

Bernard Liengme

You cannot use Boolean function in an array function but you can use Boolean
operators: AND is *, OR is +
SO you could use this (entered, of course, with CTRL+SHIFT+ENTER
=MAX(IF(($A$1:$A$10>=92.05)*($A$1:$A$10<92.25),($B$1:$B$10)))
best wishes
 
Top