Formula Can't be Entered as it used more than 64 Levels of Nesting - Help!

[destructiver]

Hi,

I've seen this on various forums, with answers/solutions but i am struggling to apply the solution to my formula specifically... Here is the formula i am trying to do

=IF(AND($B$32="April",$B$36="2012"),C20,IF(AND($B$32="May",$B$36="2012",D20,IF(AND($B$32="June",$B$36="2012",D20,IF(AND($B$32="July",$B$36="2012",D20,IF(AND($B$32="August",$B$36="2012",D20,IF(AND($B$32="September",$B$36="2012",D20,IF(AND($B$32="October",$B$36="2012",D20,IF(AND($B$32="November",$B$36="2012",D20,IF(AND($B$32="December",$B$36="2012",D20,IF(AND($B$32="January",$B$36="2012",D20,IF(AND($B$32="February",$B$36="2012",D20,IF(AND($B$32="March",$B$36="2012",D20,IF(AND($B$32="April",$B$36="2013",D20,IF(AND($B$32="May",$B$36="2013",D20,IF(AND($B$32="June",$B$36="2013",D20,IF(AND($B$32="July",$B$36="2013",D20,IF(AND($B$32="August",$B$36="2013",D20,IF(AND($B$32="September",$B$36="2013",D20,IF(AND($B$32="October",$B$36="2013",D20,IF(AND($B$32="November",$B$36="2013",D20,IF(AND($B$32="December",$B$36="2013",D20,IF(AND($B$32="January",$B$36="2013",D20,IF(AND($B$32="February",$B$36="2013",D20,IF(AND($B$32="March",$B$36="2013",D20,IF(AND($B$32="April",$B$36="2014",D20,IF(AND($B$32="May",$B$36="2014",D20,IF(AND($B$32="June",$B$36="2014",D20,IF(AND($B$32="July",$B$36="2014",D20,IF(AND($B$32="August",$B$36="2014",D20,IF(AND($B$32="September",$B$36="2014",D20,IF(AND($B$32="October",$B$36="2014",D20,IF(AND($B$32="November",$B$36="2014",D20,IF(AND($B$32="December",$B$36="2014",D20,IF(AND($B$32="January",$B$36="2014",D20,IF(AND($B$32="February",$B$36="2014",D20,IF(AND($B$32="March",$B$36="2014",D20,IF(AND($B$32="April",$B$36="2015",D20,IF(AND($B$32="May",$B$36="2015",D20,IF(AND($B$32="June",$B$36="2015",D20,IF(AND($B$32="July",$B$36="2015",D20,IF(AND($B$32="August",$B$36="2015",D20,IF(AND($B$32="September",$B$36="2015",D20,IF(AND($B$32="October",$B$36="2015",D20,IF(AND($B$32="November",$B$36="2015",D20,IF(AND($B$32="December",$B$36="2015",D20,IF(AND($B$32="January",$B$36="2015",D20,IF(AND($B$32="February",$B$36="2015",D20,IF(AND($B$32="March",$B$36="2015",D20,IF(AND($B$32="April",$B$36="2016",D20,IF(AND($B$32="May",$B$36="2016",D20,IF(AND($B$32="June",$B$36="2016",D20,IF(AND($B$32="July",$B$36="2016",D20,IF(AND($B$32="August",$B$36="2016",D20,IF(AND($B$32="September",$B$36="2016",D20,IF(AND($B$32="October",$B$36="2016",D20,IF(AND($B$32="November",$B$36="2016",D20,IF(AND($B$32="December",$B$36="2016",D20,IF(AND($B$32="January",$B$36="2016",D20,IF(AND($B$32="February",$B$36="2016",D20,IF(AND($B$32="March",$B$36="2016",D20)))))))))))))))))))))




I want it to look at the month and then the year and the return a cell value

Hopefully you can help!

Cheers

[Pepe Le Mokko]

If I understand correctly all combinations return D20 except the first?

Then =IF(AND($B$32="April",$B$36="2012"),C20,d20)

[ben_hensel]

Even if they were all different, I would push through a INDEX(MATCH,MATCH) on a lookup table; months on one axis and years on the other, populate the table with links to the master values. Much cleaner to maintain, debug, and update.

[oeldere]

Even if it is possible with 64 if forumula's I advice you to choose for a differant solution, since it is not easy to amend and not easy to understand.

As already offered in #3 you could use index/match.

You get better help if you add a small excel file without confidential information.

Please also add the expected result manualy in the excel file.

[destructiver]

Capture11.JPG

see image...

i want to see a return of the values depending on the orange cells

[oeldere]

You get better help if you add a small excel file without confidential information.

Please also add the expected result manualy in the excel file.

[Sam Capricci]

Agree with those who've already said to post a sample. But based on what your images in post #5 I would recommend you consider an HLOOKUP.

[newdoverman]

Something like this should solve your problem

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

32

Month

June

April

May

June

July

August

September

October

November

December

January

February

March

33

2012

34

2013

35

2014

123

36

Year

2014

2015

37

2016

38

39

123

=INDEX(E33:P37,MATCH(B36,D33:D37,0),MATCH(B32,E32:P32,0))