SUBSTITUTE Function - Nesting Limitation

[Guest]

Maybe Subsitute is the wrong function.. if it is, let me know of a cleaner
easier way to do this.

Simply, I have a list of 10 Categories, each relating to an individual. The
issues are all listed in a drop down box in Column B. I want Colmun F to list
the individual automatically.

Lets say the Categories are: Cat1, Cat2, Cat3....
And the related people are: Per1, Per2, Per3...

When I put "Cat1" is cell B4, I want "Per1" to appear in F4

The formula I wrote for this is

F4 =
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"Cat1","Per1"),"Cat2","Per2"),"Cat3","Per3)....

This works. The problem is that with 10 Categories, and the limitation of
only nesting 7 functions... I can't get it to work for the whole group.

Can anyone suggest a workaround.. or a different function or way to do this?

Thanks.. I really appreciate it.

Dave

 

[Guest]

Take a look VLOOKUP function in help menu

 

[Guest]

I think reality is going to prove that the VLOOKUP() solution is really the
one to use, but if the situation were exactly as you've described, you could
have used =SUBSTITUTE(B4,"Cat","Per")