Funcția XLOOKUP se utilizează 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ă codul produsului sau găsiți numele unui angajat pe baza ID-ului său de angajat. Cu XLOOKUP, puteți căuta într-o coloană un termen de căutare și puteți returna un rezultat din același rând din altă coloană, indiferent de partea pe care se află coloana de returnare.
Notă
XLOOKUP nu este disponibil în Excel 2016 și Excel 2019. Totuși, 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 el, dacă acesta a fost creat de altcineva utilizând o versiune mai nouă de Excel.
Sintaxă
Funcția XLOOKUP caută într-o zonă sau 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 Obligatoriu* |
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 niciunul, returnează #N/A. Aceasta este opțiunea implicită. -1 - Potrivire exactă. Dacă nu a fost găsit niciunul, returnați următorul element mai mic. 1 - Potrivire exactă. Dacă nu a fost găsit niciunul, 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 sortarea lookup_array în ordine crescătoare . 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ă într-o zonă, apoi returnează codul de țară de telefon al acesteia. Aceasta include argumentele lookup_value (celula F2), lookup_array (zona B2:B11) și return_array (zona D2:D11). Acesta nu include argumentul match_mode , deoarece XLOOKUP produce o potrivire exactă în mod implicit.
Notă
XLOOKUP utilizează o matrice de căutare și o matrice returnată, în timp ce VLOOKUP utilizează o matrice de tabel unică urmată de un index de coloană. Formula VLOOKUP echivalentă în acest caz ar fi: =VLOOKUP(F2,B2:D11,3,FALSE)
———————————————————————————
Exemplul 2 caută informații despre un angajat pe baza numărului ID al unui angajat. Spre deosebire de VLOOKUP, XLOOKUP poate returna o matrice cu mai multe elemente, astfel că o singură formulă poate returna atât numele angajatului, cât și departamentul din celulele C5:D14.
———————————————————————————
Exemplul 3 adaugă un argument if_not_found la exemplul anterior.
———————————————————————————
Exemplul 4 caută în coloana C venitul personal introdus în celula E2 ș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 , ceea ce înseamnă că funcția va căuta 1o potrivire exactă și, dacă nu găsește una, returnează următorul element mai mare. În cele din urmă, argumentul search_mode este setat la 1, ceea ce înseamnă că funcția va căuta de la primul element la ultimul.
Notă
Coloana lookup_array a XARRAY se află în partea dreaptă a coloanei return_array , în timp ce VLOOKUP poate să privească 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ă. Aceasta caută mai întâi Profitul brut în coloana B, apoi caută Trim1 în rândul de sus al tabelului (zona C5:F5) și, la final, 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 .
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 din două zone. În acest caz, dorim să însumăm valorile pentru struguri, banane și să includem perele, care se află între cele două.
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ă, astfel încât atunci când calculează, formula sfârșește prin a arăta astfel: =SUM($E$7:$E$9). Puteți vedea cum funcționează această idee pe cont propriu, selectând o celulă cu o formulă XLOOKUP asemănătoare acesteia, apoi selectați Formule>Audit Evaluare>formulă și apoi selectați Evaluare pentru a parcurge pas cu pas calculul.
Notă
Mulțumim MVP-ului Microsoft Excel, Bill Jelen, pentru că a sugerat acest exemplu.
———————————————————————————