lookup question #3

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.
0
4/24/2004 3:57:53 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
191 Views

Similar Articles

[PageSpeed] 16

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.

-- 
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Pokerjoe" <pokerjoe43@yahoo.com> skrev i en meddelelse
news:2e279cf9.0404231957.42730160@posting.google.com...
> 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.


0
4/24/2004 5:14:23 AM
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" <leo.heuser@NOSPAMadslhome.dk> wrote in message news:<O5LHNsbKEHA.4032@TK2MSFTNGP10.phx.gbl>...
> 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.
> 
> -- 
> Best Regards
> Leo Heuser
> 
> Followup to newsgroup only please.
> 
> "Pokerjoe" <pokerjoe43@yahoo.com> skrev i en meddelelse
> news:2e279cf9.0404231957.42730160@posting.google.com...
> > 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.
0
4/24/2004 7:40:18 PM
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 D2:D799 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.

"Pokerjoe" <pokerjoe43@yahoo.com> skrev i en meddelelse
news:2e279cf9.0404241140.3bf0cf6a@posting.google.com...
> 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
..


0
4/25/2004 6:46:03 AM
Reply:

Similar Artilces:

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

Inventory by location list question?
I have an excel spreadsheet inventory list. Sheet-1 of the spreadsheet contains everything we would want to know about the inventory (what it is, where the item is located, what it was purchased for, sold for, etc). Example of spreadsheet sheet#1 column (field) name/layout across top. Column A = (Item#), Column B = (Category), Column C = (Description), Column D = (DatePurchased), Column E = (Price Purchased), Column F = (Date Sold), Column G = (Sold For), Column H = (Location of item), Column E = (Price Purchased), and Column F = (Date Sold). Rows-3 thru X following (down and across), c...

Question about IF function
I would like to have every cell in column A that has value of "0.00" be changed to "NO" and every cell that is > 0.00 to have number values changed to "YES". What would the formula be? Thanks in advance. Not sure why you're thinking IF() function. If you just want to display "YES" or "NO", choose Format/Cells/Number/Custom and enter: "YES";;"NO";@ Otherwise In article <12750bd4-e150-4c25-92ca-2b06e540f525@t3g2000yqa.googlegroups.com>, excel_21 <lin.jeff.21@gmail.com> wrote: > I would like...

Concatenation Question
I have a form in which users fill in three fields as part of the form. After this is done I want them to be able to click a button and have the data in those three fields combined with the primary key and this concayenated data to be loaded into another field on the form? How would I do this? Thanks for your help. I can't imagine why you would do such a thing. Each data item should stand alone. To repeat data in a table is not good design. It would be better to concatenate them when you intent to use them for some other purpose. I suggest you rethink your design. But, with that...

Questions about building an offline Exchange 2000 recovery server
Hello all, I know that there is a Microsoft paper that describes the process of creating an offline Exchange 2000 server (trust relationship, etc..) Could you please forward that paper to me? Any help would be greatly appreciated! Best regards, Greg http://support.microsoft.com/?id=326052 On Wed, 27 Apr 2005 15:47:04 -0700, "Greg" <Greg@discussions.microsoft.com> wrote: >Hello all, > >I know that there is a Microsoft paper that describes the process of >creating an offline Exchange 2000 server (trust relationship, etc..) Could >you please forward that ...

