Too many nested IF statements - Workaround anyone?

colman12

New Member
Joined
Jun 27, 2014
Messages
18
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?
 

Some videos you may like

Excel Facts

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

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
why are you not using a lookup table that starts....

1 =sensitivities!C29
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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})))
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top