Need Help with Substitute Formulas, etc.

  • Thread starter ISAF Media Analysis
  • Start date
I

ISAF Media Analysis

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(SUBSTITUTE(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","GOVERNANCE","MISC"))))))))

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

Pete_UK

Set up a table in your sheet (eg in X1:Y15), made up like this:

AFGHAN NEIGHBORHOOD SECURITY
CIVILIAN CASUALTIES SECURITY
GOVERNMENT CORRUPTION GOVERNANCE
SOCIAL DEVELOPMENT CROSS-CUTTING
HUMANITARIAN CRISIS CROSS-CUTTING
ECONOMIC DEVELOPMENT CROSS-CUTTING
GOVERNMENT EFFECTIVENESS GOVERNANCE
NARCOTICS GOVERNANCE

and so on, and then you can use this formula:

=VLOOKUP(G7,X$1:Y$15,2,0)

Just add more items to the bottom of the table and adjust the table
range accordingly.

Hope this helps.

Pete
 
I

ISAF Media Analysis

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.
 
D

Dave Peterson

Did you look at Debra's sample file?


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.
Click to expand...
 
Top