XLOOKUP(): Cea mai versatilă funcție de căutare din Excel

Dacă ai folosit vreodată VLOOKUP() și te-ai blocat pentru că aveai nevoie să cauți către stânga, XLOOKUP() rezolvă această problemă. Este funcția mai nouă de căutare din Excel. Poate înlocui VLOOKUP(), HLOOKUP() și chiar majoritatea formularelor INDEX() și MATCH() cu o singură formulă simplă.

XLOOKUP() poate căuta în orice direcție, poate returna mai mult de o coloană deodată și poate afișa un mesaj personalizat când o valoare lipsește. 

În acest ghid, vom parcurge mai întâi sintaxa. Apoi vom construi formule reale pas cu pas, folosind același set de date simplu pe tot parcursul. Vom acoperi și potrivirile exacte și aproximative, cum să returnezi mai mult de o coloană, cum să gestionezi erorile și cum se compară XLOOKUP() cu VLOOKUP() și INDEX()/MATCH(). 

Cum folosești XLOOKUP() în Excel

Hai să construim de la zero o formulă reală XLOOKUP(). 

Setul nostru de date are cinci produse, fiecare cu un ID, un nume, o categorie și un preț, din coloanele A până la D, cu antete pe rândul 1.

Set de date pentru XLOOKUP(). Imagine de autor.

Să zicem că vrem să găsim prețul pentru „P104”. Hai să parcurgem pașii, pe rând.

Caută o singură valoare

Fă clic pe o celulă goală unde vrei să apară rezultatul. Eu folosesc F2. Și tastează următoarea formulă:

=XLOOKUP("P104", A2:A6, D2:D6)

În această formulă: 

• „P104” este lookup_value, lucrul pe care l-am căutat

• A2:A6 este lookup_array, intervalul în care Excel a căutat

• D2:D6 este return_array, intervalul din care Excel a extras răspunsul

Excel caută în coloana A, „Product ID”, valoarea „P104”, o găsește pe rândul 5 și returnează valoarea corespunzătoare din coloana D: $45.

În loc să tastezi direct „P104”, poți referenția o celulă, astfel încât să poți schimba valoarea de căutare fără să editezi formula în sine. La noi, celula A5 conține „P104”, deci formula devine:

=XLOOKUP(A5, A2:A6, D2:D6)

Folosește XLOOKUP() în Excel. Imagine de autor.

Acum poți tasta orice ID de produs în A5, iar prețul se actualizează automat.

Returnează o valoare din altă coloană

Acest pas arată adevărata diferență dintre XLOOKUP() și VLOOKUP(). Nu ești constrâns să returnezi o valoare dintr-o coloană aflată la dreapta coloanei de căutare.

Să zicem că, în loc de preț, vrei categoria pentru „P102”. Aceeași structură, doar că indică return_array către coloana C, „Category” în schimb:

=XLOOKUP(A3, A2:A6, C2:C6)

Aici, A3 returnează Home. Dacă îl schimbi în A5, se actualizează la Electronics. În mod similar, schimbă din nou return_array ca să preiei numele produsului, prețul sau orice altă coloană, toate folosind același lookup_value și lookup_array.

XLOOKUP() returnează o valoare din altă coloană. Imagine de autor.

Cu VLOOKUP(), ai număra coloanele de la coloana de căutare și ai ajusta un index de coloană de fiecare dată când doreai un rezultat diferit. Cu XLOOKUP(), indici direct coloana dorită. 

Copiază formula pe mai multe rânduri

Până acum, am căutat câte o valoare pe rând. Dar majoritatea foilor reale trebuie să caute multe valori deodată, de exemplu o listă întreagă de ID-uri de comenzi, fiecare având nevoie de un preț corespunzător.

Iată cum setezi asta: Coloana A, începând cu A2, conține deja lista noastră de Product ID. Vrem ca prețurile corespunzătoare să apară lângă fiecare, în coloana F.

Pentru asta, fă clic pe F2 și tastează formula, referențiind A2 pentru valoarea de căutare, astfel:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Acum fă din nou clic pe F2, apoi trage mânerul de umplere (pătratul mic din colțul din dreapta-jos al celulei) în jos, prin restul rândurilor.

