Funcția XLOOKUP

Utilizați funcția XLOOKUP pentru a găsi lucruri într-un tabel sau într-o zonă după rând. De exemplu, căutați prețul unei piese auto după numărul piesei sau găsiți un nume de angajat pe baza ID-ului său de angajat. Cu XLOOKUP, puteți să căutați într-o coloană un termen de căutare și să returnați un rezultat din același rând din altă coloană, indiferent de partea pe care se află coloana returnată.

Notă: XLOOKUP nu este disponibil în Excel 2016 și Excel 2019. Cu toate acestea, este posibil să întâlniți o situație de utilizare a unui registru de lucru în Excel 2016 sau Excel 2019 cu funcția XLOOKUP în acesta, dacă a fost creat de altcineva utilizând o versiune mai nouă de Excel.

Browserul nu acceptă redarea video. Instalați Microsoft Silverlight, Adobe Flash Player sau Internet Explorer 9.

Sintaxă

Funcția XLOOKUP caută într-o zonă sau într-o matrice, apoi returnează elementul corespunzător primei potriviri pe care o găsește. Dacă nu există nicio potrivire, XLOOKUP poate returna cea mai apropiată potrivire (aproximativă).

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

Argument	Descriere
lookup_value Necesar*	Valoarea de căutat Dacă este omis, XLOOKUP returnează celulele necompletate pe care le găsește în lookup_array*.
matrice_căutare Obligatoriu	Matricea sau intervalul de căutat
return_array Obligatoriu	Matricea sau zona de returnat
[if_not_found] Opțional	Dacă nu se găsește o potrivire validă, returnați textul [if_not_found] pe care îl furnizați. Dacă nu se găsește o potrivire validă și lipsește [if_not_found], se returnează #N/A .
[match_mode] Opțional	Specificați tipul de potrivire: 0 - Potrivire exactă. Dacă nu se găsește niciuna, returnați #N/A. Aceasta este opțiunea implicită. -1 - Potrivire exactă. Dacă nu s-a găsit niciuna, returnați următorul element mai mic. 1 - Potrivire exactă. Dacă nu s-a găsit niciuna, returnați următorul element mai mare. 2 - O potrivire cu caractere wildcard în care *, ? și ~ au semnificație specială.
[search_mode] Opțional	Specificați modul de căutare de utilizat: 1 - Efectuați o căutare începând de la primul element. Aceasta este opțiunea implicită. -1 - Efectuați o căutare inversă începând de la ultimul element. 2 - Efectuați o căutare binară care se bazează pe lookup_array sortate în ordine ascendentă . Dacă nu sunt sortate, vor fi returnate rezultate nevalide. -2 - Efectuați o căutare binară care se bazează pe sortarea lookup_array în ordine descrescătoare. Dacă nu sunt sortate, vor fi returnate rezultate nevalide.

Argument

Descriere

lookup_value

Necesar*

Valoarea de căutat *Dacă este omis, XLOOKUP returnează celulele necompletate pe care le găsește în lookup_array.

matrice_căutare

Obligatoriu

Matricea sau intervalul de căutat

return_array

Obligatoriu

Matricea sau zona de returnat

[if_not_found]

Opțional

Dacă nu se găsește o potrivire validă, returnați textul [if_not_found] pe care îl furnizați.

Dacă nu se găsește o potrivire validă și lipsește [if_not_found], se returnează #N/A .

[match_mode]

Opțional

Specificați tipul de potrivire:

0 - Potrivire exactă. Dacă nu se găsește niciuna, returnați #N/A. Aceasta este opțiunea implicită.

-1 - Potrivire exactă. Dacă nu s-a găsit niciuna, returnați următorul element mai mic.

1 - Potrivire exactă. Dacă nu s-a găsit niciuna, returnați următorul element mai mare.

2 - O potrivire cu caractere wildcard în care *, ? și ~ au semnificație specială.

[search_mode]

Opțional

Specificați modul de căutare de utilizat:

1 - Efectuați o căutare începând de la primul element. Aceasta este opțiunea implicită.

-1 - Efectuați o căutare inversă începând de la ultimul element.

2 - Efectuați o căutare binară care se bazează pe lookup_array sortate în ordine ascendentă . Dacă nu sunt sortate, vor fi returnate rezultate nevalide.

-2 - Efectuați o căutare binară care se bazează pe sortarea lookup_array în ordine descrescătoare. Dacă nu sunt sortate, vor fi returnate rezultate nevalide.

Exemple

