Nested IF and VLookup with Data Validation

[Jinthesensei]

I'm having issues with this formula set up.

Week	Data Validation List 1-18
Team	Data Validation List of Teams	VS	Long IF/VLookUp Formula

So in A2, I have a drop down to select the week number, and in B2, I have a drop down to select a team.
In B4, I have this formula. The idea is to being able to select the week and team and see who they play.

=IF(B1=1,VLOOKUP(B2,Sheet2!B:U,3,0),IF(B1=2,VLOOKUP(B2,Sheet2!B:U,4,0),IF(B1=3,VLOOKUP(B2,Sheet2!B:U,5,0),IF(B1=4,VLOOKUP(B2,Sheet2!B:U,6,0),IF(B1=5,VLOOKUP(B2,Sheet2!B:U,7,0),IF(B1=6,VLOOKUP(B2,Sheet2!B:U,8,0),IF(B1=7,VLOOKUP(B2,Sheet2!B:U,9,0),IF(B1=8,VLOOKUP(B2,Sheet2!B:U,10,0),IF(B9=1,VLOOKUP(B2,Sheet2!B:U,11,0),IF(B1=10,VLOOKUP(B2,Sheet2!B:U,12,0),IF(B1=11,VLOOKUP(B2,Sheet2!B:U,13,0),IF(B1=12,VLOOKUP(B2,Sheet2!B:U,14,0),IF(B1=13,VLOOKUP(B2,Sheet2!B:U,15,0),IF(B1=14,VLOOKUP(B2,Sheet2!B:U,16,0),IF(B1=15,VLOOKUP(B2,Sheet2!B:U,17,0),IF(B1=16,VLOOKUP(B2,Sheet2!B:U,18,0),IF(B1=17,VLOOKUP(B2,Sheet2!B:U,19,0),IF(B1=18,VLOOKUP(B2,Sheet2!B:U,20,0),"")))))))))))))))))

I either get the missing parenthesis error or too many arguments error.

What am I missing?

 

[Jinthesensei]

I'm having issues with this formula set up.

Week	Data Validation List 1-18
Team	Data Validation List of Teams	VS	Long IF/VLookUp Formula

So in A2, I have a drop down to select the week number, and in B2, I have a drop down to select a team.
In B4, I have this formula. The idea is to being able to select the week and team and see who they play.

=IF(B1=1,VLOOKUP(B2,Sheet2!B:U,3,0),IF(B1=2,VLOOKUP(B2,Sheet2!B:U,4,0),IF(B1=3,VLOOKUP(B2,Sheet2!B:U,5,0),IF(B1=4,VLOOKUP(B2,Sheet2!B:U,6,0),IF(B1=5,VLOOKUP(B2,Sheet2!B:U,7,0),IF(B1=6,VLOOKUP(B2,Sheet2!B:U,8,0),IF(B1=7,VLOOKUP(B2,Sheet2!B:U,9,0),IF(B1=8,VLOOKUP(B2,Sheet2!B:U,10,0),IF(B9=1,VLOOKUP(B2,Sheet2!B:U,11,0),IF(B1=10,VLOOKUP(B2,Sheet2!B:U,12,0),IF(B1=11,VLOOKUP(B2,Sheet2!B:U,13,0),IF(B1=12,VLOOKUP(B2,Sheet2!B:U,14,0),IF(B1=13,VLOOKUP(B2,Sheet2!B:U,15,0),IF(B1=14,VLOOKUP(B2,Sheet2!B:U,16,0),IF(B1=15,VLOOKUP(B2,Sheet2!B:U,17,0),IF(B1=16,VLOOKUP(B2,Sheet2!B:U,18,0),IF(B1=17,VLOOKUP(B2,Sheet2!B:U,19,0),IF(B1=18,VLOOKUP(B2,Sheet2!B:U,20,0),"")))))))))))))))))

I either get the missing parenthesis error or too many arguments error.

What am I missing?

Corrected one of the IF statements, still not working.

=IF(B1=1,VLOOKUP(B2,Sheet2!B:U,3,0),IF(B1=2,VLOOKUP(B2,Sheet2!B:U,4,0),IF(B1=3,VLOOKUP(B2,Sheet2!B:U,5,0),IF(B1=4,VLOOKUP(B2,Sheet2!B:U,6,0),IF(B1=5,VLOOKUP(B2,Sheet2!B:U,7,0),IF(B1=6,VLOOKUP(B2,Sheet2!B:U,8,0),IF(B1=7,VLOOKUP(B2,Sheet2!B:U,9,0),IF(B1=8,VLOOKUP(B2,Sheet2!B:U,10,0),IF(B1=9,VLOOKUP(B2,Sheet2!B:U,11,0),IF(B1=10,VLOOKUP(B2,Sheet2!B:U,12,0),IF(B1=11,VLOOKUP(B2,Sheet2!B:U,13,0),IF(B1=12,VLOOKUP(B2,Sheet2!B:U,14,0),IF(B1=13,VLOOKUP(B2,Sheet2!B:U,15,0),IF(B1=14,VLOOKUP(B2,Sheet2!B:U,16,0),IF(B1=15,VLOOKUP(B2,Sheet2!B:U,17,0),IF(B1=16,VLOOKUP(B2,Sheet2!B:U,18,0),IF(B1=17,VLOOKUP(B2,Sheet2!B:U,19,0),IF(B1=18,VLOOKUP(B2,Sheet2!B:U,20,0),"")))))))))))))))))

 

[StephenCrump]

Welcome to the Forum!

Looks like this would give the required results?

=IF(AND(B1>=1,B1<=18),VLOOKUP(B2,Sheet2!B:U,B1+2,),"")