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,),"")

 

[Jake_E]

I have a similar situation as well, but in my case, I have a dropdown list for the work order for the first one, then looking up the Note number but limiting the dropdown list to just the cells that reference the job number, then output the Note information in the final cell.

In the pictures below, we have two different test to do on Work Order 4510. I need the Note Dropdown list to be just the Note #s associated with the Work Order (i.e. WO 4337 will only show Note 602 (*Pressure Test), Work Order 4510 will show Note #'s 3 (Pressure Test) and 17 (Leak Test) in the dropdown list.). Then in the Pressure Test Specifications, I need the Notes from the selected dropdown from Note # for that work order only.

One of the issues I have is that without the reference to the work order #, it will just populate the first note that meets the number. As i have 4 different work orders with the same Note #, it only pulls the first one.


Here is the formula from the "Pressure Test Specifications" box. =VLOOKUP(E2,'Data Sheet'!E2:F8,2,FALSE)


Work Order Dropdown

Note Dropdown:

Data sheet

 

[StephenCrump]

*Jake_E, welcome to the Forum!

These are referred to as "dependent dropdowns".

As you have Excel 365, they are quite simple to set up using the UNIQUE and FILTER functions, e.g. as explained here: Create a dynamic dependent drop down list in Excel an easy way

If you get stuck, please create a new thread. You're more likely to get responses that way than if you piggyback off a (not very related) existing thread.