Funcția XLOOKUP – succesorul mai puternic al VLOOKUP

Xlookup

Mulți dintre noi ne-am lovit de situația în care am avut nevoie să extragem informații din tabele de date în funcție de o condiție.  În astfel de situații este necesar să folosim formulele de tip Lookup.

Dar ce formulă să folosești?

Vestea bună este că nu mai ai de ales – toate aceste formule pot  fi înlocuite cu succes de Xlookup, o formulă mult mai puternică si versatilă.

Poate te întrebi de ce este formula Xlookup mai bună? Aceasta este mai bună pentru că poate căuta informații atât vertical cât și orizontal, atât la stânga cât și la dreapta, poate căuta cu mai multe criterii și chiar poate returna o întreagă coloană sau un întreg rând de date, nu doar o singură valoare.

Puterea acestei formule va fi și mai clară pe măsură ce trecem prin câteva exemple XLOOKUP mai târziu în acest articol.

Definiție și sintaxa XLOOKUP

Formula Xlookup caută într-un interval de date o valoare specificată de utilizator și returnează valoarea aferentă dintr-o altă coloană.

Aceasta este disponibilă în Office365, Excel 2021 și Excel for the web.

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Dacă folosești Vlookup, poți observa că sintaxa este destul de similară, cu câteva caracteristici suplimentare. 

Primele trei argumente din formulă sunt obligatorii iar ultimele trei pot rămâne necompletate. 

  • Lookup_value – reprezintă ceea ce dorim să căutam în tabelul de date.  Lookup_value poate fi o valoare sau o referință.
  • Lookup_array – reprezintă intervalul de celule în care se realizează căutarea.
  • Return_array – reprezintă intervalul de celule din care dorești să fie extrasă informația corespunzătoare poziției în care este găsită valoarea de căutare.
  • [If_not_found] – reprezintă valoarea care dorești să fie afișată în cazul în care valoare de căutare nu este găsită în intervalul de date. În cazul în care acest argument nu este completat în formula, va fi returnată eroarea #N/A.
  • [Match_mode] – reprezintă o valoare logica prin care poți specifica tipul de căutare dorit:
      • 0 sau dacă este omis – Xlookup va realiza o căutare exactă.
      • -1 – la fel ca mai sus, se realizează o căutare exactă , dar dacă valoarea nu este găsită  va returna următorul element sau valoare mai mică găsită.
      • 1 – se realizează o căutare exactă , dar dacă valoarea nu este găsită  va returna următorul element sau valoare mai mare găsită.
      • 2 – se folosește pentru căutările parțiale folosind caracterele wildcard -> pentru mai multe informații despre ele acceseaza acest link.
  • [Search_mode] – prin acest argument alegi modalitatea în care formula xlookup va căuta în Lookup_array:
      • 1 sau dacă este omis – căutarea are loc de la prima celulă la ultima din intervalul de date selectat.
      • -1 – căutarea inversă, aceasta are loc de la ultima celulă la prima din intervalul de date selectat.
      • 2 – se efectuează o căutare binară în care datele trebuie sortate în ordine crescătoare. Dacă nu este sortat, acest lucru poate genera erori sau rezultate greșite.
      • -2 – se efectuează o căutare binară în care datele trebuie sortate în ordine descrescătoare. Dacă nu este sortat, acest lucru poate genera erori sau rezultate greșite.

Potrivit Microsoft, căutarea binară este inclusă pentru utilizatorii avansați. Este un algoritm special care găsește poziția unei valori de căutare într-o matrice sortată comparând-o cu elementul din mijloc al matricei. O căutare binară este mult mai rapidă decât o căutare obișnuită, dar funcționează corect numai pe date sortate.

Cum se utilizează Xlookup? (exemple)

Acum să trecem la partea interesantă – câteva exemple practice Xlookup.

Aceste exemple te vor ajuta să înțelegi mai bine cum funcționează Xlookup, cum este diferit de Vlookup și Index+Match

Să presupunem că ai următoarea bază de date cu performanța țărilor participante la toate jocurile olimpice:

 
Informațiile din tabel sunt sortate crescător în funcție de numele țării.

Pentru a descărca fișierul cu exemple folosește butonul de mai jos:

Download

Exemplul 1: Extragem o informație în funcție de o valoare de căutare

.

Dorești să afli în ce an a participat prima oară România la jocurile olimpice.

Mai jos este formula care face acest lucru:

=XLOOKUP(M7,DataSet[Tara],DataSet[An prima participare])

