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
--
Bernard Liengme
Microsoft Excel MVP
people.stfx.ca/bliengme
email address: remove uppercase characters

"Nelson" wrote in message
...
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

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



On Nov 22, 10:32*pm, Nelson wrote:
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

On Sat, 22 Nov 2008 09:32:00 -0800, Nelson
wrote:

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

On Sat, 22 Nov 2008 18:53:51 GMT, Lars-Åke Aspelin
wrote:

On Sat, 22 Nov 2008 09:32:00 -0800, Nelson
wrote:

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

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

"Nelson" wrote:

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

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