Excel copiază formula în jos, iar fiecare rând returnează acum prețul corect pentru propriul Product ID. ID-ul din A2 își primește prețul în F2, ID-ul din A3 în F3 și așa mai departe.

Copiază formula în jos în alte celule. Imagine de autor.

Observă semnele dolar din jurul A2:A6 și D2:D6. Aceasta se numește referință absolută. Blochează intervalul, astfel încât să nu se deplaseze când copiezi formula în jos. 

Asta contează aici, deoarece fiecare rând trebuie să caute în aceeași listă de produse, doar cu o valoare de căutare diferită de fiecare dată.

Cum folosești potrivirea exactă și aproximativă în XLOOKUP()

Implicit, XLOOKUP() caută o potrivire exactă. Dacă nu găsește una, returnează o eroare, cu excepția cazului în care ai configurat argumentul if_not_found despre care am vorbit mai devreme. 

Dar XLOOKUP() poate găsi și cea mai apropiată potrivire, folosind argumentul match_mode. 

Potrivire exactă (Implicit)

Nu trebuie să tastezi nimic în plus pentru asta. Fiecare formulă construită până acum folosește deja potrivire exactă, deoarece acesta este comportamentul implicit al XLOOKUP().

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel caută valoarea exactă din A2 și returnează un rezultat doar dacă găsește acea potrivire precisă undeva în intervalul de căutare.

Dacă vrei să o scrii explicit, adaugă un zero ca al patrulea argument:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() returnează o potrivire exactă. Imagine de autor.

Observi spațiul înainte de 0? Asta sare peste if_not_found, deoarece îl lăsăm gol aici, și trece direct la match_mode. De cele mai multe ori, poți omite complet acest argument și lăsa Excel să folosească implicit potrivirea exactă.

Potrivire aproximativă

Acum, să zicem că datele tale nu au o valoare pentru fiecare posibilă căutare. Gândește-te la o structură de comisioane în care agenții câștigă rate diferite în funcție de intervalul în care se încadrează vânzările totale. Nu vei avea un rând pentru fiecare sumă în dolari, ci doar punctul de start al fiecărui interval.

Aici ajută o potrivire aproximativă. 

Setează match_mode la -1, iar XLOOKUP() găsește cea mai mare valoare care este mai mică sau egală cu valoarea căutată.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Presupune că intervalul tău de căutare conține praguri de vânzări de $0, $5.000 și $10.000, fiecare legat de o rată de comision diferită. 

Un agent cu vânzări de $7.500 nu se potrivește exact cu niciun rând. Cu match_mode setat la -1, XLOOKUP() găsește $5.000, cel mai mare prag care nu depășește $7.500, și returnează rata de comision a acelui rând.

Setează match_mode la 1 în schimb, iar XLOOKUP() inversează logica. Găsește cea mai mică valoare care este mai mare sau egală cu valoarea căutată. Asta e util pentru lucruri precum pragurile de greutate la livrare, unde rotunjești în sus la nivelul următor, nu în jos.

XLOOKUP() returnează o potrivire aproximativă. Imagine de autor.

Notă: potrivirea aproximativă presupune că intervalul de căutare este sortat, crescător pentru -1, descrescător pentru 1. Dacă nu este sortat corect, XLOOKUP() poate returna un rezultat greșit fără să arunce o eroare, ceea ce e mai rău. Așadar, verifică întotdeauna ordinea de sortare înainte să te bazezi pe asta.

Cum cauți valori în stânga cu XLOOKUP()

VLOOKUP() caută doar în coloana din stânga a intervalului și returnează o valoare dintr-o coloană aflată la dreapta acesteia; nu poate invers. Al treilea său argument nici măcar nu este un interval; este un index de coloană numărat de la marginea stângă. 

Nu există nicio modalitate să-i spui „mergi o coloană la stânga”, așa că, dacă valoarea de care ai nevoie este la stânga coloanei de căutare, ești nevoit să-ți rearanjezi datele sau să adaugi o coloană ajutătoare doar ca să funcționeze căutarea.

