Într-un articol anterior am văzut cum se poate folosi formula Xlookup pentru a extrage informații în funcție de mai multe criterii exacte care trebuie respectate simultan.
Însă există situații în care criteriile care trebuie respectate nu sunt exacte.
De exemplu, folosind tabelul de mai jos dorim să extragem primul magazin în care s-au înregistrat vânzări mai mari de 3000 de lei pentru produsul Capsator.
XLOOKUP – formula generică cu mai multe criterii logice de căutare
Xlookup ne ajută să construim mai multe matrice bazate pe criterii de căutare folosind argumentul lookup_array. Astfel, se pot aplica mai multe teste logice.
Pentru exemplul nostru, formula generală este următoarea:
XLOOKUP(1,( lookup_array_1=lookup_value_1)*( lookup_array_2> lookup_value_2), return_array, [if_not_found], [match_mode], [search_mode])
Este important de reținut că se pot adăuga mai multe condiții prin completarea la cel de-al doilea argument a mai multor seturi de lookup_array si lookup_value. Acestea se vor scrie între paranteze și cu semnul * intre seturile de criterii.
De asemenea, la modalitatea de comparare poate fi folosit oricare operator logic (=, >, >=, <, <=).
Rezultatul oferit de Xlookup va fi cel care respectă toate condițiile notate la cel de-al doilea argument.
Exemplu: XLOOKUP cu mai multe criterii logice de căutare
Acum vom aplica Xlookup pentru exemplul de mai sus și vom extrage primul magazin în care s-au înregistrat vânzări mai mari de 3000 de lei pentru produsul Capsator.
Mai jos este formula care face acest lucru:
=XLOOKUP(1,(DataSet[Produs]=”Capsator”)*(DataSet[[Valoare vanzari ]]>3000),DataSet[Magazin])
În cadrul lookup_array am adăugat cele două criterii din exemplu:
(DataSet[Produs]=”Capsator”)*(DataSet[[Valoare vanzari ]]>3000)
Și, pentru că avem mai multe criterii logice, Excel evaluează fiecare set de criterii și returnează două serii care conțin valori TRUE/ FALSE:
- Rezultatul testului logic Produs = Capsator este -> {FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}
- Rezultatul testului logic Valoare vânzări >3000 este -> {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}
Prin înmulțirea seturilor de criterii ne asigurăm că aceste condiții sunt respectate simultan:
{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}*{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}
Rezultatul înmulțirii este o singură matrice de 1 și 0, în care 1 înseamnă că există True pe acelasi rând în seturile de criterii și 0 înseamnă că nu există True pe acelasi rând în seturile de criterii.
{0;0;0;0;0;0;0;1;0;0;0;0}
După ce Excel realizeaza aceste calcule, formula noastră arată astfel:
=XLOOKUP(1,{0;0;0;0;0;0;0;1;0;0;0;0},DataSet[Magazin])
Acum se întelege ușor că am folosit 1 la lookup_value pentru ca acesta reprezintă faptul că cele două condiții setate au fost îndeplinite simultan.
În continuare Xlookup caută și găsește că se potrivește „1” în poziția a 8-a din lookup_array și returnează cea de-a opta valoare corespunzătoare din return_array, în exemplul nostru coloana Magazin a tabelului.
Te invit să urmărești aplicarea formulei Xlookup cu mai multe criterii logice de căutare în următorul video:
Dacă dorești să arunci o privire mai atentă acestui exemplu poți descărca fișierul folosind butonul de mai jos:
Concluzie
Până acum am parcurs două modalități în care Xlookup se poate folosi pentru căutările cu mai multe condiții. Trebuie să recunosc că varianta cu concatenarea mai multor criterii poate fi mai ușor de utilizat, însa aceasta cu criteriile logice este mult mai flexibilă si mai puternică.
În cele din urmă, poți decide ce variantă să utilizezi în funcție de tipul criteriilor care trebuie îndeplinite.
Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate de situațiile în care se poate folosi Xlookup.
