Excel: Using INDEX array formula to find values in list between a min and max

Question

In excel I have a list of names each with a value associated with them.

I also have a min and max value.

I want to use an array formula to find all the names in the list which are between those min and max values.

To start with I created an array formula that worked to just find all the names where the value was greater than the min value:

This used the following formula in cells D5 (copied down to D10):

    {=INDEX($A$1:$B$6,SMALL(IF($B$1:$B$6>=$E$1,ROW($B$1:$B$6)),ROW(1:1)),1)}

Then I tried to modifiy this formula to find the names with values between the min and max values, in cell F5 as follows:

    {=INDEX($A$1:$B$6,SMALL(IF(AND($B$1:$B$6>=$E$1,$B$1:$B$6<=$E$2),ROW($B$1:$B$6)),ROW(1:1)),1)}

But it doesn't work, see image below:

How can I solve this? Do I need to use VBA?

Answer 1

Change the array formula to:

=INDEX($A$1:$B$6,SMALL(IF(($B$1:$B$6>=$E$1)*($B$1:$B$6<=$E$2),ROW($B$1:$B$6)),ROW(1:1)),1)

Array formulas for some reason do not like the And or the Or funnctions, but you can do them with * for And and + for Or.

This has to do with the fact that TRUE/FALSE is a Boolean. Therefore False = 0 and True = 1, So when the value is between it is 1*1=1 or True. If it is either greater than or less than the extant it would be 1*0=0 or false.