shorten an if statement

[sable]

How can I shorten this If statement?

=IF(E6="Monday",J7,IF(E6="Tuesday",K7,IF(E6="Wednesday",L7, IF(E6="Thursday",M7,IF(E6="Friday",N7,IF(E6="Saturday",O7, IF(E6="Sunday",P7)))))))

This works for me but I was wondering if there was an easier way to do write this formula.

[NBVC]

One Way:

=CHOOSE(WEEKDAY(E6,2),J7,K7,L7,M7,N7,O7,P7)

assuming you have an actual date in E6, formatting as "dddd"


else, you can list the days in J6:P6 and use HLookup

e.g.

=Hlookup(E6,J6:K7,2,0)

or if you want to put days lower down, say in J100:P100

=Index(J7:P7,Match(E6,J100:P100,0))

[sable]

actually the first way worked perfectly! Thanks so much!

[daddylonglegs]

Perhaps also......

=INDEX(J7:P7,WEEKDAY(E6,2))

and note that if E6 is blank both that formula and NBVC's CHOOSE will return the (Saturday) value in O7 so you might need to add an IF, i.e.

=IF(E6="","",INDEX(J7:P7,WEEKDAY(E6,2)))

[NBVC]

Perhaps also......

=INDEX(J7:P7,WEEKDAY(E6,2))

Good one... should've thought of that.. was fixed on a way trying to use Lookup() but can't use cell refs in arrays within formula.....and the Index() passed me by.

[sable]

thanks daddylonglegs... that actually a little better for me.