Fórmula genérica
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Resumen
Para probar una celda para una de muchas cadenas, mientras excluye otras, puede usar una fórmula basada en las funciones SEARCH, ISNUMBER y SUMPRODUCT. En el ejemplo que se muestra, la fórmula en C5 es:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
donde "incluir" es el rango con nombre E5: E9 y "excluir" es el rango con nombre G5: G6.
Explicación
En esencia, esta fórmula utiliza la función BUSCAR para buscar varias cadenas dentro de una celda. Dentro del SUMPRODUCTO izquierdo, SEARCH busca todas las cadenas en el rango con nombre "incluir".
En el SUMPRODUCTO de la derecha, SEARCH busca todas las cadenas en el rango denominado "excluir".
En ambas partes de la fórmula, SEARCH devuelve posiciones numéricas cuando se encuentran cadenas y errores cuando no. La función ISNUMBER convierte los números en VERDADERO y los errores en FALSO, y el doble negativo convierte los valores VERDADERO FALSO en 1 y 0.
El resultado en este punto se ve así:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Entonces:
=(1>0)*(0=0) =TRUE*TRUE =1
Nota: esta fórmula devuelve 1 o cero, que se manejan como VERDADERO y FALSO en fórmulas, formato condicional o validación de datos.
