EXCEL: La celda es igual a una de muchas cosas

Si desea probar una celda para ver si es igual a una de varias cosas, puede hacerlo con una fórmula que use la función SUMAPRODUCTO.

Contexto

Digamos que tiene una lista de cadenas de texto en el rango B5: B11, y desea probar cada celda contra otra lista de cosas en el rango E5: E9. En otras palabras, para cada celda en B5: B11, desea saber: ¿esta celda es igual a alguna de las cosas en E5: E9?

Puede comenzar a crear una fórmula grande basada en declaraciones SI anidadas, pero una fórmula de matriz basada en SUMAPRODUCTO es un enfoque más simple y limpio.

Solución

La solución es crear una fórmula que pruebe múltiples valores y devuelva una lista de valores VERDADERO / FALSO. Una vez que tengamos eso, podemos procesar esa lista (una matriz, en realidad) con SUMAPRODUCTO.

La fórmula que estamos usando se ve así:

=SUMAPRODUCTO(--(B5=things))>0

Cómo funciona esta fórmula

La clave es este fragmento:

--(B5=things)

que simplemente compara el valor en B5 con cada valor en el rango nombrado "cosas". Debido a que estamos comparando B5 con una matriz (es decir, las "cosas" del rango con nombre, E5: E11) el resultado será una matriz de valores VERDADERO / FALSO como este:

{VERDADERO; FALSO; FALSO; FALSO; FALSO}

Si tenemos incluso un VERDADERO en la matriz, sabemos que B5 equivale a al menos una cosa en la lista, por lo tanto, para forzar los valores VERDADERO / FALSO a 1s y 0s, usamos un doble negativo (-, también llamado doble unario). Después de esta coerción, tenemos esto:

{1; 0; 0; 0; 0}

Ahora procesamos el resultado con SUMAPRODUCTO, que sumará los elementos en la matriz. Si obtenemos un resultado distinto de cero, tenemos al menos una coincidencia, por lo que usamos> 1 para forzar un resultado final de VERDADERO o FALSO.

Con una lista codificada

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, si solo está buscando los colores rojo, azul y verde, puede usar {"rojo", "azul", "verde"} de esta manera:

--(B5={"red","blue","green"})

Manejo de espacios extra

Si las celdas que está probando contienen espacio adicional, no coincidirán correctamente. Para eliminar todo el espacio extra, puede modificar la fórmula para usar la función ESPACIOS de la siguiente manera:

=SUMAPRODUCTO(--(ESPACIOS(A1)=things))>0