FIND ( ) with arrays

[DashboardNovice]

My formula is in column F. The expected result is in column D. Here is what I'm trying to do.

Identify cases where the 5 digit project ID has blanks in H:I
For any rows that share that 5 digit project ID (including that first row), return a 1 in column F.

 

[ETAF]

what version of excel do you have

with a helper column
=IF(COUNTIFS($K$5:$K$18,K5,$H$5:$H$18,"",$I$5:$I$18,""),1,"")

But should be able to do with other functions - possibly in later versions of excel - like filter() maybe

how about - no helper needed
=SUMPRODUCT((LEFT($G$5:$G$18,5)=K5)*($H$5:$H$18="")*($I$5:$I$18=""))

 

[DashboardNovice]

what version of excel do you have

with a helper column
=IF(COUNTIFS($K$5:$K$18,K5,$H$5:$H$18,"",$I$5:$I$18,""),1,"")

But should be able to do with other functions - possibly in later versions of excel - like filter() maybe

how about - no helper needed
=SUMPRODUCT((LEFT($G$5:$G$18,5)=K5)*($H$5:$H$18="")*($I$5:$I$18=""))

After I saw your solution for COUNTIFS I remembered I did that a few days ago.

Nice to know that SUMPRODUCT is another way to do this.

This gave me what I was looking for.

Thank you.

 

[Peter Bartholomew]

= LET(
    projCode, TEXTBEFORE(project, ".",,,1),
    selected, TOROW(IF(ISBLANK(state), projCode, NA()),3),
    SIGN(BYROW(projCode=selected, OR))
  )