question related to Data Vaildation
Dear all, I have two columns. Each column contains a defined value and each value carries some predefined details. I am trying to place a dropdown through data validation, so that i select any value, i should be getting all the details related to that value without any extended space. Like below-: A operations downtime client figure B ratings flow chart technical support If i select A, I want to get the details of A as dropdown, i select B, i want to get the details of B. Kindly help ! Refer the below by Debra Dalgleish ..(you will need to fill the blank cells ...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

Excel Report Builder #3
I'm trying to setup security for users to access and use Excel Report Builder and followed what the user's guide directions: 1. Open the Security Task Setup window (Microsoft Dynamics GP menu >> Tools >>Setup >> System >> Security Tasks) 2. Enter a Task ID, Task Name, Task Description and Category. 3. Select SmartList Builder as the Product and Excel Report Builder Permissions as the Type and Series. 4. Mark the operations that you want to grant access to for the selected user or user class. It grants the user access and appears the capability to build and...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Workflow Manager Question
Trying to create a rule that will copy the street address into a "ship to" address field for leads. Any idea where I might find instructions on how to do this? Thank you if you can help. ...

A Question re Protection: hiding / deleting rows
First: you folks are great! I have workbook with a bunch of sheets. I protect the sheets, but want to let the users hide rows that apply to their needs; I've tried unlocking the rows, bbut once I protect the sheet, I can't hide or delete the rows. Any ideas? Thansk! RN Hi which Excel version are you using. Depending on the Excel version you can for example allow formating rows in the protection dialog -- Regards Frank Kabel Frankfurt, Germany Random NumNuts wrote: > First: you folks are great! > > I have workbook with a bunch of sheets. I protect the sheets, but > w...

Design Question #3
I have a company Acme.com, using Exch03 Enterprise. They have a small remote subsidiary omegasales.com These users vpn in to the main office at acme.Omegasales wants to have their email come into this Exch server, and yet be transparent (appear to send/recieve from omegasales.com) . Aside from the obvious -mx entry with the ISP, any suggestions for the best approach on Exchange? Thanks, KK This is one AD domain and Exchange org., right? If so, all I would do for the user who are using omegasales.com is make their primary email address "user@omegasales.com". also make sure y...

PeekMessage Question
Hello, I have been using the following code in an MFC application and it has worked great. // Check for user cancel if ((nLoops++ % 0x1F) == 0) { // Check for escape key if (::PeekMessage(&msg, m_hWnd, WM_KEYFIRST, WM_KEYLAST, PM_REMOVE)) { if (msg.message == WM_KEYDOWN && msg.wParam == VK_ESCAPE) { m_bAbort = true; return false; } } nLoops = 0; } It is used to detect Escape within a tight loop. I now want to use the same code, but while a modal dialog box is displayed (the code is not part of my dialog box class but is called from t...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

Questions On Access 2007 Runtime...
Hello, I currently have an Access 2007 project that we use for our company’s inventory system. A few months back, we upgraded some users to the full version of office, and now I am attempting to setup some other users with the 2007 Runtime version and have some questions... If the computer that the user is using already has Access 2007 Runtime installed on it, should I still be making an MSI installer package for them to run, or can I simply copy an ADE file to their computer...? I have actually tried both ways but wasn’t sure which was the best practice. When I tried putting an ADE...

Reporting question 08-23-07
I'm trying to figure out how to use the report builder to pull data from my incident base and from some of my custom fields in the incident extension base. Has anyone had any luck doing this? J. Hi Jeremiah, any reason for not using filteredincident view which contains all base incident fields as well as all custom fields. In addition, user security is honoured, dates are converted to local, pickup lists and lookups are expanded to include verbose name. Bar the monstrosity of the view, a win-win approach. Hope this helps -- George Doubinski CRM Certified Professional - Developer...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

Easy question?
Hi, Let's supose that I have the following XML: <?xml version="1.0" encoding="utf-8" ?> <root> <element1 type="1"> <element2>some value</element2> <element2>some other value</element2> </element1> <element1 type="2"> <element2>some value</element2> <element2>some other value</element2> <element2>another value</element2> </element1> <element1 type="1"> <element2>one thing</element2> <element2>anything<...

Question #2
We are a land reseller company implementing Great Plains. We purchase a piece of land and divide it into smaller parts and resell it. At times, after selling the smaller piece of land, it is returned back and we need to resell it again. My question is: How do I implement this in Great Plains. If I consider the land we buy as an item, how do I split it into multiples. The other issue here is that if every land purchased will be a unique item, then our item data is going to grow very fast. Any ideas will be appreciated. Here are a couple of ideas: 1) When you create the Inventory Item...

Money Reports Question
I have Microsoft Money Deluxe OEM Edition Version 11.0 (according to Help/About) I would like to create a report which shows all my accounts with "Today's Blance" and "Ending Balance", rather than the "Accounts Listing" which only displays "Ending Balance". How do I do this? If I can't, will later versions of money enable me to do so?? TIA JS ...

Vendor Lookup
One doing the vendor lookup - one user sees the 'show details' information upon lookup; other user sees the vendor list and needs to clik on the show details - how do you get the show details window to be the default option you see. Thansk! Check for full stops/periods/dots on the window title bar before or after the window name. It is possible to use VBA or modifier to open the details automatically. David Musgrave [MSFT] Escalation Engineer - Microsoft Dynamics GP Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (formerly Microsoft Business Solutions) http://www...

CDockState question
Hi, I used CDockState in a CFrameWnd to temporarily store my toolbar state & restore it later using CFrameWnd::GetDockState() & CFrameWnd::SetDockState(). Somehow when the state is restored, one of my vertical toolbar didn't display correctly, most of the buttons on it are missing. I have to drag it into floating state to make it display all the buttons. When I did this & return it to the vertical docking position, everything is fine. Does anybody know why it is so? Is this a bug with MFC or maybe a problem with graphics card/driver? Thanks, Vincent There is an issue...

Outlook 2000 won't start #3
My Outlook has stopped working. It was working fine, and then all of a sudden, it will not come up. I have uninstalled and reinstalled and it still will not work. When I click on it, I get the the box that says Microsoft Outlook on the screen, but that is where it gets stuck and it never actually loads. This was installed with Office, and all of the other programs that were installed with Office work just fine. What can I do to get this to work? On Wed, 20 Apr 2005 15:00:01 -1000, Ole Lang Signs"" <Ole Lang <Signs@discussions.microsoft.com>> wrote: > My O...