Complex Question, ISERROR / HLOOKUP / VLOOKUP

[azalexx]

Hello, and thank you in advance for any assistance I may receive. I have used this forum for years (I cannot retrieve my original account) and am so grateful that excel experts spend thier own time to look and solve complete stranger problems.

I am at hour 4 on this and I just cannot make it work - I admit defeat!!!


Facts:

The topic is "Accounts", and what roles are assigned to said accounts. There can be multiple rows for the same unique account ID, but with unique "Roles" assigned to them. I need to determine if each account is assigned the role of "Account Manager"... or not.

My source data has 30K + rows, but this is a small example of the type of data I am working with.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Account ID[/TD]
[TD]Account Name[/TD]
[TD]Role Title[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]IZSdEIAX[/TD]
[TD]Purple Pens[/TD]
[TD]Director[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]IZSdEIAX[/TD]
[TD]Purple Pens[/TD]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]IZSznIAH[/TD]
[TD]Orange Pens[/TD]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]IZSznIAH[/TD]
[TD]Orange Pens[/TD]
[TD]Account Manager[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]IZSznIAH[/TD]
[TD]Orange Pens[/TD]
[TD]Inside Sales[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]IZStnIAH[/TD]
[TD]Blue Pens[/TD]
[TD]Inside Sales[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]IZStnIAH[/TD]
[TD]Blue Pens[/TD]
[TD]Director[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]IZStnIAH[/TD]
[TD]Blue Pens[/TD]
[TD]Account Manager[/TD]
[/TR]
</tbody>[/TABLE]


















My output sheet are Columns A and B with duplicates removed, so that I have only unique Account ID's and Account Names remaining. My goal is to write a TRUE or FALSE statement that will follow this logic:


Match in column A (I tried VLOOKUP and can get that far)
AND
Match unique Account ID
AND
Search in Column C for Account Manager
IF
Account Manager is a value, return TRUE
IF
Account Manager is missing, return FALSE


Output would look like this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Account ID[/TD]
[TD]Account Name[/TD]
[TD]Account Manager[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]IZSdEIAX[/TD]
[TD]Purple Pens[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]IZSznIAH[/TD]
[TD]Orange Pens[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]IZStnIAH[/TD]
[TD]Blue Pens[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]










Does anyone have any ideas on how to accomplish this or can point me to an existing thread where this was covered?

Thank you again for taking the time to look!

 

[barry houdini]

You could use COUNTIFS, so if your data is in Data worksheet then in summary worksheet C2 use this formula copied down

=COUNTIFS(Data!A:A,A2,Data!B:B,B2,Data!C:C,C$1)>0

 

[mrshl9898]

Like this?
=IFERROR(IF(MATCH("Account Manager",INDIRECT(ADDRESS(MATCH(A2,A:A,0),3)&":"&ADDRESS(MATCH(A2,A:A,0)+COUNTIF(A:A,A2)-1,3)),0)>0.1,"TRUE","FALSE"),"FALSE")