Aceste exemple vă vor învăța cum să Vlookup mai multe criterii, să returnați o instanță specifică sau toate potrivirile, să faceți Vlookup dinamic mai multe foi și multe altele.
Este a doua parte a seriei care vă va ajuta să profitați de puterea Excel VLOOKUP. Exemplele implică faptul că știți cum funcționează această funcție. Dacă nu, este normal să începeți cu utilizările de bază ale VLOOKUP în Excel.
Înainte de a merge mai departe, permiteți-mi să vă reamintesc pe scurt sintaxa:
Acum că toată lumea se află pe aceeași pagină, să aruncăm o privire mai atentă asupra exemplelor avansate de formule VLOOKUP:
- Cum să Vlookup mai multe criterii în Excel
- Vlookup și returnare a n-a potrivire
- Vlookup valori multiple
- Vlookup bazat pe valorile rândurilor și coloanelor
- Formula Vlookup imbricată
- VLOOKUP și INDIRECT pentru a extrage date din mai multe foi
Cum să Vlookup criterii multiple
Funcția Excel VLOOKUP este foarte utilă atunci când vine vorba de căutarea într-o bază de date pentru o anumită valoare. Cu toate acestea, îi lipsește o caracteristică importantă – sintaxa sa permite o singură valoare de căutare. Dar dacă doriți să căutați cu mai multe condiții? Există câteva soluții diferite pentru a alege.
Formula 1. VLOOKUP bazat pe tw o valori
Să presupunem că aveți o listă de comenzi și doriți să găsiți cantitatea pe baza a 2 criterii, numele clientului și produsul. Un factor complicat este că fiecare client a comandat mai multe produse, așa cum se arată în tabelul de mai jos:
O formulă obișnuită VLOOKUP nu a funcționat în acest situație deoarece returnează prima potrivire găsită pe baza unei singure valori de căutare pe care o specificați.
Pentru a depăși acest lucru, puteți adăuga o coloană de asistență și puteți concatena valorile din două coloane de căutare (Client și Produs) acolo. Este important ca coloana de asistență să fie cea mai stângă coloană din tabelul de tabele, deoarece este acolo unde Excel VLOOKUP caută întotdeauna valoarea de căutare.
Deci, adăugați o coloană în stânga tabelului și copiați formula de mai jos din coloana respectivă. Aceasta va popula coloana de ajutor cu valorile din coloanele B și C (caracterul spațial este concatenat între ele pentru o mai bună lizibilitate):
=B2&" "&C2
Și apoi, utilizați o formulă standard VLOOKUP și plasați ambele criterii în argumentul lookup_value, separat cu un spațiu:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Sau introduceți criteriile în celule separate (G1 și G2 în cazul nostru) și concatenați aceste celule:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
După cum dorim să returnăm o valoare din coloana D, care este a patra în matricea de tabele, folosim 4 pentru col_index_num. Argumentul range_lookup este setat la FALS pentru Vlookup o potrivire exactă. Captura de ecran de mai jos arată rezultatul:
În cazul în care tabelul dvs. de căutare se află într-o altă foaie, includeți numele foaiei în formula VLOOKUP. De exemplu:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
Alternativ, creați un interval denumit pentru tabelul de căutare (să zicem, Comenzi) pentru a face formula mai ușoară- pentru a citi:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
Pentru mai multe informații, consultați Cum să vizualizați dintr-o altă foaie în Excel.
Formula 2. Excel VLOOKUP cu condiții multiple
În teorie, puteți utiliza abordarea de mai sus pentru Vlookup mai mult de două criterii. H cu toate acestea, există câteva avertismente. În primul rând, o valoare de căutare este limitată la 255 de caractere și, în al doilea rând, designul foii de lucru poate să nu permită adăugarea unei coloane de ajutor.
Din fericire, Microsoft Excel oferă adesea mai multe modalități de a face același lucru. Pentru a căuta mai multe criterii, puteți utiliza fie o combinație INDEX MATCH, fie funcția XLOOKUP introdusă recent în Office 365.
De exemplu, pentru a căuta pe baza a 3 valori diferite (Data, Numele clientului și Produsul), utilizați una dintre următoarele formule:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Unde:
Pentru explicația detaliată a formulelor, consultați:
- XLOOKUP cu mai multe criterii
- INDEX MATCH formula cu mai multe criterii
Cum se folosește VLOOKUP pentru a obține a doua, a treia sau a n-a potrivire
După cum știți deja, Excel VLOOKUP poate prelua o singură valoare de potrivire, mai exact, returnează prima potrivire găsită. Dar ce se întâmplă dacă există mai multe potriviri în matricea dvs. de căutare și doriți să obțineți a doua sau a treia instanță? Sarcina sună destul de complicată, dar soluția există!
Formula 1. Vlookup Nth instance
Să presupunem că aveți nume de clienți într-o coloană, produsele pe care le-au achiziționat în alta și căutați să găsiți al doilea sau al treilea produs cumpărat de un anumit client.
Cel mai simplu mod este să adăugați o coloană de ajutor în stânga tabelului, așa cum am făcut în primul exemplu. Dar de data aceasta, îl vom completa cu nume de clienți și numere de apariție precum „John Doe1”, „John Doe2” etc.
Pentru a obține apariția, utilizați funcția COUNTIF cu o referință de interval mixt ( prima referință este absolută și a doua este relativă ca $ B $ 2: B2). Deoarece referința relativă se modifică pe baza unei poziții a celulei în care formula este copiată, în rândul 3 va deveni $ B $ 2: B3, în rândul 4 – $ B $ 2: B4 și așa mai departe.
Concatenată cu numele clientului (B2), formula ia această formă:
=B2&COUNTIF($B$2:B2, B2)
Formula de mai sus merge la A2 și apoi îl copiați în câte celule este necesar.
După aceea, introduceți numele țintă și numărul de apariție în celule separate (F1 și F2) și utilizați formula de mai jos pentru a căuta o apariție specifică:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Formula 2. Vlookup 2nd occurrence
Dacă căutați a doua instanță a valorii de căutare, puteți face fără coloana de ajutor. În schimb, creați tabelul dinamic folosind funcția INDIRECT împreună cu MATCH:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Unde:
- E1 este valoarea de căutare
- A2: A11 este intervalul de căutare
- B11 este ultima celulă (dreapta jos) a tabelului de căutare
Rețineți că formula de mai sus este scrisă pentru un caz specific în care celulele de date din tabelul de căutare încep în rândul 2. Dacă tabelul dvs. este undeva în în mijlocul foii, utilizați această formulă universală, unde A1 este celula din stânga sus a tabelului de căutare care conține un antet de coloană:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Cum funcționează această formulă
Iată partea cheie a formulei care creează un interval de vizualizare dinamic:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
Funcția MATCH configurată pentru potrivirea exactă (0 în ultimul argument) compară numele țintei (E1) cu lista de nume (A2: A11) și returnează poziția primei potriviri găsite, care este 3 eu nu cazul tău. Acest număr va fi folosit ca coordonată de rând de pornire pentru intervalul vlookup, așa că îi adăugăm 2 (+1 pentru a exclude prima instanță și +1 pentru a exclude rândul 1 cu anteturile coloanei). Alternativ, puteți utiliza 1 + ROW (A1) pentru a calcula automat ajustarea necesară pe baza poziției rândului de antet (A1 în cazul nostru).
Ca rezultat, obținem următorul șir de text, pe care INDIRECT îl convertește la o referință de interval:
INDIRECT("A"&5&":B11") -> A5:B11
Acest interval merge la argumentul table_array al VLOOKUP forțându-l să înceapă căutarea în rând 5, lăsând în afară prima instanță a valorii de căutare:
VLOOKUP(E1, A5:B11, 2, FALSE)
Cum să vizualizați și să returnați mai multe valori în Excel
Funcția Excel VLOOKUP este concepută pentru a returna doar o potrivire. Există o modalitate de a vizualiza mai multe instanțe? Da, există, deși nu este unul ușor. Acest lucru necesită o utilizare combinată a mai multor funcții, cum ar fi INDEX, SMALL și ROW este o formulă matrice.
De exemplu, cele de mai jos pot găsi toate aparițiile valorii de căutare F2 în intervalul de căutare B2: B16 și returnare mai multe potriviri din coloana C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Există 2 moduri de a introduce formula în foaia de lucru:
- Tastați formula în prima celulă, apăsați Ctrl + Shift + Enter, apoi trageți-o în jos către alte câteva celule.
- Selectați mai multe celule adiacente într-o singură coloană (F1: F11 în captura de ecran de mai jos), tastați formula și apăsați Ctrl + Shift + Enter pentru a o completa.
În orice caz, numărul de celule în care introduceți formula trebuie să fie egal sau mai mare decât numărul maxim de potriviri posibile.
Pentru explicația detaliată a logicii formulei și mai multe exemple, consultați Cum să căutați mai multe valori în Excel .
Cum să Vlookup în rânduri și coloane (căutare bidirecțională)
Căutarea bidirecțională (căutarea matricială sau căutarea bidimensională) este un cuvânt elegant pentru a căuta o valoare la intersecția unui anumit rând și coloană.Există câteva modalități diferite de a efectua căutări bidimensionale în Excel, dar, deoarece acest tutorial se concentrează pe funcția VLOOKUP, o vom folosi în mod natural.
Pentru acest exemplu, vom lua tabelul de mai jos cu vânzări lunare și elaborați o formulă VLOOKUP pentru a prelua cifra de vânzări pentru un anumit articol într-o anumită lună.
Cu numele articolelor în A2: A9, numele lunilor în B1: F1, articolul țintă în I1 și luna țintă în I2, formula merge după cum urmează:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Cum funcționează această formulă
Nucleul formulei este funcția standard VLOOKUP care caută o potrivire exactă cu valoarea de căutare din I1. Dar, din moment ce nu știm în care se află exact coloana vânzările pentru o anumită lună, nu putem furniza numărul coloanei direct la argumentul col_index_num. Pentru a găsi acea coloană, folosim următoarea funcție MATCH:
MATCH(I2, A1:F1, 0)
Tradusă în engleză, formula spune: căutați în sus valoarea I2 în A1: F1 și returnează poziția sa relativă în matrice. Furnizând 0 celui de-al treilea argument, instruiți MATCH să găsească valoarea exact egală cu valoarea de căutare (este ca și cum ați folosi FALSE pentru argumentul range_lookup al VLOOKUP).
Deoarece Mar se află în a 4-a coloană în matricea de căutare, funcția MATCH returnează 4, care merge direct la argumentul col_index_num al VLOOKUP:
VLOOKUP(I1, A2:F9, 4, FALSE)
Vă rugăm să acordați atenție că, deși numele lunii încep în coloana B, folosim A1: I1 pentru matricea de căutare. Acest lucru se face pentru ca numărul returnat de MATCH să corespundă poziției coloanei din table_array din VLOOKUP.
Pentru a afla mai multe modalități de efectuare a căutării matriciale în Excel, consultați INDEX MATCH MATCH și alte formule pentru căutare bidimensională.
Cum se face mai multe Vlookup în Excel (Vlookup imbricat)
Uneori se poate întâmpla ca tabelul dvs. principal și tabelul de căutare să nu aibă o singură coloană în comun, ceea ce vă împiedică să faceți un Vlookup între două tabele. Cu toate acestea, există un alt tabel, care nu conține informațiile pe care le căutați, dar are o coloană comună cu tabelul principal și o altă coloană comună cu tabelul de căutare.
În imaginea de mai jos este ilustrată situația:
Scopul este de a copia prețurile în tabelul principal pe baza ID-urilor articolelor. Problema este că tabelul care conține prețuri nu are ID-uri de articol, ceea ce înseamnă că va trebui să facem două Vlookup-uri într-o singură formulă.
Din motive de comoditate, să creăm mai întâi câteva intervale denumite :
- Tabelul de căutare 1 se numește Produse (D3: E3).
- Tabelul de căutare 2 se numește Prețuri (G3: H3).
Tabelele pot fi în aceleași sau în foi de lucru diferite.
Și acum, vom efectua așa-numitul Vlookup dublu, alias Vlookup imbricat.
Mai întâi, faceți un VLOOKUP formula pentru a găsi numele produsului în tabelul Căutare 1 (denumit Produse) pe baza codului articolului (A3):
=VLOOKUP(A3, Products, 2, FALSE)
Apoi, puneți formula de mai sus în argumentul lookup_value al unei alte funcții VLOOKUP pentru a extrage prețurile din tabelul de căutare 2 (denumit Preturi) pe baza numelui produsului returnat de VLOOKUP imbricat:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
Captura de ecran de mai jos prezintă formula noastră Vlookup imbricată în acțiune:
Cum să Vlookup multiple foi dinamice
Uneori, este posibil să aveți date în același format împărțite pe mai multe foi de lucru. Și scopul dvs. este să extrageți datele dintr-o anumită foaie, în funcție de valoarea cheie dintr-o celulă dată.
Acest lucru poate fi mai ușor de înțeles dintr-un exemplu. Să spunem că aveți câteva rapoarte regionale de vânzări în același format și căutați să obțineți cifrele de vânzări pentru un anumit produs în anumite regiuni:
La fel ca în exemplul anterior, începem cu definirea câtorva nume:
- Gama A2: B5 în foaia CA se numește CA_Sales.
- Gama A2 : B5 în foaia FL este denumită FL_Sales.
- Intervalul A2: B5 în foaia KS se numește KS_Sales.
După cum puteți vedea, toate intervalele numite au o comună parte (vânzări) și piese unice (CA, FL, KS). Vă rugăm să vă asigurați că vă denumiți intervalele într-un mod similar, deoarece este esențial pentru formula pe care o vom construi.
Formula 1. INDIRECT VLOOKUP pentru a extrage dinamic date din diferite foi
Dacă sarcina dvs. este de a prelua date din mai multe foi, o formulă VLOOKUP INDIRECT este cea mai bună soluție – compactă și ușor de înțeles.
Pentru acest exemplu, organizăm tabelul rezumat în acest fel:
- Introduceți produsele de interes în A2 și A3. Acestea sunt valorile noastre de căutare.
- Introduceți părțile unice ale intervalelor denumite în B1, C1 și D1.
Și acum, concatenăm celula care conține partea unică (B1) cu partea comună („_Sales”) și alimentăm șirul rezultat către INDIRECT:
INDIRECT(B$1&"_Sales")
Funcția INDIRECT transformă șirul într-un nume pe care Excel îl poate înțelege și îl puneți în argumentul table_array al VLOOKUP:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
Formula de mai sus merge la B2 și apoi o copiați în jos și în dreapta.
Vă rugăm să fiți atenți la faptul că, în valoarea de căutare ($ A2), am „blocat coordonata coloanei cu referință absolută a celulei, astfel încât coloana să rămână fixă atunci când formula este copiată la dreapta. În referința B $ 1, am blocat rândul pentru că dorim coloana coordonați pentru a modifica și furniza o parte de nume adecvată către INDIRECT în funcție de coloana în care este copiată formula:
Dacă tabelul principal este organizat în mod diferit, valorile de căutare într-un rând și părțile unice ale numelor de intervale dintr-un colu mn, atunci ar trebui să blocați coordonatele rândului în valoarea de căutare (B $ 1) și coordonata coloanei în părțile nume ($ A2):
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Formula 2. VLOOKUP și IF-uri imbricate pentru a căuta mai multe foi
În situația în care tocmai două sau trei foi de căutare, puteți utiliza o formulă VLOOKUP destul de simplă cu funcții IF imbricate pentru a selecta foaia corectă pe baza valorii cheii dintr-o anumită celulă:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Unde $ A2 este valoarea de căutare (numele articolului) și B $ 1 este valoarea cheie (stare):
În acest caz, nu este necesar să definiți nume și să utilizați referințe externe pentru a vă referi la o altă foaie sau registru de lucru.
Pentru mai multe exemple de formule, vă rugăm să consultați Cum să căutați mai multe foi în Excel .
Așa se folosește VLOOKUP în Excel. Vă mulțumesc că ați citit și sper să ne vedem pe blogul nostru săptămâna viitoare!
Caiet de lucru pentru descărcare
Exemple avansate de formule VLOOKUP (fișier .xlsx)
- Tutorial Excel VLOOKUP pentru începători
- Cum să Vlookup mai multe potriviri în Excel
- Cum se utilizează funcțiile VLOOKUP & SUM sau SUMIF în Excel
- INDEX & Funcțiile MATCH în Excel – o alternativă mai bună la VLOOKUP
- 4 moduri de a face un Vlookup sensibil la majuscule și minuscule în Excel
- VLOOKUP Excel nu funcționează – erori N / A, NAME și VALUE