1. Intro VLOOKUP; V = Verticala

Va anunt inca de pe acum ca acest articol este unul amplu, in care arat tot ceea ce stiu in materie de VLOOKUP. Si nu oricum, ci prin intermediul a nu mai putin de 31 de exemple concrete si studii de caz.

Ready?

Cand se foloseste functia VLOOKUP?

Ce este VLOOKUP? Pentru inceput, este o functie Excel. Ce face? Acesta cauta o valoarea specificata si returneaza o valoare potrivita dintr-o alta coloana. Mai tehnic, functia VLOOKUP cauta o valoare in prima coloana a unui tabel specificat si returneaza o valoare de pe randul gasit dintr-o alta coloana a acelui tabel unde se cauta.

Exemplul 1: Care este valoarea de la Bucuresti din Trimestrul 3?

=VLOOKUP(„Bucuresti”, A2:E10, 4,FALSE)

Observati formula de mai sus.

Ce caut? Raspuns: Bucuresti

Unde caut Bucuresti? Raspuns: Pe prima coloana din tabelul specificat aici: A2:E10, adica pe coloana A2:A10

De pe a cata coloana din Tabelul A2:E10 dorim sa ni se returneze rezultatul in cazul in care este gasit Bucuresti pe prima? Raspuns: De pe coloana nr 4 (adica trimestrul 3)

Dorim o cautare aproximativa? Raspuns: False (dorim o cautare exacta a textului Bucuresti). Daca nu se gaseste exact textul Bucuresti pe coloana A2:A10 sa fim anuntati, cu eroarea #N/A (not available)

2. Sintaxa functiei VLookup

=VLOOKUP(valoare_cautata, tabel_unde_caut, nr_coloana_rezultat, tip_cautare)

Explicatii argumente:

valoare_cautata – aceasta este valoarea de cautare pe care incercati sa o gasiti in coloana cea mai din stanga a unui tabel (dat la argumentul 2). Ar putea fi o valoare, o referinta de celula sau un sir de text. In exemplul nostru de mai sus: „Bucuresti”

tabel_unde_caut – acesta este tabelul in care cautati valoarea. In exemplul nostru este tabelul A2:E10. Atentie ca valoarea de la argumentul 1 („Bucuresti”) va fi cautata numai in prima coloana a tabelului specificat la argumentul 2( A2:E10)

nr_coloana_rezultat – acesta este numarul indexului de coloana de la care doriti sa obtineti valoarea potrivita. In cazul nostru, pentru ca dorim valoarea de pe coloana Trimestrul 3, trebuie sa-I spunem a cata coloana este raportat la tabelul A2:E10 (este a patra)

tip_cautare – aici specificati daca doriti o potrivire exacta sau o potrivire aproximativa. Daca este omisa, aceasta implicita este TRUE – potrivire aproximativa (a se vedea notele suplimentare de mai jos). Atentie aici deoarece in majoritatea covarsitoare a cazurilor veti dori sa cautati exact (adica trebuie pus FALSE la acest argument). Explic mai jos care este diferenta intre TRUE si FALSE pentru acest argument.

Am speranta ca in mare ati inteles sintaxa de baza a functiei VLOOKUP, urmeaza mai jos zeci de exemple practice cu aceasta functie:

3. Exemple simple (inca doua exemple simple si apoi „decolam” 😊)

Exemplul 2: In tabelul de mai jos care este profesia angajatului care are numele Radulescu?

Poate imi spuneti ca raspunsul este: manager. Insa cum ati facut?

Ati cautat ceva. Ce anume? Textul „Radulescu”!

Ati cautat in prima coloana a carui tabel? B2:E6

De pe randul gasit, de pe a cata coloana ati vrut rezultatul? A patra (Coloana Profesia este a patra coloana din tabelul B2:E6)

Vrem sa cautam aproximativ? FALSE, pentru ca vrem sa fim anuntati cu eroarea  #N/A (not available) daca nu se gaseste textul „Radulescu” cautat

