Array Help - World cup pool

[Guest]

Hi. I have a possible array question, if in case an array is the most
appropriate.

I have a persons team selections that will proceed to second round in
sheet1( A1,B1,C1,D1). If any of the persons team selection matches my
worksheet with my games stats(sheet2) than give them 10 pts.
Format of the games stats is:

Second round
team vs. team
M1 Q1
M2 Q2
M3 Q3

So if any of the persons selection in Sheet1(a1,b1,c1,d1) matches [M1:M3 OR
Q1:Q3] (sheet2) than give them 10 pts.

Thanks for the help
Chris.

 

[Biff]

Hi!

Try this:

Do you mean to calculate 10 points FOR EACH MATCH?

=SUMPRODUCT(--(ISNUMBER(MATCH(A11,M1:M3,0))+ISNUMBER(MATCH(A11,Q1:Q3,0))))*10

Biff

 

[Guest]

Sweet. That works.

Thanks,
Chris

Go Germany!

Hi!

Try this:

Do you mean to calculate 10 points FOR EACH MATCH?

=SUMPRODUCT(--(ISNUMBER(MATCH(A11,M1:M3,0))+ISNUMBER(MATCH(A11,Q1:Q3,0))))*10

Biff

Hi. I have a possible array question, if in case an array is the most
appropriate.

I have a persons team selections that will proceed to second round in
sheet1( A1,B1,C1,D1). If any of the persons team selection matches my
worksheet with my games stats(sheet2) than give them 10 pts.
Format of the games stats is:

Second round
team vs. team
M1 Q1
M2 Q2
M3 Q3

So if any of the persons selection in Sheet1(a1,b1,c1,d1) matches [M1:M3
OR
Q1:Q3] (sheet2) than give them 10 pts.

Thanks for the help
Chris.

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Sweet. That works.

Thanks,
Chris

Go Germany!

Hi!

Try this:

Do you mean to calculate 10 points FOR EACH MATCH?

=SUMPRODUCT(--(ISNUMBER(MATCH(A11,M1:M3,0))+ISNUMBER(MATCH(A11,Q1:Q3,0))))*10

Biff

Hi. I have a possible array question, if in case an array is the most
appropriate.

I have a persons team selections that will proceed to second round in
sheet1( A1,B1,C1,D1). If any of the persons team selection matches my
worksheet with my games stats(sheet2) than give them 10 pts.
Format of the games stats is:

Second round
team vs. team
M1 Q1
M2 Q2
M3 Q3

So if any of the persons selection in Sheet1(a1,b1,c1,d1) matches
[M1:M3
OR
Q1:Q3] (sheet2) than give them 10 pts.

Thanks for the help
Chris.