More than 7 nested if statements

[AndrewR]

Hi there,

Is there a way around using an if statement with more that 7 nested arguments? Whilst it works for 2007 I need it to be viewed in 2003.

My formula is below:

=IF($A$1=1,Raw_Volume!S4,IF($A$1=2,Raw_Volume!S9,IF($A$1=3,Raw_Volume!S86,IF($A$1=4,Raw_Volume!S151,IF($A$1=5,Raw_Volume!S164,IF($A$1=6,Raw_Volume!S13,IF($A$1=7,Raw_Volume!S17,IF($A$1=8,Raw_Volume!S21,IF($A$1=9,Raw_Volume!S25,IF($A$1=10,Raw_Volume!S29,IF($A$1=11,Raw_Volume!S33,IF($A$1=12,Raw_Volume!S37,IF($A$1=13,Raw_Volume!S41,IF($A$1=14,Raw_Volume!S45,IF($A$1=15,Raw_Volume!S49,IF($A$1=16,Raw_Volume!S53,IF($A$1=17,Raw_Volume!S57,IF($A$1=18,Raw_Volume!S61,IF($A$1=19,Raw_Volume!S65,IF($A$1=20,Raw_Volume!S69,IF($A$1=21,Raw_Volume!S73,IF($A$1=22,Raw_Volume!S77,IF($A$1=23,Raw_Volume!S81,IF($A$1=24,Raw_Volume!S90,IF($A$1=25,Raw_Volume!S94,IF($A$1=26,Raw_Volume!S98,IF($A$1=27,Raw_Volume!S102,IF($A$1=28,Raw_Volume!S106,IF($A$1=29,Raw_Volume!S110,IF($A$1=30,Raw_Volume!S114,IF($A$1=31,Raw_Volume!S118,IF($A$1=32,Raw_Volume!S122,IF($A$1=33,Raw_Volume!S126,IF($A$1=34,Raw_Volume!S130,IF($A$1=35,Raw_Volume!S134,IF($A$1=36,Raw_Volume!S138,IF($A$1=37,Raw_Volume!S142,IF($A$1=38,Raw_Volume!S146,IF($A$1=39,Raw_Volume!S155,IF($A$1=40,Raw_Volume!S159,IF($A$1=41,Raw_Volume!S168,IF($A$1=42,Raw_Volume!S172,IF($A$1=43,Raw_Volume!S176,IF($A$1=44,Raw_Volume!S180,IF($A$1=45,Raw_Volume!S184,IF($A$1=46,Raw_Volume!S188,IF($A$1=47,Raw_Volume!S192,IF($A$1=48,Raw_Volume!S196,IF($A$1=49,Raw_Volume!S200,IF($A$1=50,Raw_Volume!S204,IF($A$1=51,Raw_Volume!S208,IF($A$1=52,Raw_Volume!S212,IF($A$1=53,Raw_Volume!S216,IF($A$1=54,Raw_Volume!S220,IF($A$1=55,Raw_Volume!S224,IF($A$1=56,Raw_Volume!S228,IF($A$1=57,Raw_Volume!S232,IF($A$1=58,Raw_Volume!S236,IF($A$1=59,Raw_Volume!S240)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Thanks
Andrew

 

[Dsewardj]

Wow,

There could be several solutions, the easiest being a simple lookup table and using a VLOOKUP function to return the value you want. I would simply create a table with a column numbered 1-59 and then create another column beside it with the corresponding values you wish to return.