# Choose value in a range of data

#### dgavin

##### Active Member
Hello,

Column H3-H30 contain values which are changing continuously.

I need a formula for A1 that will......

find the search column H for a value that is betweem 15-20, if there are more than one in the cloumn then choose the lowest value between 15-20.

Thanks

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Richard Schollar

##### MrExcel MVP
Try:

=SMALL(H3:H30,COUNTIF(H3:H30,"<15")+1)

#### Richard Schollar

##### MrExcel MVP
Unfortunately, the above doesn't restrict to the range 15 to 20. This array formula does (enter using Ctrl+Shift+Enter):

=MIN(IF(H3:H30>=15,IF(H3:H30<=20,H3:H30)))

It will return 0 if there is no value within the specified range).

#### dgavin

##### Active Member
Thanks Richard.

Now I need a formula in B2 which

row number the lowest value between 15-20, is in

#### dgavin

##### Active Member
Hi, this formula is working great

=MIN(IF(H3:H30>=15,IF(H3:H30<=20,H3:H30)))

however how can I adapt it so the range is not restricted to 15-20 but instead the range is anything greater than 15.

Thanks

##### MrExcel MVP
Hi, this formula is working great

=MIN(IF(H3:H30>=15,IF(H3:H30<=20,H3:H30)))

however how can I adapt it so the range is not restricted to 15-20 but instead the range is anything greater than 15.

Thanks
Omit the 2nd condition and change >= to >...

=MIN(IF(H3:H30>15,H3:H30))