Funcția Excel VLOOKUP

VLOOKUP este o funcție Excel pentru a obține date dintr-un tabel organizat vertical. Valorile de căutare trebuie să apară în prima coloană a tabelului trecută în VLOOKUP. VLOOKUP acceptă potrivirea aproximativă și exactă, și metacaracterele (*?) Pentru potrivirile parțiale.

Date verticale | Numere coloane | Arată doar corect | Moduri de potrivire | Potrivire exactă | Meci aproximativ | Primul meci | Meci cu comodine | Căutare bidirecțională | Criterii multiple | # N / A Erori | Videoclipuri

V este pentru vertical

Scopul VLOOKUP este de a obține informații dintr-un tabel organizat astfel:

Folosind numărul comenzii din coloana B ca valoare de căutare, VLOOKUP poate obține codul de client, suma, numele și statul pentru orice comandă. De exemplu, pentru a obține numele clientului pentru comanda 1004, formula este:

=VLOOKUP(1004,B5:F9,4,FALSE) // returns "Sue Martin"

 Pentru date orizontale, puteți utiliza HLOOKUP, INDEX și MATCH sau XLOOKUP. 
 VLOOKUP se bazează pe numerele de coloane 
 Când utilizați VLOOKUP, imaginați-vă că fiecare coloană din tabel este numerotată, începând de la stânga. Pentru a obține o valoare dintr-o anumită coloană, furnizați numărul corespunzător ca „index coloană”. De exemplu, indexul coloanei pentru preluarea prenumelui de mai jos este 2: 
  
 Numele și adresa de e-mail pot fi recuperate cu coloane 3 și 4: 

=VLOOKUP(H3,B4:E13,2,FALSE) // first name=VLOOKUP(H3,B4:E13,3,FALSE) // last name=VLOOKUP(H3,B4:E13,4,FALSE) // email address

 VLOOKUP arată doar corect 
 VLOOKUP poate privi doar spre dreapta. Datele pe care doriți să le extrageți (valorile rezultatelor) pot apărea în orice coloană din dreapta valorilor căutării: 
  
 Dacă trebuie să căutați valori în stânga, consultați INDEX și MATCH sau XLOOKUP. 
 Potrivire exactă și aproximativă 
 VLOOKUP are două moduri de potrivire, exactă și aproximativă. Numele argumentului care controlează potrivirea este „range_lookup”. Acesta este un nume confuz, deoarece se pare că are ceva de-a face cu intervale de celule precum A1: A10. De fapt, cuvântul „interval” în acest caz se referă la „interval de valori” – când range_lookup este ADEVĂRAT, VLOOKUP se va potrivi mai degrabă cu un interval de valori decât cu o valoare exactă. Un bun exemplu în acest sens este utilizarea VLOOKUP pentru a calcula notele. 
 Este important să înțelegem că range_lookup implicit este TRUE, ceea ce înseamnă că VLOOKUP va folosi în mod implicit potrivirea aproximativă, ceea ce poate fi periculos. Setați range_lookup la FALSE pentru a forța potrivirea exactă: 

 Notă: De asemenea, puteți furniza zero (0) în loc de FALSE pentru o potrivire exactă. 
 Potrivire exactă 
 În majoritatea cazurilor, probabil că veți dori să utilizați VLOOKUP în modul de potrivire exactă. Acest lucru are sens atunci când aveți o cheie unică de utilizat ca valoare de căutare, de exemplu, titlul filmului din aceste date: 
  
 Formula din H6 pentru a găsi Anul, pe baza unei potriviri exacte a titlului filmului, este: 
=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match

 Potrivire aproximativă 
 În cazurile în care doriți cea mai bună potrivire, nu neapărat o potrivire exactă, veți dori să utilizați modul aproximativ. De exemplu, mai jos vrem să căutăm o rată de comision în tabelul G5: H10. Valorile de căutare provin din coloana C. În acest exemplu, trebuie să folosim VLOOKUP în modul de potrivire aproximativă, deoarece în majoritatea cazurilor nu se va găsi niciodată o potrivire exactă. Formula VLOOKUP din D5 este configurată pentru a efectua o potrivire aproximativă setând ultimul argument la TRUE: 
  

=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate match

 VLOOKUP va scana valorile din coloana G pentru valoarea de căutare. Dacă se găsește o potrivire exactă, VLOOKUP îl va folosi. Dacă nu, VLOOKUP va „face un pas înapoi” și se va potrivi cu rândul anterior. 
 Notă: datele trebuie să fie sortate în ordine crescătoare după valoarea de căutare atunci când utilizați modul de potrivire aproximativ cu VLOOKUP. 
 Prima potrivire 
 În cazul valorilor duplicate, VLOOKUP va găsi prima potrivire atunci când modul de potrivire este exact. În ecranul de mai jos, VLOOKUP este configurat pentru a găsi prețul pentru culoarea „Verde”. Există trei intrări cu culoarea verde, iar VLOOKUP returnează prețul pentru prima intrare, 17 USD. Formula din celula F5 este: 

