nested if or vlookup help

[Emac]

I am not too swift with database functions so I am trying to do this problem with nested if, match or vlookups.

For example, I am trying to create a NHL schedule for fantasy teams.

on the vertical column (A) I have teams...horizontally across row 1 I have dates and for my table I have teams and dates and opponents.

My goal is to either start with the date and then look up the teams that play on that date....run a match with the players team and then determine the opponent if any.

I just need a little help with nested statements...I will continue to use the search function here, but if anyone can give me a leg up, I would appreciate it.

Thanks!

 

[Andrew Fergus]

Hi

Is this what you want?

NHL.xls
	A	B	C	D	E	F	G
1	Draw	1/01/2006	2/02/2006	3/03/2006	4/04/2006	5/05/2006
2	Team1	Bye	Team5	Team4	Team3	Team2
3	Team2	Team5	Team3	Bye	Team4	Team1
4	Team3	Team4	Team2	Team5	Team1	Bye
5	Team4	Team3	Bye	Team1	Team2	Team5
6	Team5	Team2	Team1	Team3	Bye	Team4
7
8
9	Opponents		Week
10	Player	Team	1/01/2006	2/02/2006	3/03/2006	4/04/2006	5/05/2006
11	Andy	Team3	Team4	Team2	Team5	Team1	Bye
12	Biff	Team5	Team2	Team1	Team3	Bye	Team4
13	Jo	Team1	Bye	Team5	Team4	Team3	Team2
Sheet1

Andrew

 

[Emac]

Dang, looks reasonable...I will try that out...thank you Andrew!!!!

 

[Andrew Fergus]

You're welcome. FYI the formula in cell C11 is designed to be copied and pasted either down or across.
Cheers, Andrew

 

[Aladin Akyurek]

You're welcome. FYI the formula in cell C11 is designed to be copied and pasted either down or across.
Cheers, Andrew

Why

INDEX(MATCH(C$10,$B$1:$F$1),1)

in

=VLOOKUP($B11,$A$2:$F$6,1+INDEX(MATCH(C$10,$B$1:$F$1),1)) ?

 

[Andrew Fergus]

Hi Aladin
My bad. That should be :
=VLOOKUP($B11,$A$2:$F$6,1+MATCH(C$10,$B$1:$F$1))
Andrew