Funcția Excel XLOOKUP: Tot ce trebuie să știți (10 exemple)

Vizionați video - Funcția XLOOKUP Excel (10 exemple XLOOKUP)

excela Funcția XLOOKUP a sosit în sfârșit.

Dacă ați folosit VLOOKUP sau INDEX / MATCH, sunt sigur că vă va plăcea flexibilitatea pe care o oferă funcția XLOOKUP.

În acest tutorial, voi acoperi tot ce trebuie să știți despre funcția XLOOKUP și câteva exemple care vă vor ajuta să știți cum să o utilizați cel mai bine.

Asadar, haideti sa începem!

Ce este XLOOKUP?

XLOOKUP este o funcție nouă Office 365 și este o versiune nouă și îmbunătățită a funcției VLOOKUP / HLOOKUP.

Face tot ce făcea VLOOKUP și multe altele.

XLOOKUP este o funcție care vă permite să căutați rapid o valoare într-un set de date (vertical sau orizontal) și să returnați valoarea corespunzătoare în alte rânduri / coloane.

De exemplu, dacă ați obținut scorurile pentru studenți la un examen, puteți utiliza XLOOKUP pentru a verifica rapid cât a obținut un student folosind numele studentului.

Puterea acestei funcții va deveni și mai clară pe măsură ce mă adânc în unele Exemple XLOOKUP mai târziu în acest tutorial.

Dar, înainte de a intra în exemple, există o întrebare mare - cum obțin acces la XLOOKUP?

Cum să obțineți acces la XLOOKUP?

De acum, XLOOKUP este disponibil numai pentru utilizatorii Office 365.

Deci, dacă utilizați versiuni anterioare de Excel (2010/2013/2016/2019), nu veți putea utiliza această funcție.

De asemenea, nu sunt sigur dacă acest lucru va fi lansat vreodată pentru versiunile anterioare sau nu (poate Microsoft poate crea un add-in așa cum au făcut-o pentru Power Query). Dar, de acum, îl puteți folosi numai dacă sunteți în Office 365.

Faceți clic aici pentru a face upgrade la Office 365

În cazul în care sunteți deja în Office 365 (ediția Home, Personal sau University) și nu aveți acces la acesta, puteți accesa fila File și apoi faceți clic pe Cont.

Ar exista un program Office Insider și puteți face clic și adera la Programul Office Insider. Acest lucru vă va oferi acces la funcția XLOOKUP.

Mă aștept ca XLOOKUP să fie disponibil în curând pe toate versiunile Office 365.

Notă: XLOOKUP este disponibil și pentru Office 365 pentru Mac și Excel pentru web (Excel online)

Sintaxa funcției XLOOKUP

Mai jos este sintaxa funcției XLOOKUP:

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

Dacă ați folosit VLOOKUP, veți observa că sintaxa este destul de similară, cu câteva caracteristici suplimentare minunate, desigur.

Nu vă faceți griji dacă sintaxa și argumentul arată prea mult. Le acoper cu câteva exemple ușoare de XLOOKUP mai târziu în acest tutorial, care vor face ca cristalul să fie clar.

Funcția XLOOKUP poate povesti 6 argumente (3 obligatorii și 3 opționale):

1. valoare_cercare - valoarea pe care o căutați
2. lookup_array - matricea în care căutați valoarea de căutare
3. return_array - matricea din care doriți să preluați și să returnați valoarea (corespunzătoare poziției în care se găsește valoarea de căutare)
4. [if_not_found] - valoarea de returnat în cazul în care valoarea de căutare nu este găsită. În cazul în care nu specificați acest argument, va fi returnată o eroare # N / A
5. [match_mode] - Aici puteți specifica tipul de potrivire dorit:
   • 0 - Potrivire exactă, în care valoarea de căutare ar trebui să se potrivească exact cu valoarea din matricea de căutare. Aceasta este opțiunea implicită.
   • -1 - Caută potrivirea exactă, dar dacă este găsită, returnează următorul articol / valoare mai mică
   • 1 - Caută potrivirea exactă, dar dacă este găsită, returnează următorul articol / valoare mai mare
   • 2 - Pentru a face potrivire parțială folosind metacaractere (* sau ~)
