I seem to have reached nested if limit WAY to soon

[Barry Scott]

I'm getting a formula error on a nested if. The formula works fine for 8 nested ifs but I get a error on the 9th if. I have tried changing the 9th if to look at other cells or other conditions but I still get the error. Here is the code

=IF($O3=1,$B3,IF($O4=1,$B4,IF($O5=1,$B5,IF($O6=1,$B6,IF($O7=1,$B7,IF($O8=1,$B7,IF($O9=1,$B9,IF($O10=1,$B10,IF($O11=1,$B11,IF($O12=1,$B12,IF($O13=1,$B13,9999)))))))))))

I've uploaded my workbook. The formula in question is on worksheet ca_g4_rr1 in cell B28

 

[Xiq]

Hi Barry Scott,

Simply save the file as xlsx (currently it's xls). This would increase the number of nested ifs allowed.

By the way, I can't find the formula you are referring to.

 

[Vijay Dhangar]

You can you use CHOOSE formula.....




=IF(O3<=4,CHOOSE(O3,ca_g4_rr1_d!#REF!,ca_g4_rr1_d!#REF!,ca_g4_rr1_d!#REF!,ca_g4_rr1_d!#REF!),"too many")

 

[Somendra Misra]

Hi Barry,

Looking at your IF formula you posted here, if you are going to have only one 1 in column O, than try below formula.

=INDEX($B$3:$B$14,MATCH(1,$O$3:$O$14,0))

Regards,