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

What do you mean by Excel (Google Sheets) ?
"Arrayformula" is not an Excel function

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Apologies, I thought google used Excel formulas,
I trained on excel and it works on Google Sheets.

So what are you looking for, an XL or a GS solution?
If XL please post a sample sheet ( no pics please)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Its the AND function in an array,

to get it to work

=ArrayFormula( IF(ROW(L:L)=1,"rej", IF(ISBLANK(L:L),"",IF((L:L<10)*(L:L>4), "Yes", "No"))))

so the next part of the question is, the formula outputs yes or no.

If the output is no, I want the words not eligible written in another column.
but that column cant have a formula in it, as when I run the mail merge script, it adds the date the email sent, and if there is already text in that row will skip it and go to the next one down.

So in my case
I have Column
N =email sent
O =Eligibility
P = Helper cell that will look at column O and if the answer is no, it will write not eligible in Column N