Array formula to omit data if criteria met !

  • Hi All


    Attached is a sample workbook, where I am trying to educate myself !


    The worksheet "Charts" lists and filters supplier details.
    I am trying to implement a further filter in the array formula below:
    The filter should omit any records that have the same details as selected in "Charts(B3) and listed in "Store Details.column(D)"


    i.e. Sub Suppliers 5 and 6 are located in the South West, so they should not be listed.


    {=IFERROR(INDEX('Store Details'!$C$3:$C$15,SMALL(IF('Store Details'!$F$3:$F$15=B$5,ROW('Store Details'!$C$3:$C$15)-ROW('Store Details'!E$3)+1),ROWS($B$6:B6))),"")}


    Thank you for any help.

  • You can add another nested IF() statement:


    =IFERROR(INDEX('Store Details'!$C$3:$C$15,SMALL(IF('Store Details'!$F$3:$F$15=B$5,IF('Store Details'!$D$3:$D$15<>$B$3,ROW('Store Details'!$C$3:$C$15)-ROW('Store Details'!E$3)+1)),ROWS($B$6:B6))),"")

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!