Finding a cell depending on whether another cell is odd/even? I'm lost

[DrSues02]

Alright, here is what I have so far.

I have a sheet with a list of a bunch of teams.

Say the teams go in order like this:

Houston +5 1
Southern Miss -5 2
Clemson -10 3
Virginia 10 4

This is an example of two games...Houston vs Southern Miss and Clemso
vs Virginia.

Each game is numbered all the way down in Column C.


I also have seperate sheets in the same workbook for each team.

I want to put a formula on say Houston's sheet that will look up th
team they are playing from a list of a hundred teams.

The team they are playing is going to be in the cell directly above o
below the cell that the team name is in. In Houston's case, Souther
Miss is directly below...if the situation was reversed, then Houston i
directly above Southern Miss.

How would I go about doing this? The number (which is in Column B) i
entered on the individual team sheets, and I've tried using somethin
like the negative of that number, with no success.

Another idea that I had would be to look up the row that the team nam
was in. If I looked up Houston, and it was in row #50, the team the
would be playing would have to be directly above them.

If they were in row #49, the team they were playing would have to b
directly below them.

This is true because there is always an even amount of teams and i
starts in row #1.

Any ideas on how to set up this in a formula?


Someone else came up with this formula, which bases the the game foun
by finding the negative of the number in Column B.

=INDEX(GAME!A1:A500,MATCH(-INDEX(GAME!B1:B500,MATCH
(A1,GAME!A1:A500,0)),GAME!B1:B500,0)
For example, if I wanted to find out who Houston was playing, thi
formula would try to find a teamname that had the -5.5.

This works perfectly, except it only finds the first instance of tha
number. Since there are over 100 teams, there could be several team
that have -5.5.

I think the best way to go about this is to try to find the row numbe
that the team is entered, then base the result on whether that ro
number is positive or negative.

Here is what I have:

I've tried to modify the formula above by using the odd/even method.
have column D in the original data which just counts down b
row..basically, its the row number.

I want to Match something in Column A, such as "Indiana," find the ro
number which is in Column D, determine if this row odd/even, then giv
me a value in Column A which is in the cell directly BELOW "Indiana" i
Column D is ODD, and give me the value in Column A directly ABOVE i
EVEN.

Here is what I'm attempting so far.

=IF(B16/2<=ROUND(B16/2,0),OFFSET(SUMMARY!A1200,MATCH(B16+1,SUMMARY!A
200,0)-1,-1,1,1),OFFSET(SUMMARY!A1200,MATCH(B16-1,SUMMARY!A1200,0)-1,-1,1,1)

B16 is a cell on each individual team page which gives the row number
I am inputting this formula into another cell on that team page.

The first part of the IF statement should determine if the cell is odd
correct?

However, it is not returning what I wanted.

Any suggestions?

I'm totally lost and could really use some help.
DrSues0

 

[Dave Peterson]

I'd use a helper cell:

If you put Houston in A1 (of sheet1), then this formula:
=MATCH(A1,Sheet2!A:A,0)
will return the row that contains Houston.
(say that this formula is in B1).

Then you could use this in C1:

=INDEX(Sheet2!A:A,B1+(IF(MOD(B1,2)=1,1,-1)))

Depending on how sheet2 is setup (headers/no headers???), you may have to change
to:

=INDEX(Sheet2!A:A,B1+(IF(MOD(B1,2)=1,-1,1)))