Scriem totul in formula:

=Vlookup(„Radulescu”, B2:E6, 4, false)

 returneaza „manager”

Dupa acelasi principiu, daca doream pentru Radulescu sa aflu prenumele sau? Returnam de pe randul gasit, de pe a doua coloana

=Vlookup(„Radulescu”, B2:E6, 2, false)

 returneaza „Ion”

Daca insa voiam sa gasesc Nr Crt al lui Radulescu? (adica nr 3, vezi tabel)

Nu se poate, deoarece functia Vlookup stie sa returneze doar valoarea aflata in dreapta coloanei unde se cauta, nu o valoare aflata in stanga.

E, nu se poate, ba se poate insa cu o formula mai complexa. Va rog sa parcurgeti Partea 2 a acestui articol (Vlookup pentru avansanti), explic acolo cum se poate returna o valoare aflata in stanga coloanei unde se face cautarea.

Exemplul 3: Cum cautam cu VLOOKUP o valoare scrise intr-o celula Excel?

In exemplul anterior, presupunem ca textul Radulescu este scris intr-o celula separata si dorim sa aflam Data Nasterii pentru numele respectiv:

=VLOOKUP(G3,B2:E6, 3,FALSE)

Acum scrieti un alt nume in celula G3, automat Excel gaseste data nasterii pentru noul nume. Daca se scrie un nume care nu exista in coloana unde cautam (G2:G6), Excel va returna eroarea #N/A (vezi animatia).

4. Cum cautam cu VLOOKUP in alte foi

Exemplul 4: Il cautam pe Radulescu aflat scris in alta foaie

Daca textul pe care il cautam (Radulescu) se afla in alta foaie, iar formula este scrisa in foaia unde se afla tabelul unde cautam:

=VLOOKUP(G3,B2:E6, 3,FALSE)

Trebuie sa specificam in fata adresei de celule pe care o cautam numele foii din care face parte. In formula de mai sus am specificat ca urmeaza sa caut valoarea din celula E3 aflata in foaia ‘Foaia cu textul de cautat’

Exemplul 5: Daca tabelul unde cautam se afla in alta foaie:

Daca tabelul unde cautam se afla in alta foaie, iar formula o scriem in foaia unde se afla textul pe care il cautam (unde este scris Radulescu):

=VLOOKUP(E3,’Foaia cu tabelul’!B2:E6, 3, FALSE)

In acest caz trebuie sa specificam in fata blocului de celule cu tabelul unde se face cautarea numele foii din care face parte. In formula de mai sus am specificat ca urmeaza sa caut valoarea din celula E3 (din foaia curenta) in tabelul B2:E6 aflata in foaia ‘Foaia cu tabelul’.

5. Cum cautam cu VLOOKUP in alte carti (workbooks)

Exemplul 6: Tabelul unde cautam se afla in alt fisier

Presupunem ca tabelul unde se face cautarea se afla in alt fisier, in acest caz iata cum arata adaptat exemplul de mai sus:

