lookup question

[Pokerjoe]

How do you do a lookup within a lookup?



Say I have a list of game results, Home Team Name in cell A800, Road
Team Name in B800, score in C800, date in K800.

I want to return the home team's rating on that date.

The rating comes from a series of team logs within in the same sheet.
For example, Everton's name would be in A2, Blackburn's in A42, etc.
Starting in A6 (Blackburn's in A46, etc) would be Everton's own log of
games, (again, Home Team Name in column A6:A30, Road Team Name in
column B, Date in column K), but with a rating in column R.

So I have to first read cell A800, lookup in the appropiate date range
(in Everton's case, k6:k30) and return the value column R from the
same row.

Much, much thanks in advance.

 

[Leo Heuser]

Pokerjoe

If I have understood you correctly, this formula
is one way of doing it:

=SUMPRODUCT((A2:A799=A800)*(K2:K799=K800)*R2:R799)

assuming date is unique for the team and the rating
is numeric.

 

[Pokerjoe]

Mr. Heuser, I haven't explained myself well, I know. Permit a retry.


Each page of this sheet has one team, each row list the results for
one game, 33 games per team in all. For example, (for convenience
here, showing the last four games only, though in fact there are 33
results per team)



row2 EVERTON

row6 Chelsea Everton 0-0 Sat 17-04-2004 .15
row7 Leeds Everton 1-1 Tue 13-04-2004 .10
row8 Everton Tottenh 3-1 Fri 09-04-2004 .07
row9 Newcast Everton 4-2 Sat 03-04-2004 .10


then, page down, to


row42 Blackburn

row46 Blackbrn Leicest 1-0 Sat 17-04-2004 -.27
row47 Fulham Blackbrn 3-4 Mon 12-04-2004 -.29
row48 Blackbrn Leeds 1-2 Sat 10-04-2004 -.30
row49 Liverpl Blackbrn 4-2 Sun 04-04-2004 -.28

and so on for each of 20 teams.



After these individual team logs is a league log, a long list of all
the games played in the league this year, about 330 total. This list
is a repeat of the information in the above lists, but NOT broken down
by individual teams. Here are the last 11 entries (of the 330 total):

row804 Man United Charlton 2-0 Tue 20-04-2004
r0w805 Aston Villa Newcastle 0-0 Sun 18-04-2004
row806 Liverpool Fulham 0-0 Sat 17-04-2004
row807 Portsmouth Manc United 1-0 Sat 17-04-2004
row808 Blackburn Leicester 1-0 Sat 17-04-2004
row809 Wolverhampton Middlesbrough 2-0 Sat 17-04-2004
row810 Manc City Southampton 1-3 Sat 17-04-2004
row811 Charlton Birmingham 1-1 Sat 17-04-2004
row812 Bolton Tottenham 2-0 Sat 17-04-2004
row813 Chelsea Everton 0-0 Sat 17-04-2004
row814 Arsenal Leeds 5-0 Fri 16-04-2004

Spellings, locations and formats are consistent throughout the sheet.

You see that the information from Everton's team log, row 6, is
repeated in the league log in row 813.

The information from Blackburn's team log, row 46, is also repeated in
the league log, in row 808.

This is true for every game for every team.

EXCEPT that the last number in the team logs is NOT in the league log,
because it is a rating of my own construct, based on the team's
previous results (all other data is downloaded).

I need to write a formula that will transfer each team's rating from
their team log, where it is now (in column E as written above), down
to the league log. So that, again for example, row808, showing the
results from Blackburn vs. Leicester, will return from row 46 the -.27
for Blackburn on that date, and whatever rating Leicester had on that
date as well. I have to have the exact rating for the exact date. It
does me no good to compare Manchester United's rating from Feb 12
against Liverpool's rating from Jan 9 for a game they played in
November. What I'm doing, you see, is testing the rating's accuracy
for predicting results in a given game.

I have tried a few things, but have failed, obviously. Any help would
be greatly appreciated. I could of course do it by hand, scrolling up
to the team logs to get the relevant rating and typing it in to the
league log, but I have a sample of 4k games in total, and the
potential tediousness overwhelms me.

I would certainly be willing to restructure the sheet if need be.

Thanks again,
Pokerjoe

 

[Leo Heuser]

Hi again

Here's my second bid

Assumptions:
The team logs start in A2 with home
teams (and headings) in A2:A799,
road teams in B2:B799, scores in C2:C799,
dates in D2799 and ratings in E2:E799.

The long list of all games played
has home teams in A800 and down,
road teams in B800 and down, scores in
C800 and down and dates in D800 and down

In E800 enter this formula:

=SUMPRODUCT(($A$2:$A$799=A800)*($B$2:$B$799=B800)*
($D$2:$D$799=D800)*$E$2:$E$799)

In F800 enter this formula:

=SUMPRODUCT(($A$2:$A$799=B800)*($B$2:$B$799=A800)*
($D$2:$D$799=D800)*$E$2:$E$799)

Select E800:F800 and copy down with the fill
handle (the little square in the lower right corner
of the selection)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Mr. Heuser, I haven't explained myself well, I know. Permit a retry.


Each page of this sheet has one team, each row list the results for
one game, 33 games per team in all. For example, (for convenience
here, showing the last four games only, though in fact there are 33
results per team)



row2 EVERTON

row6 Chelsea Everton 0-0 Sat 17-04-2004 .15
row7 Leeds Everton 1-1 Tue 13-04-2004 .10
row8 Everton Tottenh 3-1 Fri 09-04-2004 .07
row9 Newcast Everton 4-2 Sat 03-04-2004 .10


then, page down, to


row42 Blackburn

row46 Blackbrn Leicest 1-0 Sat 17-04-2004 -.27
row47 Fulham Blackbrn 3-4 Mon 12-04-2004 -.29
row48 Blackbrn Leeds 1-2 Sat 10-04-2004 -.30
row49 Liverpl Blackbrn 4-2 Sun 04-04-2004 -.28

and so on for each of 20 teams.



After these individual team logs is a league log, a long list of all
the games played in the league this year, about 330 total. This list
is a repeat of the information in the above lists, but NOT broken down
by individual teams. Here are the last 11 entries (of the 330 total):

row804 Man United Charlton 2-0 Tue 20-04-2004
r0w805 Aston Villa Newcastle 0-0 Sun 18-04-2004
row806 Liverpool Fulham 0-0 Sat 17-04-2004
row807 Portsmouth Manc United 1-0 Sat 17-04-2004
row808 Blackburn Leicester 1-0 Sat 17-04-2004
row809 Wolverhampton Middlesbrough 2-0 Sat 17-04-2004
row810 Manc City Southampton 1-3 Sat 17-04-2004
row811 Charlton Birmingham 1-1 Sat 17-04-2004
row812 Bolton Tottenham 2-0 Sat 17-04-2004
row813 Chelsea Everton 0-0 Sat 17-04-2004
row814 Arsenal Leeds 5-0 Fri 16-04-2004

Spellings, locations and formats are consistent throughout the sheet.

You see that the information from Everton's team log, row 6, is
repeated in the league log in row 813.

The information from Blackburn's team log, row 46, is also repeated in
the league log, in row 808.

This is true for every game for every team.

EXCEPT that the last number in the team logs is NOT in the league log,
because it is a rating of my own construct, based on the team's
previous results (all other data is downloaded).

I need to write a formula that will transfer each team's rating from
their team log, where it is now (in column E as written above), down
to the league log. So that, again for example, row808, showing the
results from Blackburn vs. Leicester, will return from row 46 the -.27
for Blackburn on that date, and whatever rating Leicester had on that
date as well. I have to have the exact rating for the exact date. It
does me no good to compare Manchester United's rating from Feb 12
against Liverpool's rating from Jan 9 for a game they played in
November. What I'm doing, you see, is testing the rating's accuracy
for predicting results in a given game.

I have tried a few things, but have failed, obviously. Any help would
be greatly appreciated. I could of course do it by hand, scrolling up
to the team logs to get the relevant rating and typing it in to the
league log, but I have a sample of 4k games in total, and the
potential tediousness overwhelms me.

I would certainly be willing to restructure the sheet if need be.

Thanks again,
Pokerjoe

..