XLOOKUP() nu are această restricție, deoarece îi indici intervale separate pentru căutare și pentru returnare.

Ia lista noastră de produse: Product ID este în coloana A, Product Name în coloana B. 

Presupune că ai un nume de produs și vrei să-i găsești ID-ul; opusul a ceea ce poate face VLOOKUP().

În C9, introdu Bluetooth Speaker (un nume existent în listă). În E2, introdu:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() caută în coloana B „Bluetooth Speaker”, îl găsește pe rândul 5 și returnează „P104” din coloana A, chiar dacă A este la stânga lui B. 

XLOOKUP() caută valorile din stânga, spre deosebire de VLOOKUP(). Imagine de autor.

Cum returnezi mai multe coloane cu XLOOKUP()

XLOOKUP() poate returna mai multe coloane deodată, într-o singură formulă, folosind o funcționalitate numită matrice dinamice (dynamic arrays). Pentru asta, indică return_array către un interval în loc de o singură coloană, iar XLOOKUP() „varsă” întregul rând corespunzător în atâtea celule câte sunt necesare. 

Hai să încercăm cu lista noastră de produse. 

În C9, tastez un Product ID, de exemplu, „P104”. În E2 introduc:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Observă că return_array aici este B2:D6, lat de trei coloane, acoperind Product Name, Category și Price. După ce apeși Enter, XLOOKUP() găsește „P104” în coloana A, apoi returnează Bluetooth Speaker, Electronics și $45 deodată, „revărsându-se” în E9, F9 și G9.

XLOOKUP() returnează mai multe coloane cu o singură formulă. Imagine de autor.

Asta înseamnă „spill range”. E9 are formula propriu-zisă, iar F9 și G9 se completează singure, fără propriile formule. 

Fă clic pe F9, iar bara de formule arată o versiune estompată a formulei originale, nu ceva tastat direct acolo. Încearcă să tastezi în F9 cât timp revărsarea din E9 este activă, iar Excel va arunca o eroare de spill. 

Vom acoperi cum să rezolvi asta în secțiunea Erori comune.

Cum gestionezi valorile lipsă în XLOOKUP()

Implicit, XLOOKUP() aruncă o eroare #N/A când nu găsește o potrivire. Argumentul if_not_found poate remedia asta afișând ce vrem noi în locul erorii implicite.  

Să presupunem că în C9 tastez un Product ID care nu există în datele mele, cum ar fi „P999”.

Returnarea unui mesaj personalizat

În G2, introduc:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

Deoarece „P999” nu există în coloana A, XLOOKUP() sare complet eroarea și returnează „Not Found”. Schimbă expresia cum vrei, de tipul „Check Product ID” sau „No Match”.

Afișează un mesaj personalizat în locul erorii implicite. Imagine de autor.

Returnarea unei celule goale

Uneori vrei o celulă goală, fără text. Pentru asta, folosește două ghilimele fără nimic între ele, astfel:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

Poți folosi asta când utilizezi formula într-un raport unde un „Not Found” rătăcit ar arăta inestetic lângă o coloană cu valori reale.

Afișează o celulă goală în locul erorii implicite. Imagine de autor.

Lăsarea ca eroare autentică

Dacă omiți complet if_not_found, XLOOKUP() revine la #N/A. Nu e întotdeauna un lucru rău. Dacă îți construiești o formulă pentru uz propriu și vrei să prinzi imediat un typo sau o lipsă, să vezi #N/A este mai util decât o celulă goală care ascunde problema.

Notă: if_not_found prinde doar potrivirile lipsă. Dacă lookup_array și return_array au dimensiuni diferite, de exemplu, XLOOKUP() tot aruncă o eroare #VALUE!, iar if_not_found nu o va opri pe aceasta. Vom acoperi cazul specific în secțiunea Erori comune mai jos.

Funcții avansate XLOOKUP()

Să vedem funcțiile avansate ale XLOOKUP()

Căutare de jos în sus

Setează search_mode la -1, iar XLOOKUP() scanează de la baza intervalului în sus, în loc de sus în jos. Folosește asta când datele au valori duplicate și vrei înregistrarea cea mai recentă, nu prima.