=VLOOKUP(E3,'[Vlookup Part 1.xlsx]Foaia cu tabelul’!$B$2:$E$6, 3, FALSE)

In acest caz trebuie sa prefixam adresa tabelului nu numai cu numele foii din care face part, dar si cu numele fisierului .xlsx

Sintaxa finala de cautare intr-un tabel aflat la distanta (alta foaie, alta carte):

=VLOOKUP(celula_foaie_curenta,'[nume_fisier.xlsx]nume foaie’!tabel_unde_caut, index_coloana, FALSE)

Exemplul 7: Valoarea cautata se afla in alt fisier:

Sintaxa finala de cautare intr-un tabel aflat in foaia curenta pe baza unei celule aflate la distanta (alta foaie, alta carte):

=VLOOKUP(‘[nume_fisier.xlsx]nume foaie’!celula_foaie_curenta,tabel_unde_caut, index_coloana, FALSE)

6. VLOOKUP si Data Validation (liste drop-down)

Exemplul 8: Cautarea unei valori scrise intr-o celula ce contine un drop down list:

In exemplul simplu de mai jos s-a cautat pentru Brasov valoarea de la Trimestrul 2:

Dorim sa nu fim nevoiti sa scriem manual in celula G4, de fiecare data noul oras pentru care dorim sa realizam cautarea. Vrem sa alegem Orasul dintr-o lista predefinita de Orase (combo box sau drop down list) (vezi animatia):

Presupunand ca avem undeva in Excel extrasa o lista cu valorile unice ale Oraselor, putem foarte usor sa implementam un Data Validation de tip lista pentru celula G4 (vezi animatia):

7. VLOOKUP cu Named Range

Named Range reprezinta un instrument foarte util in Excel, acesta ne permite sa dam denumiri usor de tinut minte blocurilor de celule cu care lucram frecvent in formule.

De pilda, in ultimele noastre exemple pentru a ne referi la blocul de celule in care se face cautarea, a trebuit sa scriem de fiecare data adresa acelui tabel:  A2:E10

Putem sa cream un Named Range pentru tabelul A2:E10, sa-i spunem Orase (vezi cum se face in animatie):

Exemplul 9: Cautarea intr-un tabel pentru care avem facut un name range:

In acest caz, putem sa scriem formula de cautare mult mai elegant (vezi animatia):

=VLOOKUP(G4, Orase,3, FALSE)

8. VLOOKUP cu cautare aproximativa (true al patrulea argument)

Exemplul 10: Cautarea unei valori aproximative cu Vlookup

Avem un Target minimal de indeplinit: 39999

Dorim sa gasim numele Filialei cu cel mai apropiat Total de Target insa mai mic decat acel Target-ul (39999)

Cu alte cuvinte: Care este prima filiala care NU si-a indeplinit target-ul?

In acest caz nu mai putem sa folosim functia Vlookup cu cautare exacta, deoarce nu exista nicio valoare fixa de cautat , ci una aproximativa (adica apropiata de 39999 insa mai mica decat ea)

Pentru a gasi valoarea cea mai apropiata mai mica de un numar dat (39999), lucram in 2 pasi:

Pasul 1: Sortam tabelul unde se face cautarea dupa coloana de cautat (coloana Total)

Pasul 2: Cautam target-ul (39999) in coloana  Total, punand la final (la al patrulea argument) valoarea True (adica cautare aproximativa)

Obs.: Ca si in cazul cautarii exacte, retineti ca Vlookup nu stie sa caute decat la dreapta unei coloane date (nu la stanga).

In cazul nostru: coloana de unde vrem rezultatul (coloana Filiala) se afla la stanga coloanei de cautat (Total), asa incat va trebui sa mutam coloanele astfel:

Pasul 1: Sortam tabelul dupa coloana Total (vezi animatia):

Pasul 2: Scriem formula de cautare aproximativa (TRUE la final):

=VLOOKUP(E3,A2:B10, 2, TRUE)

Exemplul 11: Cautarea intr-o grila de calificative (cautare aproximativa):

Vrem sa scriem o formula in celula C2 care sa afiseze litera calificativului din tabelul din dreapta (in functie de Total)

Pasul 1: Sortam tabelul din dreapta ascendent dupa coloana Total:

Pasul 2: Scriem formula cu Vlookup in celula C2:

=VLOOKUP(A2, $E$3:$F$6, 2, TRUE)

Am blocat cu semnul dolar tabelul unde se face cautarea ($E$3:$F$6), deoarece urmeaza sa copiem in jos aceasta formula (peste toata coloana Calificativ):

9. Cautare Partiala cu Vlookup

Caracterele wildcards permit cautari ce au o „potrivire partiala”.

In Excel exista trei caractere speciale (metacaractere sau wildcards) dintre care acestea doua sunt cel mai des folosite:

* –  inlocuieste oricate caractere

? – inlocuieste un singur caracter

Exemplul 12: Sa se gaseasca numele complet al companiei in functie de o cautare partiala (vedeti poza de mai jos):

Textul de cautat partial: „*Amazon*”, cu Amazon este scris in celula C2, textul de cautat devine: „*” & C2 & „*”

Unde caut numele complet al companiei: A2:A6

De pe a cata coloana dorim rezultatul: 1 (pentru ca tabelul unde caut are o singura coloana)

Cum caut: exact (FALSE)

=VLOOKUP(„*” & C2 & „*”, A2:A6, 1, FALSE)

10. Inlocuirea IF-urilor multiple cu un singur VLOOKUP

In tabelul de mai jos se doreste scrierea procentului de indexare in dreptul fiecarui angajat pe baza profesiei:

Pentru ca exista 5 profesii diferite pe baza carora se stabileste Procentul de indexare, am putea sa scriem 4 if-uri imbricate, astfel:

=IF(B9=”inginer”,10%,IF(B9=”asistent”,5%, IF(B9=”manager”,14%,IF(B9=”avocat”,20%,25%))))

O astfel de metoda nu are cum sa se aplice prea des, este greu de scris si de intretinut, in plus daca avem mai multe decizii de luat, devine chiar imposibil. Asa incat o sa aplicam o alta metoda:

Exemplul 13: Cum inlocuiesc IF-urile multiple imbricate (nested) cu un singur VLOOKUP:

Pasul 1: Scriem toate procentele de indexare separat, intr-un panou de control:

Pasul 2: In celula D9 scriem o formula VLOOKUP care cauta profesia randului curent in panoul de control de sus si ne returneaza valoarea din panou de pe a doua coloana:

=VLOOKUP(B9,$A$2:$B$6, 2,FALSE)

11. Valoarea VLOOKUP – #N/A si functia IFNA

In exemplul de mai sus a aparut eroarea #N/A deoarece Radulescu are Profesia hr (profesie ce nu exista in nomenclatorul de sus).

Exemplul 14: Tratarea exceptiei #N/A cu functia IFNA

Dorim sa afisam un text personalizat inlocul acelei erori:

=IFNA(VLOOKUP(B9,$A$2:$B$6, 2,FALSE), „Nu exista”)

Cum functioneaza functia IFNA de mai sus?

Functia IFNA evalueaza expresia de la argumentul 1 (in cazul nostru: VLOOKUP(B9,$A$2:$B$6, 2,FALSE)) si daca aceasta returneaza eroarea #N/A , atunci functia IFNA va returna valoarea de la argumentul 2(in cazul nostru textul „Nu exista”).

Daca expresia de la argumentul 1 NU contine exceptia #N/A, atunci functia IFNA returneaza direct rezultatul de la argumentul 1

12. Excel VLOOKUP – lucruri de retinut!

-Functia VLOOKUP nu poate privi spre stanga coloanei unde se cauta.

– Functia VLOOKUP returneaza doar prima valoare gasita pe coloana unde face cautarea (prima de sus in jos). Nu cautati cu VLOOKUP pe o coloana cu valori duplicate (decat daca sunteti experti in Excel 😊)

-Excel VLOOKUP nu este sensibila la litere mari sau mici, ceea ce inseamna ca literele minuscule si majuscule sunt tratate ca echivalent.

– Daca al treilea parametru este mai mic decat 1, eroarea #VALUE! este returnata. In cazul in care este mai mare decat numarul de coloane din tabel, formula va returna eroarea #REF!.

– Atunci cand cautati cu o potrivire aproximativa (range_lookup setat la TRUE sau omis), intotdeauna trebuie sa sortati ascendent tabelul unde cautati dupa coloana de cautare.

Acum sunteti pregatiti sa treceti la nivelul urmator si va invit sa parcurgeti articolul:

Vlookup pentru avansati (Partea II)

Va multumesc pentru ca ati citit pana la final!