lookup & match?

[blackduck]

Hi,

Please help me, i'm not very good with excel. My scenario is I have 3 columns of names. I want to look at the first name column (which is my source of truth) and see if the name exists in the other two columns. If the name does exist in all three columns, I want to return a "Y" value in a new column. Issue is the columns are in different orders (names are not on the same lines), so i need a formula that looks at the entire column 2 then entire column 3 and the works out if there is a match in both then return the value.... help?

[TABLE="width: 500"]
<tbody>[TR]
[TD]NAME1[/TD]
[TD]NAME2[/TD]
[TD]NAME3[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]sally[/TD]
[TD]Peter[/TD]
[TD]John[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]sally[/TD]
[TD]Alex[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD][/TD]
[TD]sally[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

Maybe

=IF(COUNTIF(B:B,A2)*COUNTIF(C:C,A2),"Y","N")

 

[Legacy 143009]

Maybe

=IF(COUNTIF(B:B,A2)*COUNTIF(C:C,A2),"Y","N")

A slightly different one inspred from above

=IF(COUNTIF($B:$C,$A2)>1,"Y","N")

 

[blackduck]

Thank you both so much!!!! works perfectly!

 

[Tetra201]

You are welcome.

Please note that the formula from Post # 2 would work even if you have duplicate names in a particular column.

 

[Legacy 143009]

You are welcome.

Please note that the formula from Post # 2 would work even if you have duplicate names in a particular column.

Ahh, unfortunately he is right. My formula will give a false positive if you have dublicate names in Column B and no name in Column C.