Cum se folosește o formulă VLOOKUP pentru a obține date dintr-o listă Excel. Depanați VLOOKUP atunci când lucrurile merg prost. Videoclipuri pas cu pas și registre de lucru Excel cu exemple VLOOKUP.
VLOOKUP Video
Urmăriți pașii pentru crearea unei formule VLOOKUP în videoclipul de căutare a prețului produsului, arătați mai jos. Instrucțiunile scrise se află sub videoclip. Obțineți fișierul eșantion de căutare a prețului produsului pe care îl urmăriți împreună cu videoclipul (descărcați fișierul # 4).
Selectați o locație pentru un tabel de căutare
Este o idee bună să stocați fiecare căutare. tabel pe o foaie de lucru separată din registrul de lucru. Apoi, pe măsură ce adăugați și ștergeți rânduri în tabelele de căutare, nu veți adăuga sau șterge accidental rânduri în orice alt tabel.
În acest exemplu, tabelul de căutare se află pe o foaie denumită Produse.
Creați un tabel de căutare
Formulele de căutare pot funcționa vertical, căutând valori pe o coloană sau pot funcționa orizontal, căutând valori pe un rând . În acest exemplu, informațiile vor fi stocate vertical, cu valorile în jos pe o coloană, iar mai târziu vom folosi o formulă VLookup pentru a efectua o căutare verticală.
- Introduceți titlurile din primul rând
- Prima coloană ar trebui să conțină valorile cheie unice pe care se va baza căutarea. În acest exemplu, puteți găsi preț pentru un anumit cod de produs.
- Dacă aveți alte date pe foaia de lucru, lăsați cel puțin un rând gol în partea de jos a tabelului și o coloană goală în partea dreaptă a tabelului, pentru a separa tabel de căutare din celelalte date.
Notă: Pentru a face mai ușoară referirea la tabel, puteți denumi intervalul. Există instrucțiuni aici: Denumirea unui interval ▲ TOP
Argumente ale funcției VLOOKUP
Funcția VLOOKUP are patru argumente:
- lookup_value : Ce valoare doriți să căutați? În acest exemplu, codul produsului se află în celula A7 și doriți să găsiți pr numele produsului.
- table_array: Unde este tabelul de căutare? Dacă utilizați o referință absolută ($ A $ 2: $ C $ 5), în loc de o referință relativă (A2: C5), va fi mai ușor să copiați în formulă în alte celule. Sau, denumiți tabelul de căutare și faceți referire la acesta după nume.
- col_index_num: Ce coloană are valoarea pe care doriți să o returnați? În acest exemplu, numele produselor se află în a doua coloană a tabelului de căutare.
- : Doriți o potrivire exactă? Este o potrivire aproximativă în regulă?
Dacă folosiți TRUE ca ultim argument sau omiteți ultimul argument, o potrivire aproximativă poate fi returnată. Acest exemplu are FALS ca ultim argument, deci dacă nu se găsește codul produsului, rezultatul va fi # N / A. (Notă: Excel este destul de iertător și va accepta 0 în loc de FALS și 1 în loc de ADEVĂRAT.) ▲ TOP
Creați o formulă VLookup
Odată ce ați creat tabelul de căutare, puteți crea alte formule în registrul de lucru și puteți extrage informații din lista de produse. De exemplu, pentru a pregăti o factură, puteți introduce un cod de produs, iar formulele vor primi numele produsului sau prețul din tabelul cu produse.
În acest exemplu, factura este creată pe o foaie numită Factură. Formula VLOOKUP ar trebui să găsească o potrivire exactă pentru codul produsului și să returneze numele produsului.
Pentru a crea formula VLOOKUP care calculează prețul produsului, urmați acești pași:
- Selectați foaia Factură
- Introduceți codul produsului A23 în celula A7
- În celula B7, porniți formula VLOOKUP:
= VLOOKUP ( - Faceți clic pe celula A7 pentru a introduce referința.
- Tastați o virgulă, pentru a termina primul argument
- Selectați foaia Produse
- Selectați celulele A2: C5 (lista de produse )
- Apăsați tasta F4, pentru a schimba referințele celulei la absolut. Formula ar trebui să arate astfel:
= VLOOKUP (A7, Produse! $ A $ 2: $ C $ 5 $ - Tastați o virgulă pentru a termina al doilea argument.
- Tastați un 2, care este coloana din tabelul de căutare care conține numele produsului.
- Tastați o virgulă pentru a termina al treilea argument.
- Tastați FALS, pentru a specifica că se găsește o potrivire exactă pentru codul produsului și adăugați parantezul de închidere.
- Formula ar trebui să arate acum l faceți acest lucru:
= VLOOKUP (A7, Produse! $ A $ 2: $ C $ 5,2, FALS) - Apăsați tasta Enter pentru a completa formula. Numele produsului va fi afișat.
Notă: pentru a returna prețul produsului, creați o formulă VLOOKUP care se referă la coloana 3 din tabelul de căutare. De exemplu, introduceți următoarea formulă în celula C7:
= VLOOKUP (A7, Produse! $ A $ 2: $ C $ 5,3, FALSE) ▲ TOP
Formula VLOOKUP pentru gama de valori
În unele situații, este preferată o potrivire aproximativă, deci mai multe valori vor întoarce același rezultat. De exemplu, atunci când notează lucrările studenților, toate lucrările cu o notă de 85 sau peste ar trebui să primească o notă A.
Pentru a vizualiza pașii pentru crearea acestei formule, vizionați videoclipul VLOOKUP prezentat mai jos. Instrucțiunile scrise se află sub videoclip.
În acest exemplu, tabelul de căutare este creat pe o foaie numită Grade. Pentru a crea tabelul de căutare, introduceți scorul minim pentru fiecare notă în coloana A. Introduceți nota potrivită în coloana B. Sortați scorurile în ordine crescătoare.
Celulele A2: B6 au fost denumite GradeList.
Scorurile sunt introduse pe o foaie numită Report Card, unde o formulă VLOOKUP calculează nota.
În captură de ecran de mai jos, formula a fost copiată în rândul 6 și puteți vedea formula în celula C6.
Combinați VLOOKUP cu MATCH
În loc să introduceți numărul coloanei într-o formulă VLOOKUP, utilizați funcția MATCH pentru a găsi coloana corectă în tabelul de căutare. Acest lucru are câteva avantaje:
- Face formula flexibilă, astfel încât este mai ușor să copiați formula într-o foaie de lucru.
- Poate preveni probleme dacă se adaugă coloane noi în tabelul de căutare sau dacă coloanele de căutare sunt rearanjate.
Acest videoclip arată pașii și există instrucțiuni scrise pentru un alt exemplu, sub videoclip.
Browserul dvs. nu poate afișa acest cadru. Iată un link către pagină
VLOOKUP With MATCH for Order Details
În acest exemplu, o formulă VLOOKUP va returna detaliile comenzii dintr-un tabel de căutare, pe baza numărului de identificare a comenzii . Iată tabelul de căutare, numit tblOrders.
NOTĂ: Acest exemplu se află în exemplul de registru de lucru nr. 1, pe foaia numită OrdersMATCH.
Iată foaia de lucru cu formulele VLOOKUP. Vrem regiunea, data comenzii și suma comenzii pentru fiecare comandă, deci sunt necesare 3 formule VLOOKUP.
Dacă numerele coloanelor sunt introduse în formulă, este necesară o formulă diferită în fiecare coloană:
- Regiune: = VLOOKUP ($ B6, tblOrdersALL, 2,0)
- OrderDate: = VLOOKUP ($ B6, tblOrdersALL, 3,0)
- OrderAmt: = VLOOKUP ($ B6, tblOrdersALL, 4,0)
Funcția MATCH
În loc să tastați numărul coloanei în formula VLOOKUP, putem folosi funcția MATCH. Funcția MATCH găsește poziția unui element într-o listă și returnează numărul poziției.
În captura de ecran de mai jos, formula MATCH returnează 2 ca poziția „Regiune”, în celulele de titlu (A1: D1) pentru tabelul de căutare.
= MATCH ( C5, Comenzi_ALL! $ A $ 1: $ D $ 1, 0)
NOTĂ: Pentru ca această tehnică să funcționeze corect, titlurile din foaia VLOOKUP trebuie să corespundă exact titlurilor tabelului de căutare. Pentru a asigura o potrivire exactă, celulele de titlu VLOOKUP sunt legate de celulele de titlu ale tabelului de căutare.
Adăugați MATCH la VLOOKUP
Pentru a adăuga funcția MATCH la formula VLOOKUP, înlocuiți doar numărul coloanei tastate
= VLOOKUP ($ B6, tblOrdersALL, 2,0)
cu formula MATCH:
= VLOOKUP ($ B6, tblOrdersALL, MATCH (C5, Comenzi_ALL! $ A $ 1: $ D $ 1, 0), 0)
Copiați formula VLOOKUP peste
Acum, în loc să aveți nevoie de o formulă diferită în fiecare coloană, puteți copia formula și utilizați aceeași formulă în toate coloanele. În fiecare coloană, se va referi la celula de titlu din acea coloană și își va găsi poziția în tabelul de căutare.
NOTĂ: Dacă completați formula pe coloane cu formatare diferită, urmați acești pași:
- Selectați celula cu formula pe care doriți să o copiați
- Arătați spre mânerul de umplere de pe celula selectată (pătrat mic în colțul din dreapta jos)
- Apăsați butonul DREAPTA al mouse-ului și trageți până la ultima celulă care are nevoie de formulă
- Eliberați butonul mouse-ului și faceți clic pe Completare fără formatare
Combinați IF și VLOOKUP
Puteți utiliza o formulă IF cu o formulă VLookup, pentru a returna valorile exacte dacă a fost găsită și un șir gol dacă nu este găsit.
Pentru a vedea pașii pentru configurarea formulei IF și VLOOKUP, puteți viziona acest scurt videoclip. Instrucțiunile scrise se află sub videoclip.
Pentru a ascunde erorile prin combinarea IF cu VLOOKUP, urmați acești pași:
- În foaia Factură, în celula A8, introduceți produsul cod A28. Dacă formula VLookup din celula B8 are FALSE ca al patrulea argument, rezultatul este # N / A, deoarece nu există o potrivire exactă pentru codul produsului în tabelul de căutare.
- Înfășurați formula VLookup cu un Formula IF (în acest exemplu a fost denumită lista de produse), folosind funcția ISNA pentru a verifica o eroare # N / A:
- Apăsați tasta Enter și celula apare necompletată. Deoarece nu s-a găsit o potrivire exactă, formula VLookup a returnat un # N / A, deci rezultatul funcției ISNA este ADEVĂRAT. Formula IF a transformat acest lucru într-un șir gol.
= IF (ISNA (VLOOKUP (A8, ProductList, 2, FALSE)) ), „”, VLOOKUP (A8, ProductList, 2, FALSE))
Combinați IFERROR și VLOOKUP
Mulțumim lui Chip Pearson pentru sugerarea acestei formule.
În Excel 2007, a fost introdusă o nouă funcție, IFERROR . Puteți utiliza o formulă IFERROR cu VLookup pentru a verifica mai multe tabele pentru o valoare și pentru a returna informațiile aferente atunci când sunt găsite. În acest exemplu, trei regiuni, Vest, Est și Central, au fișe de comandă. Pe fiecare foaie este un interval numit – OrdersW, OrdersE și OrdersC.
Pe o foaie numită Comenzi, puteți introduce un ID de comandă, apoi puteți utiliza un VLOOKUP cu IFERROR pentru a verifica fiecare interval numit și vizualizați informațiile despre comanda selectată.
- În foaia de comandă, în celula B6, introduceți un 4 ca ID de comandă. Această comandă a fost plasată în regiunea Centrală.
- Pentru a verifica pur și simplu tabelul regiunii Est, formula VLOOKUP din celula C6 ar fi:
= VLOOKUP (B6, OrdersE, 2, FALSE) - Apăsați tasta Enter și formula VLOOKUP returnează un # N / A, deoarece ID-ul comenzii 4 nu se află în tabelul de ordine al regiunilor de est.
- Deoarece o comandă ar putea avea a fost plasat în oricare dintre cele trei regiuni, aveți nevoie de o formulă care să verifice fiecare tabel.
Dacă ID-ul comenzii nu se găsește în primul tabel, formula ar trebui să verifice al doilea tabel. Dacă ID-ul comenzii nu se află în al doilea tabel tabel, ar trebui să verifice al treilea tabel. Dacă ID-ul comenzii nu este „t în al treilea tabel, atunci ar trebui să apară un mesaj„ Nu a fost găsit ”în celulă. - Formula IFERROR vă permite să verificați o valoare, apoi specificați ce să faceți dacă se găsește o eroare. Dacă utilizați IFERROR cu formula existentă, puteți afișa „Not Found”, în loc de eroarea # N / A:
= IFERROR (VLOOKUP (B6, OrdersE, 2, FALSE), „Not Found”) - Pentru a verifica toate cele trei tabele, puteți urmări formulele IFERROR și VLOOKUP:
Aceasta verifică tabelul OrdersE și dacă se găsește o eroare, verifică tabelul OrdersW, apoi OrdersC. Dacă ID-ul comenzii nu se găsește în niciunul dintre cele trei tabele, mesajul Not Found este afișat în celulă.
Puteți verifica, de asemenea, mai multe tabele în versiunile mai vechi de Excel, unde IFERROR nu este disponibil, utilizând un formula mai lungă:
VLOOKUP pentru valori combinate
În unele tabele, este posibil să nu existe valori unice orice coloană din tabelul de căutare. De exemplu, în tabelul prezentat mai jos, Jacheta este listată de două ori în coloana A. Cu toate acestea, există o singură înregistrare pentru fiecare combinație de jachete și mărimi – Jachetă Medie în rândul 4 și Jachetă Mare în rândul 5.
Dacă trebuie să găsiți prețul pentru o jachetă mare, o VLOOKUP bazată doar pe coloana A va returna prețul pentru prima jachetă listată (mediu ). Ați fi subprețuit sacoul – vândându-l pentru 60,00, în loc de 65,00.
Pentru a crea valori de căutare unice, poate introduce o nouă coloană în partea stângă a tabelului și poate utiliza o formulă pentru a combina produsul și dimensiunea. În celula A2, formula combină valoarea în B2 și caracterul țevii și valoarea în C2.
= B2 & „|” & C2
Copiați acea formulă în ultimul rând de date, astfel încât fiecare rând să aibă o valoare unică în coloana A.
Notă : În loc de caracterul pipe, puteți utiliza un alt caracter care nu este „inclus în datele dvs.
Apoi, în o formulă VLOOKUP, combinați produsul și dimensiunea ca valoare_Căutare. În celula H1, formula combină valoarea în F1 și caracterul țevii și valoarea în G1.
= VLOOKUP (F1 &” | „& G1, $ A $ 2: $ D $ 5,4, FALS)
Notă: Prețul este acum în coloana 4, în loc de coloana 3. ▲ TOP
Depanarea formulei VLOOKUP
Formula VLOOKUP poate returna un # N / A, chiar dacă valoarea pe care o căutați pare să fie în tabelul de căutare. Cauzele frecvente pentru acest lucru sunt:
Text vs. Număr
Caractere spațiale
Alte caractere
Text vs. Număr
O cauză obișnuită a acestei erori este că una dintre valori este un număr, iar cealaltă este text. De exemplu, tabelul de căutare poate conține „123 (text), iar valoarea de căutare este 123 (un număr).
- Dacă este posibil, convertiți textul în numere, utilizând unul dintre metodele prezentate aici: Conversia textului în numere
- Dacă nu puteți converti datele, puteți converti valoarea de căutare în formula VLOOKUP, utilizând una dintre soluțiile de mai jos:
- Valorile de căutare sunt text, tabelul are numere
- Valorile de căutare sunt numere, tabelul are text
- Pentru a afla ce valori sunt text și care sunt numere, consultați numărul detaliat sau pașii de depanare a textului
1. Valorile de căutare sunt Text și tabelul conține Numere
Dacă tabelul de căutare conține numere, iar valoarea de căutare este text, utilizați o formulă similară cu următoarea:
= VLOOKUP (–A7, Produse!$ A $ 2: $ C $ 5,3, FALS)
Unarul dublu (-) convertește textul într-un număr și va funcționa corect chiar dacă valorile de căutare sunt numere.
2. Valorile de căutare sunt Numere, iar tabelul conține Text
Dacă tabelul de căutare conține text și valoarea de căutat este numerică, utilizați o formulă similară cu următoarea:
= VLOOKUP (A7 & „”), Produse! $ A $ 2: $ C $ 5,3, FALS)
SAU = VLOOKUP (TEXT (A7, „00000”) , Produse! $ A $ 2: $ C $ 5,3, FALS)
Funcția TEXT convertește un număr în text și va funcționa corect chiar dacă valorile de căutare sunt text. În primul exemplu, operatorul & creează un șir de text dintr-un număr neformatat. În al doilea exemplu, un număr formatat cu zerouri de start (de exemplu, 00123) se va potrivi cu un „număr” de text cu zerouri de început.
Pentru a vedea pașii pentru remedierea problemei VLOOKUP atunci când tabelul de căutare are valori text, urmăriți acest scurt tutorial video.
Spații într-o valoare și nu în cealaltă
O altă cauză potențială pentru care nu se găsește nicio valoare potrivită este diferența de spații. Una dintre valori poate conține spații principale (sau finale sau spații încorporate), iar cealaltă nu. Pentru a testa valorile, puteți utiliza funcția LEN, pentru a verifica lungimea fiecărei valori.
De exemplu: = LEN (A7) va returna numărul de caractere din celula A7. Ar trebui să fie egal cu numărul de caractere din celula care se potrivește în tabelul de căutare.
Dacă este posibil, eliminați inutile spații, iar formula VLOOKUP ar trebui să funcționeze corect. Dacă nu puteți elimina spațiile, utilizați funcția TRIM din VLOOKUP, pentru a elimina spațiile de început, de urmărire sau de duplicat. De exemplu:
= VLOOKUP (TRIM (A7), ProductList, 2, FALSE)
Alte caractere
Dacă funcția TRIM singură nu rezolvă problema , puteți încerca una dintre următoarele sugestii:
- Funcția SUBSTITUTE
- Funcția CURĂ
- Macro pentru eliminarea caracterelor web
Funcția SUBSTITUTE
Utilizați funcția SUBSTITUTE pentru a elimina caracterele nedorite. Există un exemplu pe blogul Contextures: Curățați datele Excel cu TRIM și SUBSTITUTE
Funcția CLEAN
O altă modalitate de a remedia problemele VLOOKUP este cu funcția CLEAN, care poate elimina unele caractere nedorite din text. Există mai multe informații despre funcția CLEAN în această postare de blog Contextures: 30 Funcții Excel în 30 de zile: 29 – CLEAN
Caracterele web într-o valoare și nu în cealaltă
Dacă ați copiat date dintr-o pagină web, aceasta poate conține spațiu HTML care nu se rupe (& nbsp) personaje. David McRitchie a scris o macro pentru a le elimina, împreună cu o caractere spațiale
Probleme la sortarea formulei VLOOKUP
O formulă VLOOKUP poate returna la început rezultatele corecte, dar apoi afișează rezultate incorecte dacă lista de articole este sortată. Acest lucru se poate întâmpla dacă referința la valoarea Căutare include un nume de foaie. De exemplu:
= VLOOKUP („Formular de comandă”! B5, Produse! $ B $ 2: $ C $ 6,2, FALS)
NOTĂ: Această problemă poate apărea cu alte funcții de asemenea, cum ar fi o formulă de căutare INDEX / MATCH.
Urmăriți acest videoclip pentru a vedea pașii pentru rezolvarea problemei și descărcați fișierul eșantion de problemă de sortare VLOOKUP pentru a urma. Instrucțiunile scrise se află sub videoclip.
Numele foilor în referință
Acest tip de referință este creat dacă faceți clic pe o altă foaie în timp ce creați formula. De îndată ce faceți acest lucru, Excel adaugă numele foii la orice referințe ulterioare din formulă.
În captura de ecran mai sus, Dress se află în celula B9, iar celula C9 arată prețul corect de 30 USD.
Cu toate acestea, după sortarea produselor AZ, Dress se deplasează până la celula B5, dar formula din celula C5 continuă să se refere la celula B9. Datorită numelor de foi din referințe, Excel păstrează referințele originale, în loc să păstreze o referință la rândul curent. Celula C5 arată prețul pentru un pulover, în loc de o rochie. ▲ TOP
Remediați problema
Pentru a rezolva problema, eliminați orice nume de foi inutile din Referințe celule VLOOKUP. Iată formula revizuită pentru celula C5:
= VLOOKUP (B5, Produse! $ B $ 2: $ C $ 6,2, FALS)
După ce numele foilor inutile sunt eliminate, lista poate fi sortată în siguranță, iar rezultatele corecte vor fi afișate pentru fiecare element.
Descărcați fișierele eșantion
1. Obțineți exemplul de registru de lucru VLOOKUP (Excel 2007 și versiuni ulterioare). Fișierul arhivat este în format xlsx și nu conține macrocomenzi.
2. Pentru Excel 2003, obțineți această versiune a fișierului eșantion VLOOKUP. Fișierul arhivat este în format xls și nu conține macrocomenzi.
3. Pentru a vedea problema care poate apărea la sortarea cu VLOOKUP, obțineți acest fișier eșantion de problemă de sortare VLOOKUP. Fișierul comprimat este în format xlsx și nu conține macrocomenzi.
4. Obțineți registrul de lucru Căutare preț produse, utilizat în tutorialul video.Fișierul comprimat este în format xlsx și nu conține macrocomenzi. ▲ TOP
Mai multe tutoriale
VLOOKUP Număr / Depanare text
VLOOKUP dintr-un alt registru de lucru
HLOOKUP
ALEGE Funcție
Căutare – 2 criterii
CĂUTARE
INDEX / MATCH
Compararea funcțiilor de căutare
Lista funcțiilor