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)
{=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)