Lookup value in range, then if that value is something do a calculation.

[Richhall]

Hi


I have a list of football results, i,e

A B C D

Man U 3 0 Bristol
Sheff U 6 1 West Ham
Wolves 1 2 WBA
Leeds 0 0 Arsenal
Man Utd 6 0 West Ham

On another sheet I want to log if a team has won.

A B
Team Wins
Arsenal in here I was going to put something along the lines
of if the team in A3 is in Sheet1 column A then if Sheet 2 Column B >
Sheet2 Column C class then increase the count by 1. I was also then
going to see if Sheet 2 Column D contained Sheet1 column A and if so
see if Sheet2 C was greater than Sheet 2 B and add it?

Therefore, if you could help.

a) What formula would work this out please, I'm getting in a mess with
IF statements trying to look up a range for a single value.

b) Is there an easier way please? Ideally I'd also check for Draws
and losses as well,.

Cheers

Rich

 

[daddylonglegs]

One way would be like this where "Arsenal" is in sheet2!A2 and your results
data is in sheet1. This formula in sheet2!B2

=SUMPRODUCT(--(sheet1!A$2:A$100=A2),--(sheet1!B$2:B$100>sheet1!C$2:C$100))+SUMPRODUCT(--(sheet1!D$2$100=A2),--(sheet1!C$2:C$100>sheet1!B$2:B$100))

 

[Richhall]

Thanks, what does the -- do? WHy doesn't =IF(Sheet1!A$2:A$100=A2,1,0)
bring back 1 or 0 for me?

 

[Richhall]

Thanks for your help, realised how to do it now using VLOOKUP. ta.