SUBSTITUTE Function - Nesting Limitation

D

D Bagatelle

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
 
J

JLatham

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")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top