Formula generică
=INDEX(range1,MATCH(TRUE,EXACT(A1,range2),0))
rezumat
Pentru a efectua o căutare sensibilă la majuscule, puteți utiliza funcția EXACT împreună cu INDEX și MATCH. În exemplul de prezentare, formula din F5 este:
=INDEX(C5:C14,MATCH(TRUE,EXACT(E5,B5:B14),0))
care returnează 39 de ani, vârsta lui "JILL SMITH".
Notă: aceasta este o formulă matrice și trebuie introdusă cu Control + Shift + Enter, cu excepția Excel 365.
Explicaţie
La bază, aceasta este o formulă INDEX și MATCH, cu funcția EXACT utilizată în MATCH pentru a efectua o potrivire sensibilă la majuscule.
Funcționând din interior spre exterior, EXACT este configurat pentru a compara valoarea din E5 cu numele din intervalul B5: B14:
EXACT(E5,B5:B14) // returns array of results
Funcția EXACT efectuează o comparație sensibilă la majuscule și minuscule și returnează TRUE sau FALSE ca rezultat. Doar valorile care se potrivesc exact vor reveni la TRUE. Deoarece verificăm numele din E5 („JILL SMITH”) în comparație cu toate cele zece nume din intervalul B5: B14, primim înapoi o matrice de zece valori ADEVĂRATE și FALSE ca aceasta:
(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE)
Această matrice este returnată direct la funcția MATCH ca lookup_array astfel:
MATCH(TRUE,(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE),0)
Cu o valoare de căutare TRUE, MATCH returnează 5, deoarece singurul TRUE din matrice este la a cincea poziție. Observați, de asemenea, că match_type este setat la zero (0) pentru a forța o potrivire exactă.
Funcția MATCH returnează un rezultat direct la funcția INDEX ca număr de rând, astfel încât să putem rescrie formula astfel:
=INDEX(C5:C14,5) // returns 39
INDEX returnează vârsta în al cincilea rând, 39, ca rezultat final.
Deoarece utilizarea funcției EXACT ca aceasta este o operație de matrice, formula este o formulă de matrice și trebuie introdusă cu Control + Shift + Enter, cu excepția Excel 365.
Cu XLOOKUP
În Excel 365, funcția XLOOKUP poate fi configurată pentru a efectua o căutare sensibilă la majuscule și minuscule într-un mod similar ca acesta:
=XLOOKUP(TRUE,EXACT(J5,B5:B14),C5:C14,"na",0)
Observați că valoarea de căutare și matricea de căutare sunt configurate la fel ca funcția MATCH de mai sus. După executarea EXACT, avem:
=XLOOKUP(TRUE,(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE),C5:C14,"na",0)
iar XLOOKUP returnează cel de-al 5-lea element din intervalul C5: C14 (39) ca rezultat final.
