EXCEL: Búsqueda de coincidencia exacta con SUMAPRODUCTO

Búsquedas de mayúsculas y minúsculas en Excel

Por defecto, las búsquedas estándar en Excel no distinguen entre mayúsculas y minúsculas. Tanto CONSULTAV como INDEX / MATCH simplemente devolverán el primer partido, ignorando el caso.

Una forma directa de solucionar esta limitación es utilizar una fórmula de matriz basada en INDEX / MATCH con IGUAL. Sin embargo, si solo busca valores numéricos, SUMAPRODUCTO + IGUAL también ofrece una forma interesante y flexible de hacer una búsqueda entre mayúsculas y minúsculas.

En el ejemplo, estamos usando la siguiente fórmula

=SUMAPRODUCTO(--(IGUAL(E3,B3:B8)),C3:C8)

Aunque esta fórmula es una fórmula de matriz, no es necesario ingresarla con Control + Shift + Enter, ya que SUMAPRODUCTO maneja las matrices de forma nativa.

Como funciona la fórmula

SUMAPRODUCTO está diseñado para trabajar con matrices, que multiplica y luego suma.

En este caso, somos dos matrices con SUMAPRODUCTO: B3: B8 y C3: C8. El truco consiste en ejecutar una prueba en los valores de la columna B, luego convertir los valores VERDADERO / FALSO resultantes a 1 y 0. Realizamos la prueba con IGUAL así:

IGUAL(E3,B3:B8)

Lo que produce esta matriz:

{FALSO; FALSO; VERDADERO; FALSO; FALSO; FALSO}

Tenga en cuenta que el verdadero valor en la posición 3 es nuestra coincidencia. Luego usamos el doble negativo (es decir, que es técnicamente un "doble unario") para forzar estos valores VERDADERO / FALSO en 1 y 0. El resultado es esta matriz:

{0; 0; 1; 0; 0; 0}

En este punto del cálculo, la fórmula SUMAPRODUCTO se ve así:

=SUMAPRODUCTO({0;0;1;0;0;0},{875;750;775;675;800;825})

SUMAPRODUCTO luego simplemente multiplica los elementos en cada conjunto para producir un conjunto final:

{0; 0; 775; 0; 0; 0}

Que SUMAPRODUCTO suma y devuelve 775.

Entonces, la esencia de esta fórmula es que los valores FALSO se usan para cancelar todos los demás valores. Los únicos valores que sobreviven son aquellos que fueron VERDADERO.

Tenga en cuenta que debido a que estamos usando SUMAPRODUCTO, esta fórmula viene con un giro único: si hay varias coincidencias, SUMAPRODUCTO devolverá la suma de esas coincidencias. Esto puede o no ser lo que quieres, ¡así que ten cuidado si esperas múltiples coincidencias!

Recuerde, esta fórmula solo funciona para valores numéricos, porque SUMAPRODUCTO no maneja texto. Si desea recuperar texto, use INDEX / MATCH + IGUAL.