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?

Reputation: 5

Return an array which spills if value is between

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?

Upvotes: 0

Answers (2)

RRK

Reputation: 27

Use a simple if condition:

=IF(A2<7,"Not in Range",IF(A2<50,A2,"Not in Range"))

Excel demo:

Upvotes: 1

Scott Craner

Reputation: 152660

Yes FILTER:

=FILTER(A2:A6,(A2:A6>=7)*(A2:A6<=50))

FILTER is available with Excel 365 or Excel 2021

Upvotes: 3

Return an array which spills if value is between

Answers (2)

Related Questions