Formula generică
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
rezumat
Căutări sensibile la majuscule și minuscule în Excel
În mod implicit, căutările standard în Excel nu sunt sensibile la majuscule și minuscule. Atât VLOOKUP, cât și INDEX / MATCH vor returna pur și simplu primul meci, ignorând majuscule.
O modalitate directă de a rezolva această limitare este de a utiliza o formulă matrice bazată pe INDEX / MATCH cu EXACT. Cu toate acestea, dacă căutați numai valori numerice, SUMPRODUCT + EXACT oferă, de asemenea, un mod interesant și flexibil de a efectua o căutare sensibilă la majuscule.
În exemplu, folosim următoarea formulă
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Deși această formulă este o formulă matrice, nu trebuie introdusă cu Control + Shift + Enter, deoarece SUMPRODUCT gestionează matrici în mod nativ.
Explicaţie
SUMPRODUCT este conceput pentru a lucra cu tablouri, pe care le înmulțește, apoi sumează.
În acest caz, suntem două matrice cu SUMPRODUCT: B3: B8 și C3: C8. Trucul este să rulați un test pe valorile din coloana B, apoi să convertiți valorile TRUE / FALSE rezultate în 1 și 0. Executăm testul cu EXACT așa:
EXACT(E3,B3:B8)
Care produce această matrice:
(FALS; FALS; ADEVĂRAT; FALS; FALS; FALS)
Rețineți că adevărata valoare din poziția 3 este potrivirea noastră. Apoi folosim dublul negativ (adică -, care este tehnic un "dublu unar") pentru a constrânge aceste valori TRUE / FALSE în 1 și 0. Rezultatul este acest tablou:
(0; 0; 1; 0; 0; 0)
În acest moment al calculului, formula SUMPRODUCT arată astfel:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT multiplică apoi elementele din fiecare matrice pentru a produce o matrice finală:
(0; 0; 775; 0; 0; 0)
Care SUMPRODUCT apoi însumează și returnează 775.
Deci, esența acestei formule este că valorile FALSE sunt utilizate pentru a anula toate celelalte valori. Singurele valori care supraviețuiesc sunt cele care erau ADEVĂRATE.
Rețineți că, deoarece utilizăm SUMPRODUCT, această formulă vine cu o răsucire unică: dacă există mai multe potriviri, SUMPRODUCT va returna suma acestor potriviri. Acest lucru poate fi sau nu ceea ce doriți, așa că aveți grijă dacă vă așteptați la mai multe meciuri!
Amintiți-vă, această formulă funcționează numai pentru valori numerice, deoarece SUMPRODUCT nu gestionează textul. Dacă doriți să preluați text, utilizați INDEX / MATCH + EXACT.
