Fórmula de Excel: Comprobar si una celda contiene uno de varios valores pero excluye otros valores

Supongamos que tiene dos listas de valores y desea verificar si la celda B3 contiene alguno de los valores del rango E3:E5, pero al mismo tiempo no incluye ninguno de los valores del rango F3:F4, tal como se muestra en la siguiente captura de pantalla. Este tutorial le proporcionará una fórmula para realizar esta tarea rápidamente en Excel y explicará cada uno de sus argumentos.

Fórmula genérica:

Argumentos

Valor de devolución:

La fórmula devuelve 1 o 0: devuelve 1 cuando la celda contiene alguno de los valores que deben incluirse y no contiene ninguno de los valores que deben excluirse; en caso contrario, devuelve 0. En esta fórmula, 1 y 0 se interpretan como los valores lógicos VERDADERO y FALSO, respectivamente.

Funcionamiento de esta fórmula

Suponiendo que desea comprobar si la celda B3 contiene alguno de los valores del rango E3:E5, pero al mismo tiempo excluir los valores del rango F3:F4, utilice la siguiente fórmula

Pulse Intro para obtener el resultado de la comprobación.

Explicación

Parte 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) comprueba si la celda contiene valores en E3:E5

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

Función ESNUMERO: la función ESNUMERO devuelve VERDADERO cuando una celda contiene un número. Por tanto, ISNUMBER(SEARCH($E$3:$E$5,B3)) devolverá un resultado de matriz como {VERDADERO;VERDADERO;VERDADERO}, ya que la función HALLAR ha encontrado tres coincidencias numéricas.

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

Función SUMAPRODUCTO: se utiliza para multiplicar rangos o matrices y devolver la suma de los productos. La fórmula SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) devuelve 1+1+1=3.

Por último, se compara la fórmula de la izquierda SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) con 0. Si el resultado de dicha fórmula es mayor que 0, devuelve VERDADERO; en caso contrario, devuelve FALSO. En este caso, el resultado es VERDADERO.

Parte 2: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) comprueba si la celda no contiene valores en F3:F4

La fórmula HALLAR($F$3:$F$4;B3) buscará cada valor del rango E3:E5 en la celda B3 y devolverá la posición de cada cadena de texto dentro de dicha celda. El resultado será un array como este: {#¡VALOR!;#¡VALOR!}.

ISNUMBER(SEARCH($F$3:$F$4,B3)) devolverá un resultado de array como {FALSO;FALSO}, ya que la función HALLAR no ha encontrado ninguna coincidencia.

--ISNUMBER(SEARCH($F$3:$F$4,B3)) convierte el valor VERDADERO en 1 y el valor FALSO en 0, transformando así el resultado del array en {0;0}.

Función SUMAPRODUCTO: se utiliza para multiplicar rangos o matrices y devolver la suma de los productos. La fórmula SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) devuelve 0+0=0.

Por último, se compara la fórmula de la izquierda SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) con 0. Si el resultado de dicha fórmula es igual a 0, el resultado será VERDADERO; en caso contrario, será FALSO. En este caso, devuelve VERDADERO.

Parte 3: Multiplicar ambas fórmulas

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

En esta fórmula, 1 y 0 se interpretan como los valores lógicos VERDADERO y FALSO.

Archivo de ejemplo

Haga clic para descargar el archivo de ejemplo

Fórmulas relacionadas

• Comprobar si una celda contiene un texto específico
  Para comprobar si una celda contiene alguno de los textos del rango A pero no incluye ninguno de los del rango B, puede usar una fórmula matricial que combine las funciones CONTAR, HALLAR y Y en Excel.
• Comprobar si una celda contiene uno de varios elementos
  Este tutorial te ofrece una fórmula para verificar si una celda contiene alguno de varios valores en Excel, y explica con claridad sus argumentos y cómo funciona.
• Comprobar si una celda contiene alguno de los elementos
  Supongamos que en Excel tienes una lista de valores en la columna E y quieres comprobar si las celdas de la columna B contienen alguno de esos valores, devolviendo VERDADERO o FALSO.
• Comprobar si una celda contiene un número
  ¿Necesitas saber si una celda contiene un número? Este tutorial te ofrece una fórmula que devuelve VERDADERO si la celda incluye un valor numérico y FALSO si no lo contiene.