6. [search_mode] - Aici specificați cum funcția XLOOKUP ar trebui să caute în lookup_array
   • 1 - Aceasta este opțiunea implicită în care funcția începe să caute valoarea de căutare din partea de sus (primul articol) până în partea de jos (ultimul articol) din tabela de căutare
   • -1 - Caută de jos în sus. Util atunci când doriți să găsiți ultima valoare potrivită în lookup_array
   • 2 - Efectuează o căutare binară în care datele trebuie să fie sortate în ordine crescătoare. Dacă nu este sortat, acest lucru poate da erori sau rezultate greșite
   • -2 - Efectuează o căutare binară în care datele trebuie să fie sortate în ordine descrescătoare. Dacă nu este sortat, acest lucru poate da erori sau rezultate greșite

Exemple de funcții XLOOKUP

Acum să trecem la partea interesantă - câteva exemple practice XLOOKUP.

Aceste exemple vă vor ajuta să înțelegeți mai bine cum funcționează XLOOKUP, diferența dintre VLOOKUP și INDEX / MATCH și câteva îmbunătățiri și limitări ale acestei funcții.

Faceți clic aici pentru a descărca fișierul de exemplu și a continua

Exemplul 1: Obțineți o valoare de căutare

Să presupunem că aveți următorul set de date și doriți să obțineți scorul matematic pentru Greg (valoarea de căutare).

Mai jos este formula care face acest lucru:

= XLOOKUP (F2, A2: A15, B2: B15)

În formula de mai sus, tocmai am folosit argumentele obligatorii unde caută numele (de sus în jos), găsește o potrivire exactă și returnează valoarea corespunzătoare din B2: B15.

O diferență evidentă pe care o au funcțiile XLOOKUP și VLOOKUP este modul în care gestionează matricea de căutare. În VLOOKUP, aveți întreaga matrice în care se află valoarea de căutare în coloana din stânga și apoi specificați numărul coloanei de unde doriți să obțineți rezultatul. XLOOKUP, pe de altă parte, vă permite să alegeți lookup_array și return_array separat

Un avantaj instantaneu de a avea lookup_array și return_array ca argumente separate înseamnă că acum puteți uită-te în stânga. VLOOKUP avea această limitare în care puteți doar să căutați și să găsiți o valoare care este în dreapta. Dar cu XLOOKUP, această limitare a dispărut.

Iată un exemplu. Am același set de date, unde numele este în dreapta și return_range este în stânga.

Mai jos este formula pe care o pot folosi pentru a obține scorul pentru Greg în matematică (ceea ce înseamnă căutarea în stânga valorii de căutare)

= XLOOKUP (F2, D2: D15, A2: A15)

XLOOKUP rezolvă o altă problemă majoră - În cazul în care introduceți o coloană nouă sau mutați coloanele, datele rezultate ar fi în continuare corecte. VLOOKUP probabil ar rupe sau ar da un rezultat incorect în astfel de cazuri, deoarece de cele mai multe ori valoarea indexului coloanei este codificată.

Exemplul 2: Căutați și extrageți o înregistrare întreagă

Să luăm aceleași date ca un exemplu.

În acest caz, nu vreau să aduc doar scorul lui Greg în matematică. Vreau să obțin scorurile la toate subiectele.

În acest caz, pot folosi formula de mai jos:

= XLOOKUP (F2, A2: A15, B2: D15)

Formula de mai sus utilizează un interval return_array care este mai mult decât o coloană (B2: D15). Deci, atunci când valoarea de căutare se găsește în A2: A15, formula returnează întregul rând din return_array.

De asemenea, nu puteți șterge doar celulele care fac parte din matrice care au fost populate automat. În acest exemplu, nu puteți șterge H2 sau I2. Dacă încercați, nu s-ar întâmpla nimic. Dacă selectați aceste celule, formula din bara de formule va fi gri (indicând că nu poate fi modificată)

Puteți șterge formula din celula G2 (unde am introdus-o inițial), va șterge întregul rezultat.

Aceasta este o îmbunătățire utilă, deoarece mai devreme cu VLOOKUP, va trebui să specificați separat numărul coloanei pentru fiecare formulă.

Exemplul 3: Căutare bidirecțională utilizând XLOOKUP (căutare orizontală și verticală)

