# Too many nested IF statements - Workaround anyone?

#### colman12

##### New Member
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?

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### oldbrewer

##### Board Regular
why are you not using a lookup table that starts....

1 =sensitivities!C29

#### Special-K99

##### Well-known Member
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})))

Replies
4
Views
69
Replies
4
Views
579
Replies
18
Views
676
Replies
3
Views
258
Replies
0
Views
333

Threads
1,109,054
Messages
5,526,510
Members
409,705
Latest member
MB1984