LOOKUP/MATCH/OFFSET/INDEX?? Stumped - HELP!!

[jagerplz18]

OK, so I consider myself a decent excel user (for my purposes anyway), this one has me drawing some blanks. I'm creating a spreadsheet for our yearly NFL pool. I have a tab setup (Called "Schedule") that has the entire schedule entered by week with the following columns:

A: Away Team Name
B: Away Team Score
C: Home Team Name
D: Home Team Score

The scores will be inputed weekly, determining the games' winner, and the spreadsheet does some cool things with drop down lists, conditional formatting, etc. to determine which pool players correctly guess the winner of which games. So far I have all that figured out, works great.

Now I have another tab in the spreadsheet (Called "Data") that I use for data collection for various other tabs in the sheet; it has the following columns:


A: Team Name (All 32 Team Names are listed - A2:A33)
B: Week 1 Score
C: Week 1 Opposing Team Score
D: Week 2 Score
E: Week 2 Opposing Team Score
(and so on....through week 17)

I need a formula that will reference the team name in column A, find that team name in the specified range on the "Schedule" tab, and return the value 1 cell to the right of the name (score). The problem is - depending on the week, the team name may not be in the left-hand column (depending if they are home or away team).

 

[jmarsiglio]

Sounds like you should use an IF statement checking whether they are home or away, then using offset. Google how to use offset, as you will learn a lot this way. I am sure if you want more help someone else will post with a different/better way.

And don't forget Cells.Find("Team Name").

 

[barry houdini]

is there anywhere in the first sheet that indicates the week, or is week 1 score just the first instance of that team, week 2 the 2nd instance etc.?

 

[jagerplz18]

No, there is only a single "divider line" (group of 4 merged cells) that indicates the change in the week on the schedule tab. I was planning on specifing the ranges for the formulas individually by week...I know there are easier ways of doing this (probably very easy in VBA - LOL), but I wanted to keep this spreadsheet as user friendly as possilble, and just have all the calculations kinda run unseen in the background.

 

[jagerplz18]

Sounds like you should use an IF statement checking whether they are home or away, then using offset. Google how to use offset, as you will learn a lot this way. I am sure if you want more help someone else will post with a different/better way.

And don't forget Cells.Find("Team Name").

I like the idea of a nested IF or somthing along those lines....as for the Cells.Find option, I'm really trying to avoid an VBA LOL.

 

[jmarsiglio]

Okay, then I would probably consider vlookup. You would just have to use IF statements and change the reference for vlookup. However, you know you can setup VBA macros so that they run automatically if something changes, or on Startup, etc so it will run unnoticed! But if that's not the reason you don't want to use VBA then I apologize

 

[barry houdini]

OK so if you know the first week games will all be in Schedule!A2:D17 then try this formula for week 1 score in B2 copied down

=IFERROR(VLOOKUP(A2,Schedule!A$2:B$17,2,0),IFERROR(VLOOKUP(A2,Schedule!C$2:D$17,2,0),""))

and for opposing team

=IFERROR(VLOOKUP(A2,Schedule!A$2:D$17,4,0),IFERROR(INDEX(Schedule!B$2:B$17,MATCH(A2,Schedule!C$2:C$17,0)),""))

assumes Excel 2007 or later

 

[jagerplz18]

OK so if you know the first week games will all be in Schedule!A2:D17 then try this formula for week 1 score in B2 copied down

=IFERROR(VLOOKUP(A2,Schedule!A$2:B$17,2,0),IFERROR(VLOOKUP(A2,Schedule!C$2:D$17,2,0),""))

and for opposing team

=IFERROR(VLOOKUP(A2,Schedule!A$2:D$17,4,0),IFERROR(INDEX(Schedule!B$2:B$17,MATCH(A2,Schedule!C$2:C$17,0)),""))

assumes Excel 2007 or later

Thank you for the response - I will have to wait until I'm home from work to try this as my work PC is still using 2003 (gotta love an outdated work PC LOL).

 

[jagerplz18]

OK so if you know the first week games will all be in Schedule!A2:D17 then try this formula for week 1 score in B2 copied down

=IFERROR(VLOOKUP(A2,Schedule!A$2:B$17,2,0),IFERROR(VLOOKUP(A2,Schedule!C$2:D$17,2,0),""))

and for opposing team

=IFERROR(VLOOKUP(A2,Schedule!A$2:D$17,4,0),IFERROR(INDEX(Schedule!B$2:B$17,MATCH(A2,Schedule!C$2:C$17,0)),""))

assumes Excel 2007 or later

Anyone have a solution for using this same formula in Excel 2003 - I'm assuming some of the people in our pool will have out dated systems as well......

 

[barry houdini]

OK for earlier versions try these

=IF(ISNA(VLOOKUP(A2,Schedule!A$2:B$17,2,0)),IF(ISNA(VLOOKUP(A2,Schedule!C$2:D$17,2,0)),"",VLOOKUP(A2,Schedule!C$2:D$17,2,0)),VLOOKUP(A2,Schedule!A$2:B$17,2,0))

and

=IF(ISNA(VLOOKUP(A2,Schedule!A$2:D$17,4,0)),IF(ISNA(MATCH(A2,Schedule!C$2:C$17,0)),"",INDEX(Schedule!B$2:B$17,MATCH(A2,Schedule!C$2:C$17,0))),VLOOKUP(A2,Schedule!A$2:D$17,4,0))