Mai jos este un set de date în care vreau să știu scorul lui Greg în matematică (subiectul din celula G2).

Acest lucru se poate face folosind o căutare bidirecțională în care caut numele din coloana A și numele subiectului din rândul 1. Avantajul acestei căutări bidirecționale este că rezultatul este independent de numele studentului al numelui subiectului. Dacă schimb numele subiectului în Chimie, această formulă XLOOKUP bidirecțională ar funcționa și îmi va da rezultatul corect.

Mai jos este formula care va efectua căutarea bidirecțională și va da rezultatul corect:

= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))

Această formulă utilizează un XLOOKUP imbricat, unde mai întâi îl folosesc pentru a prelua toate semnele elevului în celula F2.

Deci, rezultatul XLOOKUP (F2, A2: A15, B2: D15) este {21,94,81}, care este o matrice de note obținute de Greg în acest caz.

Acesta este apoi utilizat din nou în formula XLOOKUP exterioară ca matrice de returnare. În formula exterioară XLOOKUP, caut numele subiectului (care se află în celula G1) și matricea de căutare este B1: D1.

Dacă numele subiectului este Math, această formulă externă XLOOKUP preia prima valoare din matricea returnată - care este {21,94,81} în acest exemplu.

Acest lucru face același lucru, până acum, realizat folosind combo-ul INDEX și MATCH

Faceți clic aici pentru a descărca fișierul de exemplu și a continua

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

Tratarea erorilor a fost acum adăugată la formula XLOOKUP.

Al patrulea argument din funcția XLOOKUP este [if_not_found], unde puteți specifica ce doriți în cazul în care căutarea nu poate fi găsită.

Să presupunem că aveți setul de date așa cum se arată mai jos, unde doriți să obțineți scorul Math în cazul în care se potrivește și în cazul în care numele nu este găsit, doriți să reveniți - „Nu a apărut”

Formula de mai jos va face acest lucru:

= XLOOKUP (F2, A2: A15, B2: B15, „Nu a apărut”)

În acest caz, am codificat greu ceea ce vreau să obțin în cazul în care nu există nicio potrivire. De asemenea, puteți utiliza o referință de celulă la o celulă sau o formulă.

Exemplul 5: XLOOKUP imbricat (căutare în mai multe game)

Geniul de a avea argumentul [if_not_found] este că vă permite să utilizați formula XLOOKUP imbricată.

De exemplu, să presupunem că aveți două liste separate așa cum se arată mai jos. Deși am aceste două tabele pe aceeași foaie, le puteți avea în foi separate sau chiar în registre de lucru.

Mai jos este formula imbricată XLOOKUP care va verifica numele în ambele tabele și va returna valoarea corespunzătoare din coloana specificată.

= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))

În formula de mai sus, am folosit argumentul [if_not_found] pentru a utiliza o altă formulă XLOOKUP. Acest lucru vă permite să adăugați al doilea XLOOKUP în aceeași formulă și să scanați două tabele cu o singură formulă.

Nu sunt sigur câte XLOOKUP-uri imbricate puteți utiliza într-o formulă. Am încercat până la 10 și a funcționat, apoi am renunțat la 🙂

Exemplul 6: Găsiți ultima valoare de potrivire

Aceasta era foarte necesară, iar XLOOKUP a făcut acest lucru posibil. Acum nu este nevoie să găsiți modalități complicate de a obține ultima valoare potrivită dintr-un interval.

Să presupunem că aveți setul de date așa cum se arată mai jos și doriți să verificați când a fost ultima persoană angajată în fiecare departament și care a fost data angajării.

Formula de mai jos va căuta ultima valoare pentru fiecare departament și va da numele ultimei angajări:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,, - 1)

Iar formula de mai jos va indica data angajării ultimei angajări pentru fiecare departament:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,, - 1)

Deoarece XLOOKUP are o caracteristică încorporată pentru a specifica direcția de căutare (de la primul până la ultimul sau de la ultimul la primul), acest lucru se face cu o formulă simplă. Cu date verticale, VLOOKUP și INDEX / MATCH arată întotdeauna de sus în jos, dar cu XLOOKUP și pot specifica direcția ca și de jos în sus.

