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)

 

[Fluff]

Excel 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=filter(B1:B100,(E2:E100<=30)*(E2:E100>0))

 

[RoryA]

AND will only return one result - TRUE if every row matches the criteria, FALSE otherwise - not an array of results, one for each row. You need to multiply the arrays instead:

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

 

[drewberts]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=filter(B1:B100,(E2:E100<=30)*(E2:E100>0))

Thanks Fluff - details updated

Your suggestion returns a #VALUE error

 

[drewberts]

AND will only return one result - TRUE if every row matches the criteria, FALSE otherwise - not an array of results, one for each row. You need to multiply the arrays instead:

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

Thanks Rory - your suggestion gives me an excel error (problem with the formula)

 

[Fluff]

The ranges are mismatched, try

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

 

[drewberts]

The ranges are mismatched, try

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

Thanks Fluff - thought of that but I get a #CALC error with that.
The mismatched arrays work with the single criteria INDEX

 

[Fluff]

get a #CALC error with that

That suggests that nothing matches both criteria.

 

[drewberts]

That suggests that nothing matches both criteria.

Ahh, gotcha now - that makes sense. Thanks for your help

 

[Fluff]

Glad we could help & thanks for the feedback.