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