Nested IF statement: Breaking the 7 output limit?

[Carl B]

I was surfing the web and ran into a little formula that goes beyond the 7 outputs of an IF statement.
Whenever I had more choices then the 7 output limit, I usually parsed it out over a couple cells to get my results.
Whereas this person was able to get 27 different outputs to thier IF statement in a single cell.
How it was shown was by outputting a number associated with a letter the user types in, e.g. A = 1, B=2 etc..

What I was wondering is if anyone here would like to take on that challenge?
Can we see how many different ways this can be done with an IF statement?

I will post the IF statement if anyone is curious.

 

[PaddyD]

of the many approaches:

http://j-walk.com/ss/excel/usertips/tip080.htm

 

[Carl B]

I like those work arounds, gets me thinking about where I have used other things where those would diffenitely tidy up the ws.

This was what I found:

=IF(OR(B2="a",B2="b",B2="c",B2="d",B2="e",B2="f"),IF(B2="a",1,IF(B2="b",2,IF(B2="c",3,IF(B2="d",4,IF(B2="e",5,6))))),IF(OR(B2="g",B2="h",B2="i",B2="j",B2="k",B2="l"),IF(B2="g",7,IF(B2="h",8,IF(B2="i",9,IF(B2="j",10,IF(B2="k",11,12))))),IF(OR(B2="m",B2="n",B2="o",B2="p",B2="q",B2="r"),IF(B2="m",13,IF(B2="n",14,IF(B2="o",15,IF(B2="p",16,IF(B2="q",17,18))))),IF(OR(B2="s",B2="t",B2="u",B2="v",B2="w"),IF(B2="s",19,IF(B2="t",20,IF(B2="u",21,IF(B2="v",22,23)))),IF(B2="x",24,IF(B2="y",25,IF(B2="z",26,"error - type a letter")))))))

 

[fairwinds]

...can be shortened to.

=CODE(B2)-96


Or if you do not actually have those letters; Use VLOOKUP.

Or maybe:

=MATCH(B2,{"a";"b";"c";"d";"e";"f";"g";"h";"i";"j";"k";"l";"m";"n";"o";"p";"q";"r";"s";"t";"u";"v";"w";"x";"y";"z"},0)