Is there any built in function to find any cells within a range which contain a value between a certain range? for instance using it on the below data and requesting values between 7 and 50 would produce the second column:
| column being searched | return values |
|---|---|
| 7.6 | 7.6 |
| 8.9 | 8.9 |
| 1.78 | 45.8 |
| 73.2 | |
| 45.8 |
is there any built in functionality for this; or am I better off writing a for loop in VBA that loops through my whole data set? could I use the FIND and FILTER function in VBA to eliminate the VBA for loop?
CodePudding user response：
Yes FILTER:
=FILTER(A2:A6,(A2:A6>=7)*(A2:A6<=50))
FILTER is available with Excel 365 or Excel 2021
CodePudding user response：
user simple if condition as shown below =IF(A2<7,"Not in Range",IF(A2<50,A2,"Not in Range"))
