Matching data in two columns

[Max]

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

 

[Bernard Liengme]

We would need to know how your data is set out.
best wishes

 

[HARSHAWARDHAN. S .SHASTRI]

Hi Max ,

Pl show the sample of your data base so that soln can be given.

H S Shastri


+++++++++++++++++++++++++++++++++++++++++++++++++++

 

[Max]

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

 

[HARSHAWARDHAN. S .SHASTRI]

Hi Max,


Assuming your data is as per follows,

A B c
TEAM A TEAM B 1/1/09

TEAM A TEAM C 5/1/09

TEAM A TEAM C 10/2/09

TEAM B TEAM C 2/6/09

Put following array formula in any relevant cell

{=MAX(IF(A1:A3="TEAM A",IF(B1:B3="TEAM B",C1:C3,0),0))}


H S SHASTRI


+++++++++++++++++++++++++++++++++++++++++++++++++

 

[Max]

Hello Harshawardhan,
Thank you for your help.
What your example does not take into account is that all the teams will also
be playing games as an Away team ie: they will also appear in both columns.
These teams play each other only once during a season.
I did try and post the actual spreadsheet but it appears that is has not
shown up on the forum page.
I could e-mail it to you directly if this is possible.

Thanks again for your help.

Max

 

[HARSHAWARDHAN. S .SHASTRI]

Hi Max,

Seen your data. I have tried in following manner and getting desired
results.

Insert column before HOME column ie new insered column will be A.

put the name of desired teams in cell H1 and I1 and then put following
formula in cell A1 and drag it

=IF(OR(AND(B1=$H$1,E1=$I$1),AND(B1=$I$1,E1=$H$1)),ROW(),"")

in cell J1 put following formula

=VLOOKUP(MAX(A1:A1000),A1:C1000,3,FALSE)

and in cell K1 cell put following formula

=VLOOKUP(MAX(A1:A1000),A11000,4,FALSE)

You will get desired results.


H S Shastri


++++++++++++++++++++++++++++++++++++++++++++++++++++

 

[Max]

Hello Harshawardhan,
Thank you it worked well.

I made a small change, swopped the vlookup to us an INDEX.
Your idea worked well.

Thnak you and best regards

Max

 

[Shane Devenshire]

Hi,

Suppose your raw data start in A1100 with titles on the first row. Enter
one teams name in F1 and one in G1, (Bulls, Blues)
The formula for the two scores of the final game regardless or who is the
home team are:

=INDEX(B2:B100,MAX((A2:A100=F1)*(D2100=G1)*ROW(A2:A100)))

=INDEX(C2:C100,MAX((A2:A100=F1)*(D2100=G1)*ROW(A2:A100)))

These are array formulas so you need to enter them by pressing
Shift+Ctrl+Enter not by pressing Enter.

 

[T. Valko]

Do you have dates associated with the games?

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

 

[Teethless mama]

Try this:

Assuming your data start in A1:A15 header in row 1
Criteria: F1, and G1 (Home and Away)

In F2: =LOOKUP(2,1/(($A$2:$A$15=$F$1)*($D$2:$D$15=$G$1)),B2:B15)

Normally ENTER, copy across to G2

 

[Max]

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?

 

[Max]

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]

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?

 

[Max]

Yes please Biff, in any order. That would be perfect.
This is because the teams only play each other once and not the normal home
and away basis.

Thank you for you response and help.

Max

 

[Max]

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]

Ok, try this...

Assume:

A2:A15 = home team name
B2:B15 = home team score
C2:C15 = away team score
D215 = 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 D215

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.

 

[Max]

Hello Biff,
That is excellent, the formulas work perfectly.

Thank you very much and much appreciated.

Max

 

[T. Valko]

You're welcome. Thanks for the feedback!