Formula generica
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Sommario
Ricerche sensibili al maiuscolo / minuscolo in Excel
Per impostazione predefinita, le ricerche standard in Excel non fanno distinzione tra maiuscole e minuscole. Sia CERCA.VERT che INDICE / PARTITA restituiranno semplicemente la prima corrispondenza, ignorando le maiuscole e le minuscole.
Un modo diretto per aggirare questa limitazione è usare una formula di matrice basata su INDICE / CONFRONTA con ESATTO. Tuttavia, se stai cercando solo valori numerici, SUMPRODUCT + EXACT offre anche un modo interessante e flessibile per eseguire una ricerca con distinzione tra maiuscole e minuscole.
Nell'esempio, stiamo usando la seguente formula
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Sebbene questa formula sia una formula di matrice, non è necessario immetterla con Control + Maiusc + Invio, poiché SUMPRODUCT gestisce gli array in modo nativo.
Spiegazione
SUMPRODUCT è progettato per funzionare con gli array, che moltiplica, quindi somma.
In questo caso, siamo due array con SUMPRODUCT: B3: B8 e C3: C8. Il trucco è eseguire un test sui valori nella colonna B, quindi convertire i valori VERO / FALSO risultanti in 1 e 0. Eseguiamo il test con ESATTO in questo modo:
EXACT(E3,B3:B8)
Che produce questo array:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Nota che il vero valore nella posizione 3 è la nostra corrispondenza. Quindi usiamo il doppio negativo (cioè -, che tecnicamente è un "doppio unario") per forzare questi valori VERO / FALSO in 1 e 0. Il risultato è questo array:
(0; 0; 1; 0; 0; 0)
A questo punto del calcolo, la formula SUMPRODUCT ha questo aspetto:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT quindi moltiplica semplicemente gli elementi in ogni array per produrre un array finale:
(0; 0; 775; 0; 0; 0)
Quale SUMPRODUCT quindi somma e restituisce 775.
Quindi, l'essenza di questa formula è che i valori FALSE vengono utilizzati per annullare tutti gli altri valori. Gli unici valori che sopravvivono sono quelli che erano VERO.
Nota che poiché stiamo usando SUMPRODUCT, questa formula ha una svolta unica: se ci sono più corrispondenze, SUMPRODUCT restituirà la somma di quelle corrispondenze. Questo può essere o meno quello che vuoi, quindi fai attenzione se ti aspetti più partite!
Ricorda, questa formula funziona solo per i valori numerici, perché SUMPRODUCT non gestisce il testo. Se vuoi recuperare il testo, usa INDICE / CONFRONTA + ESATTO.
