Fórmula de Excel: Comprobar si una celda contiene todos los elementos de una lista

Supongamos que en Excel tienes una lista de valores en la columna E y quieres comprobar si las celdas de la columna B contienen todos esos valores, devolviendo VERDADERO o FALSO, tal como se muestra en la siguiente captura de pantalla. En este tutorial te ofrecemos una fórmula para resolver esta tarea.

Fórmula genérica:

Argumentos

Valor de devolución:

Esta fórmula devuelve un valor lógico: FALSO indica que la celda no contiene todos los elementos; VERDADERO, que sí los contiene.

Cómo funciona esta fórmula

Por ejemplo, si en la columna B tiene una lista de cadenas de texto y desea comprobar si contienen todos los valores del rango E3:E5, utilice la siguiente fórmula.

Pulse la tecla Entrar y arrastre el controlador de relleno sobre las celdas que desee comprobar. FALSO indica que la celda no contiene todos los valores del rango E3:E5, mientras que VERDADERO significa que sí los incluye.

Explicación

Función HALLAR: devuelve la posición del primer carácter de una cadena de texto dentro de otra. Si encuentra el texto, devuelve su posición; si no lo encuentra, devuelve el error #¡VALOR!. Por ejemplo, la fórmula SEARCH($E$3:$E$5,B4) busca cada valor del rango E3:E5 en la celda B4 y devuelve la posición de cada cadena encontrada en dicha celda. El resultado es una matriz como esta: {1;7;12}

Función ESNUMERO: la función ESNUMERO comprueba si un valor es un número y devuelve VERDADERO o FALSO. En este caso, ISNUMBER(SEARCH($E$3:$E$5,B4)) devolverá un resultado matricial como {true;true;true}, ya que la función HALLAR encuentra 3 coincidencias numéricas.

--ISNUMBER(SEARCH($E$3:$E$5,B4)) convierte el valor VERDADERO en 1 y el valor FALSO en 0, transformando así el resultado matricial en {1;1;1}.

Función SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))): multiplica rangos o matrices y devuelve la suma de los productos. La fórmula SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) devuelve 1+1+1=3.

La función COUNTA($E$3:$E$5) devuelve el número de celdas no vacías. Por ejemplo, COUNTA($E$3:$E$5) devuelve 3, por lo que el resultado de SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) también es 3; así, toda la fórmula devolverá VERDADERO o FALSO.

Observaciones:

La fórmula =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) no realiza una comprobación exacta. Consulte la siguiente captura de pantalla:

Archivo de ejemplo

Haga clic para descargar el archivo de ejemplo

Fórmulas relacionadas

• Contar celdas iguales a
  Con la función CONTAR.SI, puedes contar celdas que sean iguales a un valor especificado o que no lo contengan.
• Contar celdas iguales a X o Y
  A veces, es posible que quieras contar el número de celdas que cumplen uno de dos criterios; en ese caso, puedes usar la función CONTAR.SI.
• Contar celdas iguales a x e y
  En este artículo te mostramos la fórmula para contar celdas que cumplan simultáneamente dos criterios.
• Contar celdas distintas de
  Este artículo explica cómo usar la función CONTAR.SI para contar las celdas que no coinciden con un valor específico.