Workaround for IF Worksheet Function 1

[thunderkid]

I am running into limitations with IF worksheet function--allows seven nested IF statement. I have budget worksheet and I want the balance to be read based on month of year. Here is an example:
B2 =IF(A4="JAN",E1,IF(A4="FEB",E2,IF(A4="MAR",E3,IF(A4="apr",E4,IF(A4="MAY",E5,IF(A4="JUN",E6,IF(A4="JUL",E7)))))))

A4 = JAN

E1 = 124
E2 = 125
E3 = 126
E4 = 127
E5 = 128
E6 = 129
E7 = 130

Using the IF worksheet function as described above I have to have redo formula in B2 in July to read the rest of the months.

Does anyone know another way to build formula (within one cell) that will allow me to read the balance from all 12 months?

Thanks

 

[jdhilljr]

Make A1 a number of the month and use choose()

=CHOOSE(A4,E1,E2,E3,E4,E5,E6,E7,E8,E9,E10,E11,E12)

Or if you want to look at other options go here for vlookup sample.

http://procollectortips.com/graph.xls

Jim

 

[thunderkid]

jdhilljr,
Your solution was right onthe mark. Thanks. have a star!
thunderkid

 

[jdhilljr]

Glad I could help.

Jim

 

[KenWright]

Other Options

=INDEX(E1:E12,A4)

=OFFSET(E1,A4-1,0)

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

And if you really wanted to keep the text month eg Jan, Feb etc then

=INDEX(E1:E12,MATCH(A4,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0))

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[macropod]

Or even:

=(A4="JAN")*E1+(A4="FEB")*E2+(A4="MAR")*E3+(A4="APR")*E4+(A4="MAY")*E5+(A4="JUN")*E6+(A4="JUL")*E7+(A4="AUG")*E8+(A4="SEP")*E9+(A4="OCT")*E10+(A4="NOV")*E11+(A4="DEC")*E12

Cheers