Exemplul 1 utilizează XLOOKUP pentru a căuta un nume de țară dintr-un interval, apoi a returna codul de țară al telefonului. Include argumentele lookup_value (celula F2), lookup_array (zona B2:B11) și return_array (zona D2:D11). Nu include argumentul match_mode , deoarece XLOOKUP produce o potrivire exactă în mod implicit.

Exemplu de funcție XLOOKUP utilizată pentru a returna un nume de angajat și un departament bazat pe ID-ul angajatului. Formula este =XLOOKUP(B2,B5:B14,C5:C14)

Notă: XLOOKUP utilizează o matrice de căutare și o matrice de returnare, în timp ce VLOOKUP utilizează o singură matrice de tabel, urmată de un număr de index de coloană. Formula VLOOKUP echivalentă în acest caz ar fi: =VLOOKUP(F2,B2:D11,3,FALSE)

———————————————————————————

Exemplul 2 caută informații despre angajați pe baza unui număr ID de angajat. Spre deosebire de VLOOKUP, XLOOKUP poate returna o matrice cu mai multe elemente, astfel încât o singură formulă să poată returna atât numele angajatului, cât și departamentul din celulele C5:D14.

Exemplu de funcție XLOOKUP utilizată pentru a returna un nume de angajat și un departament bazat pe ID angajat. Formula este: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

———————————————————————————

Exemplul 3 adaugă un argument if_not_found la exemplul precedent.

Exemplu de funcție XLOOKUP utilizată pentru a returna un nume de angajat și un departament bazat pe ID angajat cu argumentul if_not_found. Formula este =XLOOKUP(B2,B5:B14,C5:D14,0,1,"Angajat negăsit")

———————————————————————————

Exemplul 4 caută venitul personal introdus în celula E2 în coloana C și găsește o rată de impozitare corespunzătoare în coloana B. Setează argumentul if_not_found să returneze 0 (zero) dacă nu se găsește nimic. Argumentul match_mode este setat la 1, ceea ce înseamnă că funcția va căuta o potrivire exactă și, dacă nu găsește una, returnează următorul element mai mare. În sfârșit, argumentul search_mode este setat la 1, ceea ce înseamnă că funcția va căuta de la primul element la ultimul.

Imagine cu funcția XLOOKUP utilizată pentru a returna o rată de impozitare bazată pe venitul maxim. Aceasta este o potrivire aproximativă. Formula este: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

Notă: Coloana de lookup_array a XARRAY se află la dreapta coloanei return_array , în timp ce VLOOKUP poate căuta doar de la stânga la dreapta.

———————————————————————————

Exemplul 5 utilizează o funcție XLOOKUP imbricată pentru a efectua atât o potrivire verticală, cât și o potrivire orizontală. Mai întâi caută Profitul brut în coloana B, apoi caută Trim1 în rândul de sus al tabelului (zona C5:F5) și, în cele din urmă, returnează valoarea de la intersecția celor două. Acest lucru este similar cu utilizarea funcțiilor INDEX și MATCH împreună.

Sfat: De asemenea, puteți utiliza XLOOKUP pentru a înlocui funcția HLOOKUP .

Imagine cu funcția XLOOKUP utilizată pentru a returna date orizontale dintr-un tabel imbricând 2 XLOOKUP. Formula este: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))

Notă: Formula din celulele D3:F3 este: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17)))

———————————————————————————

Exemplul 6 utilizează funcția SUM și două funcții XLOOKUP imbricate pentru a însuma toate valorile dintre două zone. În acest caz, dorim să însumăm valorile pentru struguri, banane și să includem pere, care se află între cele două.

Utilizarea XLOOKUP cu SUM pentru a totalizării unei zone de valori care se încadrează între două selecții

Formula din celula E3 este: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

Cum funcționează? XLOOKUP returnează o zonă, așadar, atunci când calculează, formula ajunge să arate astfel: =SUM($E$7:$E$9). Puteți vedea singur cum funcționează aceasta, selectând o celulă cu o formulă XLOOKUP similară cu aceasta, apoi selectați Formule > Audit formule > Evaluare formulă, apoi selectați Evaluare pentru a parcurge calculul.

Notă: Vă mulțumim pentru MVP Microsoft Excel, Bill Jelen, pentru că ați sugerat acest exemplu.

———————————————————————————

Consultați și

Puteți oricând să întrebați un expert din Comunitatea tehnică Excel sau să obțineți asistență în Comunități.

Funcția XMATCH

Funcții Excel (în ordine alfabetică)

Funcții Excel (după categorie)

Sintaxă

Exemple

Consultați și

Aveți nevoie de ajutor suplimentar?

Doriți mai multe opțiuni?

Au fost utile aceste informații?

Vă mulțumim pentru feedback!