AND Function in Dynamic List

[drewberts]

I'm trying to create 2 x dynamic lists to display items based on no of days to an expiry date. First list shows items which have no. of days to expiry of 0 or less using the following array formula (this works fine):-

{=IFERROR(INDEX($B$1:$B$100,SMALL(IF(R$E$2:$E$100<=0,ROW($E$2:$E$100)),ROW(1:1))),"")}

The 2nd list is to show items which have less than 30 days to go (excluding expired items) - so the criteria is <=30 AND >0 so I've used an AND function as below and I'm just getting the first item in the list - I'm guessing my syntax is wrong somewhere even though its not returning an error.

{=IFERROR(INDEX($B$1:$B$100,SMALL(IF(AND($E$2:$E$100<=30,$E$2:$E$100>0),ROW($E$2:$E$100)),ROW(1:1))),"")}

Col B = Item Name
Col E = Days to Expiry

Can anyone see where I'm going wrong? (Excel 365)

 

[drewberts]

Glad we could help & thanks for the feedback.

Apologies - I must be having a bad day (not really used the FILTER function before).

I can see that for each item that meets the criteria it is added to the list below the previous one without having to copy the formula down. The problem is I only want a maximum of 7 items in the FILTER list (even if there is more) as on my sheet there are other things going on in the cells "beneath" the list which would be overwritten - can you limit the FILTER function to a set number of items?

 

[Fluff]

Yup, that can be done like

=TAKE(FILTER(B2:B100,(E2:E100<=30)*(E2:E100>0)),7)

 

[drewberts]

Yup, that can be done like

=TAKE(FILTER(B2:B100,(E2:E100<=30)*(E2:E100>0)),7)

Great - thanks Fluff. You've been a great help

 

[Fluff]

My pleasure.