Return first cell in range between target values

Question

I want to get the value of the first cell in a range that is between two values. The best I can do is get the first cell that is either smaller or larger than a value. I want the first cell that is BOTH larger than a value AND smaller than an other value. This did not work (because it's only 1 criteria):

{=INDEX(A1:A10,MATCH(TRUE,A1:A10<B1,0))}

note: A1:A10 is the rage of values and B1 is the comparison

I tried:

{=INDEX(A1:A10,MATCH(TRUE,and(A1:A10<B1,A1:A10>B2),0))}

but it did not work.

Thank you for your help.

Answer 1

Try this:

{=INDEX(A1:A10,MATCH(1,(A1:A10<B1)*(A1:A10>B2),0))}

Please note, that it is an array formula, therefore you don't have to write the {}, but when you enter the formula, do this with Ctrl+Shift+Enter.