Fórmula genérica
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Resumen
Si desea probar una celda para ver si contiene todos los elementos de una lista, puede hacerlo con una fórmula que use la función BUSCAR, con la ayuda de las funciones ISNUMBER, SUMPRODUCT y COUNTA.
Contexto
Supongamos que tiene una lista de cadenas de texto en el rango B5: B8 y desea averiguar si estas celdas contienen todas las palabras en otro rango, E5: E7.
Puede crear una fórmula que use declaraciones IF anidadas para verificar cada elemento, pero eso no se escalará bien si tiene muchas cosas que buscar. Cada vez que agregue una palabra para buscar, deberá agregar otro SI anidado y ajustar los paréntesis.
Solución
La solución es crear una fórmula que cuente todas las coincidencias a la vez. Una vez que tenemos eso, simplemente comparamos ese recuento con el recuento de artículos que estamos buscando. Si coinciden, sabemos que una celda contiene todos los elementos.
En el ejemplo que se muestra, la fórmula que estamos usando es:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Explicación
La clave es este fragmento:
ISNUMBER(SEARCH(things,B5)
Esto se basa en otra fórmula (explicada en detalle aquí) que simplemente verifica una celda para una sola subcadena. Si la celda contiene la subcadena, la fórmula devuelve VERDADERO. Si no, la fórmula devuelve FALSO.
Sin embargo, si le damos a la misma fórmula una lista de cosas (en este caso, estamos usando un rango con nombre llamado "cosas", E5: E7), nos devolverá una lista de valores VERDADERO / FALSO, uno para cada elemento en . El resultado es una matriz que se ve así:
(VERDAD VERDAD VERDAD)
Donde cada VERDADERO representa un elemento encontrado y cada FALSO representa un elemento no encontrado.
Podemos forzar los valores VERDADERO / FALSO a 1 y 0 con un doble negativo (-, también llamado unario doble):
--ISNUMBER(SEARCH(things,B5))
que produce una matriz como esta:
(1; 1; 1)
A continuación, procesamos esta matriz con SUMPRODUCT, que nos dará una suma total. Si esta suma es igual al número de elementos en el rango denominado "cosas", sabemos que hemos encontrado todas las cosas y podemos devolver VERDADERO. La forma en que hacemos esto es comparar los dos números directamente. Obtenemos un recuento de celdas que no están en blanco en "cosas" usando COUNTA:
=COUNTA(things)
Con una lista codificada
No es necesario que uses un rango para tu lista de cosas. Si solo está buscando una pequeña cantidad de cosas, puede usar una lista en formato de matriz, que se denomina constante de matriz. Por ejemplo, si solo está buscando los colores rojo, azul y verde, puede usar ("rojo", "azul", "verde") así:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
