Formula generică
=XLOOKUP(1,--EXACT(range1,"RED"),range2)
rezumat
Pentru a crea o potrivire exactă sensibilă la majuscule, puteți utiliza funcția XLOOKUP cu funcția EXACT. În exemplul prezentat, formula din F5 este:
=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15)
care se potrivește pe „RED” (sensibil la majuscule și minuscule) și returnează întregul rând.
Explicaţie
În sine, funcția XLOOKUP nu este sensibilă la majuscule și minuscule. O valoare de căutare „ROȘU” se va potrivi cu „roșu”, „ROȘU” sau „Roșu”. Putem rezolva această limitare construind o matrice de căutare adecvată pentru XLOOKUP cu o expresie logică.
Lucrând din interior spre exterior, pentru a oferi XLOOKUP capacitatea de a se potrivi cu majuscule, folosim funcția EXACT astfel:
EXACT(B5:B15,"RED") // test for "RED"
Deoarece există 11 valori în intervalul E5: D15, EXACT returnează o matrice cu 11 rezultate ADEVĂRATE astfel:
(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Observați că poziția TRUE corespunde rândului în care culoarea este „ROȘU”.
Pentru scurtă durată (și pentru a permite extinderea ușoară a logicii cu logica booleană), forțăm valorile TRUE FALSE la 1s și 0s cu dublu negativ:
--EXACT(B5:B15,"RED") // convert to 1s and 0s
care produce o matrice ca aceasta:
(0;0;0;0;1;0;0;0;0;0;0)
Observați că poziția 1 corespunde rândului în care culoarea este „ROȘU”. Această matrice este returnată direct la funcția XLOOKUP ca argument de matrice de căutare.
Acum putem pur și simplu formula pentru:
=XLOOKUP(1,(0;0;0;0;1;0;0;0;0;0;0),B5:D15)
Cu o valoare de căutare de 1, XLOOKUP îl găsește pe 1 în poziția a 5-a și returnează al 5-lea rând în matricea returnată, B9: D9.
Extinderea logicii
Structura logicii poate fi ușor extinsă. De exemplu, pentru a restrânge potrivirea la „ROȘU” în luna aprilie, puteți utiliza o formulă de genul acesta:
=XLOOKUP(1,EXACT(B5:B15,"RED")*(MONTH(C5:C15)=4),B5:D15)
Aici, deoarece fiecare dintre cele două expresii returnează o matrice de valori ADEVĂRATE și deoarece aceste matrice sunt înmulțite împreună, operația matematică constrânge valorile ADEVĂRATE și FALSE la 1s și 0s. Nu este necesar să folosiți dublul negativ.
Deoarece valoarea de căutare rămâne 1, ca în formula de mai sus.
Primul și ultimul meci
Ambele formule de mai sus vor returna prima potrivire a „RED” într-un set de date. Dacă aveți nevoie de ultima potrivire, puteți efectua o căutare inversă setând argumentul modului de căutare pentru XLOOKUP la -1:
=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15,,,-1) // last match
Dacă trebuie să returnați rezultate din mai multe potriviri, consultați funcția FILTRU.
