How to match or look up more than two same data

[Nelson]

Hi,
I facing one problem about match or look up more than two same data.
Example:

a b z
c d z
e f z
a c z
a b z

if I want the result show "Yes" or just a remark in "z" when "a" and "b"
appear two times or more. "c" and "d" , "e" and "f" , "a" and "c" show "No"
or other remark.

I know Vlookup function is only can look up one data.
Please help me solve this problem.
Thank you

 

[Bernard Liengme]

=IF(COUNTIF(A1:B1,"a")+COUNTIF(A1:B1,"b")=2,"yes","no")
or
=IF(SUMPRODUCT(--(A1:B1={"a";"b"}))=2,"Yes","No")
best wishes

 

[muddan madhu]

Col A has a,c,e,a,a
Col B has b,d,f,c,b

Col C -( helping column ) =A1&B1 and drag it down.

in Col D put this formula =IF(COUNTIF($C$1:$C$5,$C$1:$C$5)

=2,"yes","no") and drag it down

 

[Lars-Åke Aspelin]

Hi,
I facing one problem about match or look up more than two same data.
Example:

a b z
c d z
e f z
a c z
a b z

if I want the result show "Yes" or just a remark in "z" when "a" and "b"
appear two times or more. "c" and "d" , "e" and "f" , "a" and "c" show "No"
or other remark.

I know Vlookup function is only can look up one data.
Please help me solve this problem.
Thank you

Try this forumula in cell C1:

=IF(SUMPRODUCT((A$:A$5=A1)*(B$1:B$5=B1))>1,"Yes","No")

Copy it down for as many rows that you have data.

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

Try this forumula in cell C1:

=IF(SUMPRODUCT((A$:A$5=A1)*(B$1:B$5=B1))>1,"Yes","No")

Copy it down for as many rows that you have data.

Hope this helps / Lars-Åke

Sorry, there was a missing 1 in the formula.

=IF(SUMPRODUCT((A$1:A$5=A1)*(B$1:B$5=B1))>1,"Yes","No")

And change the 5's to suit the number of data rows that you have

/ Lars-Åke

 

[Shane Devenshire]

Hi,

This question is unclear. How can a or b appear 2 or MORE than 2 times?
Your example only shows two columns? Is there more to this question? You
title also says "More than 2"

If the answers you have recieved solve your problem then here is a shorter
version:

=IF(SUM(COUNTIF(A1:B1,{"a","b"}))>1,"Yes","No")

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Nelson]

Thanks all of you.
I already slove the problem.
Thanks for your help.