Creating a customer status flag based on multiple columns

[robin_amanda]

Hello, and thanks for even just reading this post. I know this will be a crazy easy fix for someone but my brain has over thought it till I am questioning my own name.

The second tab shows my Actual customer status for New or Rejuv as a Y or N

I need on the first tab to create a customer status flag in columns b and c of 1 based on what their status is Y or N on the second tab. 1 if Y and 0 if N Then in column d in need it to put one only if b and c are flagged. So each row will only have a single flag of 1 fore New, Rejuv, or Existing.

[63falcondude]

Hello and welcome to the forum.

Try this:

B2 =IFERROR(IF(INDEX(Internal!B:B,MATCH($A2,Internal!$A:$A,0))="Y",1,IF(INDEX(Internal!B:B,MATCH($A2,Internal!$A:$A,0))="N",0)),"None")
C2 =IFERROR(IF(INDEX(Internal!C:C,MATCH($A2,Internal!$A:$A,0))="Y",1,IF(INDEX(Internal!C:C,MATCH($A2,Internal!$A:$A,0))="N",0)),"None")
D2 =COUNTIF(B2:C2,1)

Drag all formulas down.

[63falcondude]

Alternatively, you can shorten the B2 and C2 formulas if it is acceptable to show 0 when the order number is not found in the 'Internal' list.

That is,

B2 =COUNTIFS(Internal!$A:$A,$A2,Internal!B:B,"Y")
C2 =COUNTIFS(Internal!$A:$A,$A2,Internal!C:C,"Y")