How to match or look up more than two same data

  • Thread starter Thread starter Nelson
  • Start date Start date
N

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
 
=IF(COUNTIF(A1:B1,"a")+COUNTIF(A1:B1,"b")=2,"yes","no")
or
=IF(SUMPRODUCT(--(A1:B1={"a";"b"}))=2,"Yes","No")
best wishes
 
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
Click to expand...
 
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
Click to expand...


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
 
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
Click to expand...

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
 
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
 
Back
Top