Formula de mai sus folosește numai argumentele obligatorii în care caută numele țării completat in celula M7 (de sus în jos), găsește o potrivire exactă în coloana Tara din tabel și returnează valoarea corespunzătoare din coloana An prima participare.

Exemplul 2: Extragem o informație în funcție de o valoare de căutare

.

În acest exemplu pornim de la tabel anterior, iar de această dată tabelul conține și coloana Rank după nr total de medalii.

Acum dorești să afișezi țările care sunt în top 10 după numărul total de medalii.

Mai jos este formula care face acest lucru:

=XLOOKUP(M8:M17,DataSet2[Rank dupa nr total de medalii],DataSet2[Tara])

Și de data aceasta am folosit numai argumentele obligatorii. Însă acum am folosit funcționalitatea Dynamic Arrays selectând la lookup_value toate cele 10 celule de la M8:M17. O altă diferență față de primul exemplu este faptul că în tabelul nostru coloana selectată la Lookup_Array Rank dupa nr total de medalii este după cea din care extragem rezultatele Tara.

Exemplul 3: Extragem înregistrări din mai multe coloane

.

Pentru acest exemplu vom folosi baza de date inițială.

De aceasta dată dorești să extragi numărul medaliilor de Aur, Argint și Bronz pentru o țară dată.

Pentru această situație vei folosi următoarea formulă:

=XLOOKUP(M7,DataSet3[Tara],DataSet3[[Aur]:[Bronz]])

Formula de mai sus folosește pentru return_array un interval de date de 3 coloane (de la coloana Aur la Bronz) în loc de una singură. De aceea, rezultatul este extras și afișat pe 3 coloane în funcție de țara completată în celula M7 .

Exemplul 4: Extragem o informație folosind o căutare orizontală și verticală

.

În acest exemplu dorești să extragi numărul de medalii de un tip dat (Aur, Argint sau Bronz) câștigat de o țară dată. 

Pentru acest lucru este necesar să construiești o formulă care îi oferă utilizatorului flexibilitatea de a alege atât țara cât și tipul medaliei.