Exemplul 7: potrivire aproximativă cu XLOOKUP (Găsiți o rată de impozitare)

O altă îmbunătățire notabilă cu XLOOKUP este că acum există patru moduri de meci (VLOOKUP are 2 și MATCH are 3).

Puteți specifica oricare dintre cele patru argumente pentru a decide cum trebuie potrivită valoarea căutării:

• 0 - Potrivire exactă, în care valoarea de căutare ar trebui să se potrivească exact cu valoarea din matricea de căutare. Aceasta este opțiunea implicită.
• -1 - Caută potrivirea exactă, dar dacă este găsită, returnează următorul articol / valoare mai mică
• 1 - Caută potrivirea exactă, dar dacă este găsită, returnează următorul articol / valoare mai mare
• 2 - Pentru a face potrivire parțială folosind metacaractere (* sau ~)

Dar cea mai bună parte este că nu trebuie să vă faceți griji dacă datele dvs. sunt sortate în ordine crescătoare sau descendentă. Chiar dacă datele nu sunt sortate, XLOOKUP se va ocupa de ele.

Mai jos am un set de date în care vreau să găsesc comisionul fiecărei persoane - și comisionul trebuie calculat folosind tabelul din dreapta.

Mai jos este formula care va face acest lucru:

= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1) * B2

Aceasta folosește pur și simplu valoarea vânzărilor ca căutare și caută tabelul de căutare din dreapta. În această formulă, am folosit -1 ca al cincilea argument ([match_mode]), ceea ce înseamnă că va căuta o potrivire exactă, iar când nu va găsi una, va returna valoarea doar mai mică decât valoarea de căutare .

Și, așa cum am spus, nu trebuie să vă faceți griji dacă datele dvs. nu sunt sortate.

Faceți clic aici pentru a descărca fișierul de exemplu și a continua

Exemplul 8: Căutare orizontală

XLOOKUP poate face o căutare atât pe verticală, cât și pe orizontală.

Mai jos am un set de date în care am nume de elevi și scorurile lor în rânduri și vreau să aduc scorul pentru numele din celula B7.

Formula de mai jos va face acest lucru:

= XLOOKUP (B7, B1: O1, B2: O2)

Aceasta nu este altceva decât o căutare simplă (similară cu ceea ce am văzut în Exemplul 1), ci orizontală.

Toate exemplele pe care le acoper despre căutarea verticală pot fi făcute și cu o căutare orizontală folosind XLOOKUP (adio la VLOOKUP și HLOOKUP).

Exemplul 9: Căutare condiționată (utilizarea XLOOKUP cu alte formule)

Acesta este un exemplu ușor avansat și arată, de asemenea, puterea XLOOKUP atunci când trebuie să efectuați căutări complexe.

Mai jos este un set de date în care am numele studenților și punctajele acestora și vreau să știu numele elevului care a obținut maximum la fiecare disciplină și numărul studenților care a obținut peste 80 la fiecare disciplină.

Mai jos este formula care va da numele studentului cu cele mai mari note la fiecare disciplină:

= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)

Deoarece XLOOKUP poate fi folosit pentru a returna o întreagă matrice, am folosit-o pentru a obține mai întâi toate notele pentru subiectul solicitat.

De exemplu, pentru Math, când folosesc XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), îmi dă toate scorurile în matematică. Pot apoi să folosesc funcția MAX pentru a găsi scorul maxim în acest interval.

Acest scor maxim devine apoi valoarea mea de căutare, iar intervalul de căutare ar fi matricea returnată de XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)

Folosesc acest lucru într-o altă formulă XLOOKUP pentru a prelua numele studentului care a obținut nota maximă.

Și pentru a număra numărul de studenți care au obținut mai mult de 80, utilizați formula de mai jos:

= COUNTIF (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")

Acesta folosește pur și simplu formula XLOOKUP pentru a obține o gamă a tuturor valorilor pentru subiectul dat. Apoi îl înfășoară în funcția COUNTIF pentru a obține numărul de scoruri care depășesc 80.

Exemplul 10: Folosirea wildcard în XLOOKUP

La fel cum puteți utiliza caractere wildcard în VLOOKUP și MATCH, puteți face acest lucru și cu XLOOKUP.

Dar există o diferență.

În XLOOKUP, trebuie să specificați că utilizați caractere wildcard (în al cincilea argument). Dacă nu specificați acest lucru, XLOOKUP vă va da o eroare.

Mai jos este un set de date în care am nume de companii și valorificarea lor pe piață.

Vreau să caut un nume de companie în coloana D și să iau capitalizarea de piață din tabelul din stânga. Și întrucât numele din Coloana D nu sunt potriviri exacte, va trebui să folosesc caractere wildcard.

Mai jos este formula care va face acest lucru:

= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)

