Adapting an Array Formula to exclude a row

Hi,

Hopefully this formula will not put too many people off as its large and I need to add to it!!!

Array: =IFERROR(IF($C$5<>"",IF($C$6="Y",INDEX('BR Action Plan'!B:B,SMALL(IF('BR Action Plan'!$D$2:$D$884=$C$5,IF('BR Action Plan'!$I$2:$I$884="Y",ROW('BR Action Plan'!$B$2:$B$884)),""),ROWS(C$8:C8))),INDEX('BR Action Plan'!B:B,SMALL(IF((ISNUMBER(SEARCH($C$5,'BR Action Plan'!$D$2:$D$884)))*('BR Action Plan'!$I$2:$I$884="Y"),ROW('BR Action Plan'!$B$2:$B$884)),ROWS(C$8:C8)))),INDEX('BR Action Plan'!B:B,SMALL(IF(('BR Action Plan'!$I$2:$I$884="Y"),ROW('BR Action Plan'!$B$2:$B$884)),ROWS(C$8:C8)))),"")

In essence it is a sheet which pulls data from one sheet to another based on two selection cells.

It can be broken down into 3 main elements based on two user input cells:

Element 1
If cell C5 is blank, return everything that is on the sheet “BR Action Plan”

Element 2
If cell C5 is complete return the row matches on the sheet “BR Action Plan” that CONTAINS the word (based on column D)

Element 3
If cell C6 contains “Y” then return the row that matches on the sheet “BR Action Plan” that CONTAINS EXCATLY what is in cell C5 (based on column D)

I now (frustratingly) need to add a 4th Element which excludes certain rows. E.g. if cell F5 contains an entry then this needs to be EXCLUDED from the summary sheet.

In the attached example I have “Country 1” in the sheet “Open FP&A BR Actions Log”. Here I need rows 3 and 5 (actions 1.02 and 1.04) excluded as column H contains “Country 1”

Can anyone help at all please?

Hi,

Any takers on this - can it even be done (to exclude a row)