You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
Hello Biff,
That is excellent, the formulas work perfectly.

Thank you very much and much appreciated.

Max


"T. Valko" wrote:

Ok, try this...

Assume:

A2:A15 = home team name
B2:B15 = home team score
C2:C15 = away team score
D2:D15 = away team name

F2 = a team name
G2 = a team name

These can be in any order:

Blues.....Bulls
Bulls......Blues

In the formulas** I've used named ranges:

HT (home team) refers to A2:A15
HS (home score) refers to B2:B15
AS (away score) refers to C2:C15
AT (away team) refers to D2:D15

Enter this array formula** in F3:

=SUM((HT=F2)*(AT=G2)*HS)+SUM((AT=F2)*(HT=G2)*AS)

Enter this array formula** in G3:

=SUM((HT=G2)*(AT=F2)*HS)+SUM((AT=G2)*(HT=F2)*AS)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
Hello Biff,
Sorry I did not read the last part of your message.

"Using TM's formula, if F1 = Team1 and G1 = Team 4 the results returned
are
10
and 20. Shouldn't the results be 17 and 15?"
No it's the team names that must be either Team 1 in postion 1 and Team
4
in
position 1 or Team 4 in postion 1 and Team 1 in postion 4. The scores
who
be
as the teams are show with there relevant scores.

I apologise for missing this important part.
Thank you again.

Max

"T. Valko" wrote:

I must be missing something.

Here's my understanding of your data:

Team1.....10.....20.....Team4
Team2.....12.....10.....Team3
Team4.....17.....15.....Team1
Team3.....10.....22.....Team2

You want to lookup the *last* entries for Team1 and Team4 in *any
order*
(home-away or away-home)

I wonder if you could look at the answer I received
from "Teethless Mama". His formula worked the way I liked best.

Using TM's formula, if F1 = Team1 and G1 = Team4 the results returned
are
10
and 20. Shouldn't the results be 17 and 15?

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
Hello Biff,
I apologise i missed your note.
Yes they do have dates that range over a 4 months period with games
being
palyed Fridays and Saturdays, with different times and in different
countries.

Yes the last game will be the last on the list.

I wonder if you could look at the answer I received from "Teethless
Mama".
His formula worked the way I liked best. Could you look at the
answer I
gave
and perhaps help with a formula there. I will copy the answer I gave
to
"Teethless Mama" below.

(Hello Toothless Mama,
Your formula works well.
I would like to make an alteration if possible.
The data in A1:A15 (actually A4:A101) gets calculated into a league
table.
Positions 1, 2, 3, 4 in the league table, go through to the
semi-finals.
1 plays 4 and 2 plays 3.

Lets look at teams placed in 1 and 4.
These would be the teams that would be displayed in F1 and G1 in
your
formula.
The adjustment I would like to make is to link directly F1 and G1 to
the
table.
This would mean that as the table changes when games are played, the
teams
that appear at the 2 places in the table would change. The problem
is
that
they could appear as Bulls(1) and Sharks(4), but in the fixtures
list
the
Bulls only play away to the Sharks. This means they would have to be
switched
around for your formula.
Is it possible to adjust your formula to make provision for these
possibilities? )

My mistake is I assumed everybody knows how a league table would
work.

Thank you an best regards

Max

"T. Valko" wrote:

Do you have dates associated with the games?

Will the last game be the game towards the bottom of the list?

--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
Hello All,
In column A is the list of the Home Teams in Column B is the Home
team
score
in column C is the Away Team score and Column d is the Away Teams

Eg:

HOME HT Score AT Score AWAY
Highlanders 31 33 Brumbies
Force 19 25 Blues
Lions 34 28 Cheetahs
Crusaders 19 13 Chiefs
Hurricanes 22 26 Waratahs
Stormers 15 20 Sharks
Bulls 33 20 Reds
Hurricanes 22 17 Highlanders
Waratahs 11 7 Chiefs
Force 16 10 Cheetahs
Stormers 27 24 Reds
Brumbies 18 16 Crusaders
Bulls 59 26 Blues
Sharks 25 10 Lions

The lists will be for the whole seasons games, so the teams are
not
sorted.

Thank you

Max

"Bernard Liengme" wrote:

We would need to know how your data is set out.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Max" wrote in message
...
Hello,
I need help with finding the last game that two teams played
each
other,
either home or away.
This is to decide who will have the home town advantage in the
semi-finals.
What formula would be able to find the last time two teams
played
each
other
in a season (either home (Team A vs Team B) or away (Team B v
Team
A)),
and
extract the score for the last game for both teams?

Thank you and best regards

Max