Check against multiple rows (using VLOOKUP/arrays?)

[Richard JIBS]

Hello,

I have a list of contacts with their company and job role fields in Sheet1. There could be multiple contacts per company – as many as 50 – and if their role is CEO or HR they will have an 'x' in the corresponding column.

[TABLE="class: grid, width: 80%, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Company[/TD]
[TD]CEO[/TD]
[TD]HR[/TD]
[/TR]
[TR]
[TD]Barry[/TD]
[TD]AAA bank[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]AAA bank[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD]AAA bank[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]BBB consultancy[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Angela[/TD]
[TD]CCC logistics[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]CCC logistics[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In Sheet2 I have a list of all companies that appear in Sheet1 eg:

[TABLE="class: grid, width: 80%, align: left"]
<tbody>[TR]
[TD]Company[/TD]
[TD]CEO check[/TD]
[TD]HR check[/TD]
[/TR]
[TR]
[TD]AAA bank[/TD]
[TD]YES[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]BBB consultancy[/TD]
[TD]NO[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]CCC logistics[/TD]
[TD]YES[/TD]
[TD]NO[/TD]
[/TR]
</tbody>[/TABLE]

What I need in Sheet2 is to check if we have at least one CEO contact listed for that company in Sheet1 and at least one HR contact listed for that company in Sheet1. The result should be something like the table above where the cell indicates YES/NO depending on the result of the check.

I would appreciate some help in how to achieve this.

Thanks,

Richard

 

[ghrain22]

=COUNTIFS($B$2:$B$7,$F$2,$C$2:$C$7,"x")>0

Assuming your data is spanning from A1:D7 And Cell F2 is part of your second table containing the Company Name

 

[Richard JIBS]

Hi,

Thanks a lot. I've just spent a few minutes testing it in my spreadsheet and it seems to do exactly what I want.

Richard