=VLOOKUP(E5,B5:C11,2,FALSE) // returns 17

  
 Potrivire cu wildcard 
 Funcția VLOOKUP acceptă metacaractere, ceea ce face posibilă efectuarea unei potriviri parțiale pe o valoare de căutare. De exemplu, puteți utiliza VLOOKUP pentru a prelua valori dintr-un tabel după ce ați introdus doar o parte a unei valori de căutare. Pentru a utiliza metacaracterele cu VLOOKUP, trebuie să specificați modul de potrivire exact furnizând FALSE sau 0 pentru ultimul argument, range_lookup. Formula din H7 recuperează prenumele, „Michael”, după ce a introdus „Aya” în celula H4: 

=VLOOKUP($H$4&"*",$B$5:$E$104,2,FALSE)

  
 Citiți aici o explicație mai detaliată. 
 Căutare bidirecțională 
 În funcția VLOOKUP, coloana argumentul index este în mod normal codificat ca un număr static.Cu toate acestea, puteți crea și un index de coloană dinamic utilizând funcția MATCH pentru a localiza coloana din dreapta. Această tehnică vă permite să creați o căutare dinamică bidirecțională, potrivită atât pe rânduri, cât și pe coloane. În ecranul de mai jos, VLOOKUP este configurat pentru a efectua o căutare bazată pe nume și lună. Formula din H6 este: 


=VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),0)

  
 Pentru mai multe detalii, consultați acest exemplu. 
 Notă: În general, INDEX și MATCH este un mod mai flexibil de a efectua căutări în două direcții. 
 Criterii multiple 
 Funcția VLOOKUP nu gestionează mai multe criterii în mod nativ. Cu toate acestea, puteți utiliza o coloană de ajutor pentru a uni mai multe câmpuri și puteți utiliza aceste câmpuri ca mai multe criterii în VLOOKUP. În exemplul de mai jos, Coloana B este o coloană de ajutor care concatenează numele și prenumele împreună cu această formulă: 

=C5&D5 // helper column

 VLOOKUP este configurat să facă același lucru pentru a crea o valoare de căutare. Formula din H6 este: 

=VLOOKUP(H4&H5,B5:E13,4,0)

  
 Pentru detalii, consultați acest exemplu. 
 Notă: INDEX și MATCH și XLOOKUP sunt modalități mai robuste de a gestiona căutările pe baza mai multor criterii. 
 VLOOKUP și # N / A errors 
 Dacă utilizați VLOOKUP, veți întâlni inevitabil eroarea # N / A. Eroarea # N / A înseamnă doar „nu a fost găsit”. De exemplu, în ecranul de mai jos, valoarea de căutare „Toy Story 2” nu există în tabelul de căutare și toate cele trei formule VLOOKUP returnează # N / A: 
  
 O modalitate de a „prinde” eroarea NA este de a utiliza funcția IFNA astfel: 
  
 Formula din H6 este: 

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"Not found")

 Mesajul poate fi personalizat după cum doriți. Pentru a nu returna nimic (adică pentru a afișa un rezultat gol) când VLOOKUP returnează # N / A puteți utiliza un șir gol ca acesta: 

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"") // no message

 Eroarea # N / A este utilă deoarece vă spune că ceva nu este în regulă. În practică, există multe motive pentru care ați putea vedea această eroare, inclusiv: 

 Valoarea căutării nu există în tabel 
 Valoarea căutării este greșită sau conține spațiu suplimentar 
 Modul de potrivire este exact, dar ar trebui să fie aproximativ 
 Intervalul tabelului nu este introdus corect 
 Copiați VLOOKUP și referința la tabel nu este blocat 

 Citiți mai multe: VLOOKUP fără erori # N / A 
 Mai multe despre VLOOKUP 

 Mai multe exemple VLOOKUP 
 videoclipuri VLOOKUP 
 23 de sfaturi pentru utilizarea VLOOKUP 

 Alte note 

 Range_lookup controlează dacă valoarea trebuie să se potrivească exact sau nu. Valoarea implicită este TRUE = permite potrivirea non-exactă. 
 Setați range_lookup la FALSE pentru a necesita o potrivire exactă și TRUE pentru a permite o potrivire non-exactă. 
 Dacă range_lookup este TRUE ( setare implicită), o potrivire non-exactă va face ca funcția VLOOKUP să se potrivească cu cea mai apropiată valoare din tabel care este încă mai mică decât valoarea. 
 Când range_lookup este omis, funcția VLOOKUP va permite un non-exact potrivire, dar va folosi o potrivire exactă dacă există una. 
 Dacă range_lookup este TRUE (setarea implicită), asigurați-vă că valorile căutării din primul rând al tabelului sunt sortate în ordine crescătoare. În caz contrar, VLOOKUP poate returna o valoare incorectă sau neașteptată. 
 Dacă range_lookup este FALS (necesită potrivire exactă), valorile din prima coloană a tabelului nu trebuie să fie sortate.