Să zicem că în setul meu de date am trei Customer ID duplicate. În celula E2, introduc formula implicită care va folosi abordarea de sus în jos: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

Aceasta se calculează de la începutul tabelului și afișează rezultatul de la prima potrivire. 

Dar dacă vreau să caut de la bază, voi folosi această formulă: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

În această formulă, -1 îi spune lui Excel să înceapă căutarea de la capătul de jos al setului de date. Și asta va returna 340, deoarece acela este primul rezultat care se potrivește pornind de jos.  

XLOOKUP() scanează datele de jos în sus. Imagine de autor.

Mod de căutare binară

Setează search_mode la 2, iar XLOOKUP() va trece de la verificarea rând cu rând la o căutare binară. 

Iată cum funcționează: 

• Se uită mai întâi la elementul din mijlocul intervalului. 

• Dacă nu este o potrivire, aruncă jumătatea în care valoarea nu poate fi, apoi se uită la mijlocul a ceea ce a rămas. 

• Continuă așa, înjumătățind datele de fiecare dată, până găsește potrivirea. 

• Setează search_mode la -2, și funcționează la fel, doar că pornește de la ultimul element, nu de la primul.

Aici, în E2, introdu:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

Această formulă returnează în schimb 200, un rând complet diferit.

Modul de căutare binară dă rezultat greșit. Imagine de autor.

Pentru că căutarea binară funcționează doar dacă lookup_array este sortat. Din moment ce coloana noastră Customer ID nu este sortată, este C100, C101, C100, C102, C100, iar căutarea binară aruncă jumătatea greșită și oferă o potrivire greșită. 

Acum să vedem ce se întâmplă când datele sunt de fapt sortate. 

Sortează același jurnal după Customer ID, crescător (toate cele trei C100 la început) și rulează din nou aceeași formulă. 

Modul de căutare binară funcționează cu date sortate. Imagine de autor.

De data asta, E2 returnează 120, potrivindu-se cu ceea ce ar da o căutare normală. Aceasta e diferența. Căutarea binară sare doar peste datele pe care le poate exclude în siguranță, iar asta e valabil doar când datele sunt sortate de la bun început. 

Formule XLOOKUP() imbricate

Imbrică un XLOOKUP() în altul atunci când rezultatul unei căutări trebuie să alimenteze o a doua căutare, în loc de un interval fix.

Să zicem că vrei să găsești un preț, dar coloana din care îl extragi depinde de o căutare separată, de exemplu nivelul de preț al clientului.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

În această formulă, XLOOKUP()-ul interior stabilește mai întâi din ce interval de coloană să extragă, pe baza lui D9, apoi XLOOKUP()-ul exterior folosește acel rezultat ca return_array. 

XLOOKUP() imbricat. Imagine de autor.

XLOOKUP() vs. VLOOKUP()

Am acoperit în detaliu cum funcționează XLOOKUP(). Iată comparația cu VLOOKUP().

Sunt două lucruri care merită menționate rapid: 

• VLOOKUP() are implicit potrivire aproximativă. Dacă uiți al patrulea argument, nu va arăta o eroare; doar va returna în liniște o valoare greșită.

• Dacă inserezi o coloană într-o foaie cu VLOOKUP(), formulele încep discret să extragă valoarea greșită. XLOOKUP() evită asta referențiind direct intervale, nu numărând coloane.

Nimic din toate acestea nu face ca VLOOKUP() să fie depășit. Încă funcționează bine pentru foi simple, statice. Dar pentru orice construiești de acum înainte, XLOOKUP() este alegerea mai sigură.

XLOOKUP() vs. INDEX() și MATCH()

Înainte să existe XLOOKUP(), INDEX() și MATCH() împreună erau metoda standard pentru a ocoli limitele VLOOKUP(). Folosește două funcții în loc de una. 

• MATCH() găsește poziția valorii căutate. 

• INDEX() folosește apoi acea poziție pentru a extrage rezultatul de oriunde indici: la stânga sau la dreapta, nu contează.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

