to many nested if functions. NEED HELP

[toothpastenerd]

Hello,

I am trying to make a master sheet that has data pulled from different sheets via a drop down box. I think I have found a way to do it with a redicutlous amount of If statements and vlookups with in one formula. It won't allow me finish the formula because here are too many. I tried it in advance on a smaller scale and it does what I want. This is obvously not the best way to get all the info but i'm not sure of another way. Thanks in advance.

here is the formula i want to use:

=IF(H1=M2,VLOOKUP(Test!M3,'Report 1'!A9:E27,2,FALSE),IF(H1=M3,VLOOKUP(M3,'Report 4'!A9:E27,2,FALSE),IF(Test!H1=Test!M4,VLOOKUP(Test!M3,'Report 7'!A9:E27,2,FALSE),IF(H1=M5,VLOOKUP(M3,'Report 10'!A9:E27,2,false),IF(Test!H1=Test!M6,VLOOKUP(Test!M3,'Report 13'!A9:E27,2,false),IF(Test!H1=Test!M7,VLOOKUP(Test!M3,'Report 16'!A9:E27,2,false),IF(Test!H1=Test!M8,VLOOKUP(Test!M3,'Report 19'!A9:E27,2,false),IF(Test!H1=Test!M9,VLOOKUP(Test!M3,'Report 22'!A9:E27,2,false),IF(Test!H1=Test!M10,VLOOKUP(Test!M3,'Report 25'!A9:E27,2,false), IF(Test!H1=Test!M11,VLOOKUP(Test!M3,'Report 28'!A9:E27,2,false), IF(Test!H1=Test!M12, VLOOKUP(Test!M3,'Report 31'!A9:E27,2,false),IF(Test!H1=Test!M13,VLOOKUP(Test!M3,'Report 34'!A9:E27,2,false),IF(Test!H1=Test!M14,VLOOKUP(Test!M3,'Report 37'!A9:E27,2,false))))

H1 is connected to the drop down, and the M column is the corresponding name that relates to the different sheet. Test is the master sheet I want it to be on

This is pretty confusing but some of you guys are pretty good so give it a shot.

Thanks

[Special-K]

Since your report number always jumps up by 3 for each element in the M column, try (untested)
=VLOOKUP(Test!M3,INDIRECT("'Report '"&(MATCH(Test!H1,Test!M$2:M$14,1,0)-1)*3+1&"!A9:E27"),2,FALSE)

Note 'Report ' is in double quotes too

[toothpastenerd]

Thanks Special - K,

This didn't work but I think you are onto something. The error I got was that "there are too many arguments for this function". So I looked closer and thought that you might have had a mistake in the Match portion. so in bold are the things I removed
=VLOOKUP(Test!M3,INDIRECT("'Report '"&(MATCH(Test!H1,Test!M$2:M$14,1,0)-1)*3+1&"!A9:E27"),2,FALSE)

I tried every combination of match types but nothing worked, I only got REF in the cell. I also made sure that there were the correct amount of parentheses. Again I am not that well versed so I'm not sure if the correct solution.

Thanks again, any help if appreciated.

- TB