Fórmula genérica
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Resumen
Búsquedas sensibles a mayúsculas y minúsculas en Excel
De forma predeterminada, las búsquedas estándar en Excel no distinguen entre mayúsculas y minúsculas. Tanto VLOOKUP como INDEX / MATCH simplemente devolverán la primera coincidencia, ignorando el caso.
Una forma directa de solucionar esta limitación es utilizar una fórmula de matriz basada en INDICE / COINCIDIR con EXACT. Sin embargo, si solo busca valores numéricos, SUMPRODUCT + EXACT también ofrece una forma interesante y flexible de realizar una búsqueda sensible a mayúsculas y minúsculas.
En el ejemplo, usamos la siguiente fórmula
=SUMPRODUCT(--(EXACT(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 SUMPRODUCT maneja las matrices de forma nativa.
Explicación
SUMPRODUCT está diseñado para trabajar con matrices, que multiplica y luego suma.
En este caso, somos dos matrices con SUMPRODUCT: B3: B8 y C3: C8. El truco consiste en realizar una prueba con los valores de la columna B y luego convertir los valores VERDADERO / FALSO resultantes en unos y ceros. Ejecutamos la prueba con EXACTO así:
EXACT(E3,B3:B8)
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 técnicamente es un "doble unario") para convertir 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 SUMPRODUCT se ve así:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT luego simplemente multiplica los elementos de cada matriz para producir una matriz final:
(0; 0; 775; 0; 0; 0)
Cuál SUMPRODUCTO luego suma y devuelve 775.
Entonces, la esencia de esta fórmula es que los valores FALSE se utilizan para cancelar todos los demás valores. Los únicos valores que sobreviven son los que eran VERDADEROS.
Tenga en cuenta que debido a que estamos usando SUMPRODUCT, esta fórmula tiene un giro único: si hay múltiples coincidencias, SUMPRODUCT devolverá la suma de esas coincidencias. Esto puede ser lo que desea o no, así que tenga cuidado si espera múltiples coincidencias.
Recuerde, esta fórmula solo funciona para valores numéricos, porque SUMPRODUCT no maneja texto. Si desea recuperar texto, use INDICE / MATCH + EXACT.
