Too many nested IF statements - Workaround anyone?

[colman12]

Hi,

There are two tabs in this example:

First tab (Developments) pulls data from a second tab (Sensitivities) based on what scenario is chosen for a particular project.

At the minute i have a need for 10 nested if statements (as there are 10 scenarios to chose from) but i don't think excel will allow me to do this.

this is what the forumula looks like up to 7 IF statements - which works

=IF($A$43=1,Sensitivites!C29,IF($A$43=2,Sensitivites!C384,IF($A$43=3,Sensitivites!C739,IF($A$43=4,Sensitivites!C1094,IF($A$43=5,Sensitivites!C1449,IF($A$43=6,Sensitivites!C1804,IF($A$43=7,Sensitivites!C2157,0)))))))

I need to go up to 10, I have been reading that there is a vlookup work around but not sure how that works for me?

I also tried using a concatenation - it returns the numbers but when i sum them up in a formula it doesn't recognise that there are any numbers and sums up the line to zero.

=IF($A$32=1,Sensitivites!C18,"")&IF(Developments!$A$32=2,Sensitivites!C373,"")&IF(Developments!$A$32=3,Sensitivites!C728,"")&IF(Developments!$A$32=4,Sensitivites!C1083,"")&IF(Developments!$A$32=5,Sensitivites!C1438,"")&IF($A$32=6,Sensitivites!C1793,"")&IF(Developments!$A$32=7,Sensitivites!C2146,"")&IF(Developments!$A$32=8,Sensitivites!C2500,"")&IF(Developments!$A$32=9,Sensitivites!C2854,"")&IF(Developments!$A$32=10,Sensitivites!C3208,"")


Any help would be much appreciated?

 

[oldbrewer]

why are you not using a lookup table that starts....

1 =sensitivities!C29

 

[Special-K99]

Or try

=IF(AND($A$32 > = 1,$A$32 < = 10),INDIRECT("Sensitivities!C"&LOOKUP($A$32,{1,2,3,4,5,6,7,8,9,10},{18,373,728,1083,1438,1793,2146,2500,2854,3208})))