În formula de mai sus, am folosit asterisc (*) caracterul wildcard înainte ca după D2 (trebuie să fie în ghilimele duble și să fie asociat cu D2 folosind ampersand).

Aceasta indică formula pentru a căuta prin toate celulele și, dacă conține cuvântul din celula D2 (care este Apple), consideră-l o potrivire exactă. Indiferent cât de multe și ce caractere sunt înainte și după textul din celula D2.

Și pentru a vă asigura că XLOOKUP acceptă caractere wildcard, al cincilea argument a fost setat la 2 (potrivirea caracterelor wildcard).

Exemplul 11: Găsiți ultima valoare în coloană

Deoarece XLOOKUP vă permite să căutați de jos în sus, puteți găsi cu ușurință ultima valoare dintr-o listă, precum și să preluați valoarea corespunzătoare dintr-o coloană.

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să știți care este ultima companie și care este capitalizarea de piață a acestei ultime companii.

Formula de mai jos vă va oferi numele ultimei companii:

= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)

Iar formula de mai jos va da plafonul de piață al ultimei companii din listă:

= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)

Aceste formule utilizează din nou caractere wildcard. În acestea, am folosit asteriscul (*) ca valoare de căutare, ceea ce înseamnă că aceasta ar considera prima celulă pe care o întâlnește ca o potrivire exactă (deoarece asteriscul ar putea fi orice caracter și orice număr de caractere).

Și întrucât direcția este de jos în sus (pentru datele aranjate vertical), va returna ultima valoare din listă.

Și a doua formulă folosește un return_range separat pentru a obține plafonul de piață al numelui de familie din listă.

Faceți clic aici pentru a descărca fișierul de exemplu și a continua

Ce se întâmplă dacă nu aveți XLOOKUP?

Întrucât XLOOKUP va fi probabil disponibil numai pentru utilizatorii Office 365, o modalitate de a-l obține este să faceți upgrade la Office 365.

Dacă aveți deja Office 365 Home, Personal sau ediția Universității, aveți deja acces la XLOOKUP. Tot ce trebuie să faceți este să vă alăturați programului Office Insider.

Pentru a face acest lucru, accesați fila Fișier, faceți clic pe Cont, apoi faceți clic pe opțiunea Office insider. Ar exista o opțiune pentru a vă alătura programului privilegiat.

În cazul în care aveți alte abonamente Office 365 (cum ar fi Enterprise), sunt sigur că XLOOKUP și alte caracteristici minunate (cum ar fi matrice dinamice, formule precum SORT și FILTER) vor deveni disponibile în curând.

În cazul în care utilizați Excel 2010/2013/2016/2019, nu veți avea XLOOKUP și va trebui să utilizați în continuare combo VLOOKUP, HLOOKUP și INDEX / MATCH pentru a obține cele mai bune rezultate din formulele de căutare.

XLOOKUP Compatibilitate inversă

Acesta este un lucru despre care trebuie să fii atent - XLOOKUP este NU este compatibil cu versiunile anterioare.

Aceasta înseamnă că, dacă creați un fișier și utilizați formula XLOOKUP, apoi îl deschideți într-o versiune care nu are XLOOKUP, acesta va afișa erori.

Deoarece XLOOKUP este un mare pas înainte în direcția corectă, cred că aceasta va deveni formula implicită de căutare, dar cu siguranță va dura câțiva ani înainte de a fi adoptată pe scară largă. La urma urmei, încă văd unii oameni care folosesc Excel 2003.

Deci, acestea sunt 11 exemple XLOOKUP care vă pot ajuta să faceți toate lucrurile de căutare și referință făcute mai rapid și, de asemenea, să le ușureze utilizarea.

Sper că ați găsit util acest tutorial!