Need to return a value that might be in the row above or the row below...help?

[bfendel]

I play fantasy football, and keep a spreadsheet of scores and such for bonuses, finances, etc.

Each week I manually enter the scores of each matchup, but looking to just copy/paste the table from the website, which calculate for us.

See the attached example. We are currently on week 6 or our season, so I am using week 6 as an example.

The second sheet (Week 6 result) is how the table pastes. It shows the team name in column A, the score in column B. The head-to-head matchups are indicated in every other row in column C.

On the 1st sheet (Scores) you'll see I've used a VLOOKUP formula to return the score for each team in their Pts For column. (As long as people don't go changing their team name!)

But, I'm stuck figuring out how to return the Pts Against value for each team (what their opponent scored.) The row numbers will differ week to week for each team, depending on the matchups.

But basically, what I want is is to find my score, and if it's in an ODD row (which also contains the "Match Up" text in column C, then return the score below mine. If it's in an even row (without the "Match Up" text), then return the score above mine.

Any suggestions on how to do this? I feel like a combination of Index(), Match(), If() should all work, but I can't seem to get it right.

Thanks,
Ben

[rylo]

HI

Replace the Team 1, Team 2 etc with the correct team names (Grass Blasters, JagerBombs) then try

B8: =VLOOKUP(B1,'Week 6 Result'!$A$1:$B$12, 2,FALSE)
C8: =IF(INDEX('Week 6 Result'!$C:$C,MATCH(B$1,'Week 6 Result'!$A:$A,0))="Match Up",INDEX('Week 6 Result'!$B:$B,MATCH(B$1,'Week 6 Result'!$A:$A,0)+1),INDEX('Week 6 Result'!$B:$B,MATCH(B$1,'Week 6 Result'!$A:$A,0)-1))

You should then be able to copy these 2 formulas across.

HTH

rylo

[bfendel]

Thanks Rylo - that worked fine. I feel like there must be a shorter approach to this, but I'm satisfied as long as it works.