Did you look at Debra's sample file?



ISAF Media Analysis wrote:

I am not sure if that would work, I would have to see an example of it.
Basically, if I input the word "Criminal Activity" into a cell I need the
next cell to indicate "Security" If I input the term Government Corruption"
into the same cell I need the next cell over "Governance." I have several
categories like this. Cheers.

"Barb Reinhardt" wrote:

Correct me if I'm wrong, you you probably have multiple subcategories for a
given category. I'm thinking you may want something like a dependent list.

http://www.contextures.com/xlDataVal13.html
--
HTH,
Barb Reinhardt



"ISAF Media Analysis" wrote:

Hello,

I'm trying to find a formula that would allow me to enter any one of several
categories into one cell and automatically determine the sub-category for
that cell. I have tried using the "Substitute" function only to discover a
major limitation which only lets me enter seven or eight categories. I need
to be able to have this formula work with at least 15-20 categories.

I have also, tried the "Lookup" function and the "If" function with similar
problems. Below is a copy of the formulas that I have tried with no success.
Can anyone help. Thanks in advance.


=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G6,"AFGHAN
NEIGHBORHOOD","SECURITY"),"CIVILIAN CASUALTIES","SECURITY"),"COALITION
ACTIVITY","SECURITY"),"CRIMINAL ACTIVITY","SECURITY"),"INSURGENT
ACTIVITY","SECURITY"),"INTERNATIONAL EFFORTS","SECURITY"),"GOVERNMENT
CORRUPTION","GOVERNANCE"),"GOVERNMENT EFFECTIVENESS","GOVERNANCE")

=IF(G7="AFGHAN NEIGHBORHOOD","SECURITY",IF(G7="CIVILIAN
CASUALTIES","SECURITY",IF(G7="GOVERNMENT
CORRUPTION","GOVERNANCE",IF(G7="SOCIAL
DEVELOPMENT","CROSS-CUTTING",IF(G7="HUMANITARIAN
CRISIS","CROSS-CUTTING",IF(G7="ECONOMIC
DEVELOPMENT","CROSS-CUTTING",IF(G7="GOVERNMENT
EFFECTIVENESS","GOVERNANCE",IF(G7="NARCOTICS","GOV ERNANCE","MISC"))))))))

=LOOKUP(G52,{"CIVILIAN CASUALTIES","ECONOMIC
DEVELOPMENT","NARCOTICS"},{"SECURITY","CROSS CUTTING","GOVERNANCE"})

--

Dave Peterson