I have database with a number of team scores for a season and I want to compile a results matrix. Within the results sheet I have ‘HomeTeam’, ‘AwayTeam’, ‘HomeScore’ and ‘AwayScore’ defined ranges. I am trying to use an IF formula to look for when Team1 were at home to Team2 and pick up the HomeScore and (separately the AwayScore). This is the formula that I am using: -
=IF(AND(HomeTeam="Team1", AwayTeam="Team2"), HomeResult)
and in the next cell
=IF(AND(HomeTeam="Team1", AwayTeam="Team2"), AwayResult)
All I get back is #VALUE!
Anyone see what I am doing wrong or know a better way of achieving it?
Hallo
propably you after:
=SUMPRODUCT( HomeTeam="Team1" ; AwayTeam="Team2" ; AwayResult )but in practise I would suggest using a “pivot table”:
→Data→→Pivottable→create…
Excellent, that works. Thanks very much.
I haven’t used Pivot Tables before and know nothing about them I am afraid.
If I am reading your question correctly, your return value names (HomeResult and AwayResult) do not match the defined range names which they are supposed to reference (HomeScore and AwayScore, respectively). Change the names in the formula to match the existing defined ranges.
It is working correctly now. I am looking for the match twice through the HomeTeam and AwayTeam then picking up the HomeScore first and the AwayScore second. Thanks again for your help.