Simplified IF statement

[manc]

Good morning Demigods,

Can the following IF statement be shortened at all?

=IF(CO10<>0,IF(MONTH(O1)=1,O3,IF(MONTH(O1)=2,S3,IF(MONTH(O1)=3,W3,IF(MONTH(O1)=4,AA3,IF(MONTH(O1)=5,AE3,IF(MONTH(O1)=6,AI3,IF(MONTH(O1)=7,AM3,IF(MONTH(O1)=8,AQ3,IF(MONTH(O1)=9,AU3,IF(MONTH(O1)=10,AY3,IF(MONTH(O1)=11,BC3,IF(MONTH(O1)=12,BG3)))))))))))),IF(MONTH(O1)=1,O4,IF(MONTH(O1)=2,S4,IF(MONTH(O1)=3,W4,IF(MONTH(O1)=4,AA4,IF(MONTH(O1)=5,AE4,IF(MONTH(O1)=6,AI4,IF(MONTH(O1)=7,AM4,IF(MONTH(O1)=8,AQ4,IF(MONTH(O1)=9,AU4,IF(MONTH(O1)=10,AY4,IF(MONTH(O1)=11,BC4,IF(MONTH(O1)=12,BG4)))))))))))))

Depending on what month is in cell O1, it takes the value from O3 if O1=1, S3 if O1=2, W3 if O1=3 etc....

I'm looking to use this formula as part of a bigger one, so having this formula as small and as simple as possible would be a great help.

Any help/suggestions much appreciated.

Best regards
manc

 

[FormR]

Hi, you could try.

=INDEX(O3:BG4,IF(CO10<>0,1,2),((MONTH(O1)-1)*4)+1)

 

[Yongle]

or use CHOOSE instead of IF

exaample
=IF(MONTH(O1)=1,O3,IF(MONTH(O1)=2,S3,IF(MONTH(O1)=3,W3,IF(MONTH(O1)=4,AA3,IF(MONTH(O1)=5,AE3,IF(MONTH(O1)=6,AI3,IF(MONTH(O1)=7,AM3,IF(MONTH(O1)=8,AQ3,IF(MONTH(O1)=9,AU3,IF(MONTH(O1)=10,AY3,IF(MONTH(O1)=11,BC3,IF(MONTH(O1)=12,BG3))))))))))))
same as
=CHOOSE(MONTH(O1),O3,S3,W3,AA3,AE3,AI3,AM3,AQ3,AU3,AY3,BC3,BG3)

 

[snjpverma]

Hi, you could try.

=INDEX(O3:BG4,IF(CO10<>0,1,2),((MONTH(O1)-1)*4)+1)

Wow, this is pure genius.

I was trying to solve it myself but I couldn't figure out this part "(MONTH(O1)-1)*4)+1"..
Loved it.

 

[manc]

Guys, thanks very much for your quick responses.

Thank-you FormR for:
=INDEX(O3:BG4,IF(CO10<>0,1,2),((MONTH(O1)-1)*4)+1)

Exactly what i was looking for.
Thank-you Yongle also.

Best regards
manc

 

[jasonb75]

Not an improvement, but a slight variation of FormR's suggestion so that you can see different ways to achieve the same result.

=INDEX(L3:BG4,1+(C10<>0),MONTH(O1)*4)