0

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?

Kevin Freiges
  • 5
  • 3

2 Answers2

3

Yes FILTER:

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

enter image description here

FILTER is available with Excel 365 or Excel 2021

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • wow that was fast – Kevin Freiges Mar 29 '22 at 18:08
  • How would I use this output as a variable in VBA? would it be an arraylist? or a range? – Kevin Freiges Mar 29 '22 at 18:10
  • 1
    you can use Worksheet.Evaluate to return the values into an array. But I am not a huge fan of using worksheet functions in vba. If you want the array in vba to manipulate, then load the lookup into an array and loop that putting the output in another array. – Scott Craner Mar 29 '22 at 18:13
  • 2
    You might be able to use `.SpillingToRange` as well, if you are trying to read the result of DA formula into an array. – BigBen Mar 29 '22 at 18:16
  • 1
    @BigBen Sir, **WOW**, came to know for the first time `.SpillingToRange` is it the same thing like the `SPILL` in Excel Formulas – Mayukh Bhattacharya Mar 29 '22 at 18:18
  • 1
    @MayukhBhattacharya - I'm not sure the docs have been updated yet. But yeah it's the range that formula spills to. Though apparently it [might be buggy](https://answers.microsoft.com/en-us/msoffice/forum/all/spillingtorange-returning-incorrect-results-am-i/85dce3e6-33b5-4bd1-834b-82bfb6e49ad6), untested. EDIT: oh yeah, [same bug](https://stackoverflow.com/questions/68476260/excel-spillparent-and-spillingtorange-difficulty) reported on SO. Maybe it's been fixed. – BigBen Mar 29 '22 at 18:20
  • 2
    @BigBen Sir, alright, it means a lot from your end, I am going through it. Thanks a bunch again to you & Scott Sir as well for making the community a wonderful place – Mayukh Bhattacharya Mar 29 '22 at 18:22
  • thank you all! this has been very helpful :) – Kevin Freiges Mar 29 '22 at 19:46
  • would i load the lookup into an array by doing 'someArrayList = worksheetfunction.FILTER(A2:A6,(A2:A6>=7)*(A2:A6<=50))'? – Kevin Freiges Mar 29 '22 at 19:55
  • you need to `Worksheet.Range(...)` when refering to the ranges in vba. – Scott Craner Mar 29 '22 at 19:57
1

Use a simple if condition:

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

Excel demo:

Excel demo

leetbacoon
  • 1,111
  • 2
  • 9
  • 32
RRK
  • 17
  • 8