EXCEL: La celda contiene algunas palabras pero no otras

Para probar una celda para ver si contiene ciertas palabras pero no otras, puede usar una fórmula de matriz basada en las funciones CONTAR y HALLAR, envuelta en la función Y.

En el ejemplo que se muestra, la fórmula en C5 es:

{=Y(CONTAR(HALLAR(inc,B5))>0,CONTAR(HALLAR(exc,B5))=0)}

Esta fórmula devuelve VERDADERO cuando B5 contiene alguna de las palabras en el rango con nombre inc y ninguna de las palabras en el rango con nombre exc.

Esta es una fórmula de matriz y debe ingresarse con Control + Shift + Enter.

Cómo funciona esta fórmula

Esta fórmula se basa en la función Y para probar dos condiciones al mismo tiempo:

1. Count of words from named range inc is >0
2. Count of words from named range exc is =0

Si ambas condiciones son VERDADERO, la fórmula devuelve VERDADERO. Si cualquiera de las condiciones es FALSO, la fórmula devuelve FALSO.

La prueba para varias palabras se realiza utilizando la función HALLAR con la ayuda de CONTAR.

Cuando HALLAR recibe más de un elemento para buscar, devuelve una matriz de resultados, uno por elemento. Cuando se encuentra una coincidencia, HALLAR devuelve la posición de la coincidencia. Cuando no se encuentra ninguna coincidencia, HALLAR devuelve el error #VALOR. La función CONTAR obtiene un recuento de números en la matriz devuelta por HALLAR. CONTAR ignora los errores por defecto.

En el ejemplo que se muestra, la fórmula se resuelve en pasos como este

=Y(CONTAR({1;11;#VALUE!})>0,CONTAR({#VALUE!;#VALUE!})=0)
=Y(2>0,0=0)
=Y(VERDADERO,VERDADERO)
=VERDADERO

Con valores codificados

No hay ningún requisito de que use un rango para su lista de cosas. Si solo está buscando un pequeño número de cosas, puede usar una lista en formato de matriz, que se denomina constante de matriz. Por ejemplo, para probar el rojo, el azul o el verde, pero excluyendo el rosa y el naranja, puede usar:

=Y(CONTAR(HALLAR({"red","blue","green"},B5))>0,CONTAR(HALLAR({"orange","pink"},B5))=0)

Esta versión no requiere la sintaxis de fórmula de matriz control + shift + enter.