=XLOOKUP(M8,DataSet4[[#Headers],[Aur]:[Bronz]],XLOOKUP(L9,DataSet4[Tara],DataSet4[[Aur]:[Bronz]]))

Această formulă folosește o combinație de două formule Xlookup

Cel de-al doilea XLOOKUP(L9,DataSet4[Tara],DataSet4[[Aur]:[Bronz]])) extrage numărul de medalii pentru țara completată în celula L9. Rezultatul acestui Xlookup este array-ul {152, 168, 192}

Acest rezultat este folosit acum în primul Xlookup la argumentul return_array și de data aceasta formula caută tipul medaliei completat în celula M8. Căutarea are loc în intervalul de date DataSet4[[#Headers],[Aur]:[Bronz]].

Pentru că în celula M8 este completat Argint, acest Xlookup extrage a doua valoare din return_array {152, 168, 192} => 168.

În următorul video poți vedea flexibilitatea pe care o are această combinație de xlookup-uri: 

Exemplul 5: Când valoarea de căutare nu este găsită (gestionarea erorilor)

.

După cum ai văzut în sintaxa formulei Xlookup, aceasta include un argument adăugat special pentru a te ajuta în gestionarea erorilor.

Acesta este cel de-al patrulea argument [if_not_found], unde poți specifica ceea dorești să returneze formula în cazul în care valoarea căutată nu poate fi găsită în tabel.

Avem aceeași situație de la primul exemplu în care dorești să afli în ce an a participat prima oară o țară dată la jocurile olimpice. Iar dacă țara completateă nu se găsește în tabel vrei să fie afișat mesajul “Completează numele țării în engleză”.

Mai jos este formula care face acest lucru:

=XLOOKUP(M7,DataSet5[Tara],DataSet5[An prima participare],"Completează numele țării în engleză")

În acest caz, am scris direct în formulă textul afișat atunci când  nu există nicio potrivire. De asemenea, la argumentul [if_not_found] se poate utiliza o referință la o celulă sau o formulă.

Exemplul 6: Extragem informații folosind căutarea după ultima valoare potrivită

.

De data aceasta vom presupune că, în cadrul familiei, folosiți un tabel în care vă gestionați bugetul de venituri și cheltuieli.

Folosind acest tabel dorești să afli cine a realizat ultima cheltuială din categoria Divertisment.

Următoarea formulă va căuta ultima valoare pentru categoria notată în celula K7 și va afișa numele persoanei care a realizat aceea cheltuială.

=XLOOKUP(K7,DataSet6[Categorie],DataSet6[Titular],,,-1)

De asemenea, pe același principiu poți scrie încă un Xlookup pentru a extrage și data în care a avut loc cheltuiala respectivă.

=XLOOKUP(K7,DataSet6[Categorie],DataSet6[Data],,,-1)
Xlookup Exemplu cautare dupa ultima potrivire
Xlookup Exemplu cautare dupa ultima potrivire

Exemplul 7: Extragem informații folosind o căutare bazată pe potrivirea aproximativă

.

Ne întoarcem la baza de date inițială, cu performantă țărilor participante la toate jocurile olimpice.

Dorești să afli ce țară se apropie cel mai mult 500 de medalii de aur, dar nu mai mult de 500.

Mai jos este formula care face acest lucru:

=XLOOKUP(M7,DataSet7[Aur],DataSet7[Tara],,-1)
În această formulă, am folosit -1 ca al cincilea argument ([match_mode]), ceea ce înseamnă că Xlookup va căuta o potrivire exactă, iar când nu găsește una, va returna valoarea mai mică decât valoarea de căutare.

Beneficiile utilizării Xlookup

După ce ai trecut prin exemplele de mai sus cu siguranță ai observat că Xlookup aduce multe beneficii față de celelalte formule de tip lookup. 

1
Căutarea de la dreapta spre stânga

O diferență evidentă dintre Xlookup și Vlookup este modul în care este gestionat argumentul Lookup_array. Când folosești Vlookup la argumentul Lookup_array este nevoie să selectezi un interval de date care conține mai multe coloane, unde valoarea de căutare este în coloana din stânga și apoi să specifici numărul coloanei de unde dorești să extragi rezultatul.

Xlookup, pe de altă parte, îți permite să alegi separat lookup_array și return_array. Astfel, Xlookup vine cu un beneficiu instantaneu pentru că acum poți realiza căutarea de la dreapta spre stânga.

2
Inserări/ștergeri de coloane

Xlookup rezolvă o altă problemă majoră. În cazul în care inserezi o nouă coloană, formula nu este afectată și rezultate afișate sunt în continuare corecte. Dacă ai folosi Vlookup, în astfel de cazuri aceasta va returna o eroare sau un rezultat incorect, deoarece de cele mai multe ori valoarea indexului coloanei este fixă în cadrul formulei.

3
Gestionarea erorilor în cadrul formulei

Dacă înainte era nevoie să incluzi Vlookup într-un Iferror pentru a gestiona situațiile care ar putea genera erori, acum ai văzut in exemplul 5 că poți realiza acest lucru direct în Xlookup.

4
Caută potrivirea exactă în mod implicit

Una dintre neplăcerile Vlookup-ului este că trebuie menționat FALSE sau 0 ca ultim argument pentru a obține rezultatul în urma unei căutări exacte. Xlookup rezolvă acest lucru având potriviri exacte în mod implicit. Poți utiliza argumentul [match_mode] pentru a modifica comportamentul de căutare.

5
Performanță mai bună

Vlookup poate încetini fișierele excel, deoarece include întregul tabel în calcule, ceea ce duce la procesarea mult mai multor celule decât este necesar. În schimb, Xlookup gestionează numai matricele de căutare și returnare.

Xlookup Compatibilitate cu versiunile Excel anterioare

Acesta este un lucru la care trebuie să fii atent – XLOOKUP NU este compatibil cu versiunile anterioare ale Excelului.

Aceasta înseamnă că dacă creezi un fișier și utilizezi formula Xlookup și apoi îl deschizi într-o versiune Excel care nu are Xlookup, va afișa erori.

Concluzie

Având în vedere că Xlookup este un pas uriaș înainte, cred că aceasta va deveni formula de căutare folosită implicit de mulți dintre noi. Însă pentru că este disponibilă doar în trei versiuni Excel va mai dura câțiva ani până să fie adoptată pe scară largă. 

Pentru mine Vlookup a fost un vechi însoțitor de încredere și, recunosc, că îl mai folosesc pentru căutări rapide. Dar Xlookup este, fără îndoială, o îmbunătățire mare și care era foarte necesară.

chat

Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate de Xlookup.

One thought on “Funcția XLOOKUP – succesorul mai puternic al VLOOKUP

  1. Adriana says:

    Câte feluri de a folosi xlookup! Știam de cea basic, din primele 2 exemple, am mai folosit-o, dar celelalte exemple sunt tare utile și interesante!

Leave a Reply

Your email address will not be published. Required fields are marked *