Thread: Using IF AND to filter number in a range, works with dragging, but not in an Array
View Single Post
 
Old 11-17-2022, 07:50 AM
mda99das mda99das is offline Windows 7 64bit Office 2013
Novice
  
Join Date: Oct 2022
Posts: 12
mda99das is on a distinguished road
Default Using IF AND to filter number in a range, works with dragging, but not in an Array
I have found a way in Excel (Google Sheets) that filters the ages from 5-9 inclusive. If they fit it , it returns YES otherwise NO

=IF(AND(L1>4, L1<10), "Yes", "No")

I have the ages in column L, and I drag this down to autofill and it works well.

I then used an array formula, that I have used to autofill the column as data is entered

=ArrayFormula( IF(ROW(L:L)=1,"Suitable?", IF(ISBLANK(L:L),"",IF(AND(L:L>4,L:L<10), "Yes", "No"))))

This returns NO all the time
Can someone guide me how to get this to work?

Many thanks
Reply With Quote