If array formula?

[spacely]

Hello,

I was trying to write an If-array type formula (shift+ctrl+enter) to get the following results. Given values in columns A, B, and C:

A B C D
33 Needs Attention 34 Needs Attention
34 Routed 34 Needs Attention
34 Needs Attention 36 Not Found
35 Routed 35 Routed

I should get results in D, with one formula dragged down.
If value in C doesn't exist in A, "Not Found" goes into D.
If value in C is somewhere in A, check if value next to the occurrence is ever "Needs Attention", if so, put that in D.
If value in C is somewhere in A, and B next to it is not "Needs Attention", put "Routed" in D.

Maybe...
{=IF(C11=$A$11:$A$13,IF(COUNTIF($B$11:$B$13,"Needs Attention")>0,"Not Routed","Routed"),"Not Found")}

Doesn't work...

Maybe I'm close?

David

 

[Tetra201]

See if the following works for you (this is a regular formula, not an array one):

=IF(COUNTIF(A:A,C1)=0,"Not Found",IF(COUNTIFS(A:A,C1,B:B,"Needs Attention")>0,"Needs Attention","Routed"))

 

[spacely]

Yep, that one works.