Compară asta cu versiunea XLOOKUP() pe care am folosit-o pe tot parcursul articolului:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() funcționează doar în Microsoft 365 și Excel 2021 și versiunile ulterioare, cum am menționat mai devreme. Dacă registrul tău de lucru trebuie deschis în Excel 2019 sau 2016, sau îl partajezi cu persoane pe versiuni mai vechi, INDEX() și MATCH() încă funcționează peste tot.

Mai este și un caz mai rar. 

INDEX() și MATCH() separă „găsește poziția” de „returnează valoarea”, ceea ce ajută în unele formule complexe imbricate. Asta apare rar, deci majoritatea cititorilor nu vor avea nevoie.

În afara acestor două cazuri, XLOOKUP() este alegerea mai bună pentru un registru de lucru nou. 

Erori comune XLOOKUP() și cum le remediezi

Iată un ghid rapid pentru problemele cu care e cel mai probabil să te întâlnești și cum să le remediezi pe fiecare.

Erori #N/A

Aceasta este reacția implicită a XLOOKUP() când nu poate găsi o potrivire. M-am lovit de asta mai devreme, când lookup_value și lookup_array nu se aliniau, iar XLOOKUP() nu avea cu ce să potrivească în coloana A.

Pentru a remedia: Verifică de două ori că lookup_value chiar există în lookup_array, scris și formatat la fel. Dacă potrivirile lipsă sunt de așteptat și normale, folosește argumentul if_not_found pentru a înlocui #N/A cu ceva mai util, cum ar fi „Not Found” sau o celulă goală, cum am arătat mai devreme. 

Interval de căutare incorect

Se întâmplă când lookup_array și return_array nu se aliniază corect: rânduri greșite, coloane greșite sau intervale de dimensiuni diferite. 

În funcție de versiunea de Excel sau Sheets pe care o folosești, asta poate apărea ca o eroare #VALUE! sau uneori ca #N/A cu un mesaj separat care explică problema reală. Nu te lăsa păcălit de #N/A crezând că e o potrivire lipsă. 

Pentru a remedia: Asigură-te că lookup_array și return_array acoperă același număr de rânduri sau coloane. Dacă lookup_array este A2:A5, atunci return_array trebuie să cuprindă tot rândurile 2 până la 5, nu 4 până la 5 sau orice alt interval nealiniat.

Nepotriviri de tip de date

VLOOKUP() poate arunca o eroare pentru că se aștepta la un număr, dar a primit text. XLOOKUP() poate lovi o problemă similară. Dacă lookup_value este un număr, dar lookup_array stochează aceleași valori ca text, sau invers, XLOOKUP() nu va găsi o potrivire, chiar dacă valorile arată identic pe ecran.

Pentru a remedia: Verifică dacă lookup_value și lookup_array folosesc același tip de date. Numerele stocate ca text apar de obicei aliniate la stânga într-o celulă, nu la dreapta. Înfășoară lookup_value în VALUE() pentru a converti textul în număr sau folosește TEXT() pentru opusul, în funcție de direcția nepotrivirii.

Erori de „spill”

Asta se întâmplă când return_array acoperă mai mult de o coloană, dar celulele de care are nevoie XLOOKUP() nu sunt goale. Excel are nevoie de spațiu liber lângă celula cu formula pentru a vărsa rezultatele. Dacă e deja ceva acolo, întreaga formulă eșuează.

Pentru a remedia: Golește celulele în care trebuie să meargă rezultatele, apoi introdu formula. Dacă ai nevoie de ceva în acele celule, mută formula XLOOKUP() în altă parte, cu spațiu liber în jur.

Gânduri finale 

Acum știi să folosești XLOOKUP() pentru valori unice, mai multe coloane, date lipsă și chiar căutări imbricate între două intervale. E mult teren acoperit.

De acum înainte, fă din XLOOKUP() implicitul tău pentru orice registru nou. E mai flexibil decât VLOOKUP(), mai ușor de citit decât INDEX/MATCH() și te scutește să înfășori fiecare formulă în IFERROR().

Dacă vrei să mergi mai adânc, merită să înveți și INDEX() și MATCH() separat.