1. Gaseste o linie intreaga Metoda 1: (nr de coloane scrise manual deasupra antetului)

Avem tabelul de mai jos, dorim sa scriem un Nr Crt de Angajat intr-o celula separata si dorim sa gasim intreaga linie a angajatului respectiv (valorile de pe toate coloanele):

In dreptul fiecarei coloane din tabelul de mai sus scriem un numar ce reprezinta indexul aferent coloanei (Nr Crt este 1, Nume este 2, etc.)

(vezi animatia)

Scriem prima formula cu Vlookup in celula B3 (pentru aflarea numelui):

=VLOOKUP($A3,$A$6:$H$10, B1, FALSE)

Observati ca indexul de coloana l-am pasat dinamic (via celula B1). Acum cand tragem de manerul de umplere al celulei B3 spre dreapta se vor gasi si celelalte valori ale randului gasit:

Daca inseram sau stergem coloane in tabelul unde se face cautarea, trebuie obligatoriu sa intretinem si indexul de pe linia de sus ajutatoare (vezi animatia).

2. Gaseste o linie intreaga Metoda 2: folosind functia COLUMNS

Nu este elegant sa adaugam de fiecare data linia ajutatoare cu indexul de coloana. Dorim sa avem acces la numarul coloanei in mod automat (fara linie ajutatoare):

Functia Columns returneaza numarul total de coloane dintr-un set de celule dat

=VLOOKUP($A3,$A$6:$H$10, COLUMNS($A$6:B10), FALSE)

Cand o sa copiem formula din B3 spre dreapta, observati ca blocul de celule $A$6:B10 (pasat functiei Columns) devine din ce in ce mai mare (pentru ca adresa B10 este relativa, iar adresa $A$6 este fixa):

De pilda, in E3 formula o sa fie:

=VLOOKUP($A3,$A$6:$H$10, COLUMNS($A$6:E10), FALSE)

Chiar si aceasta metoda prin care am gasit dinamic indexul de coloana are o problema. Daca schimbam ordinea coloanelor in tabelul unde se face cautarea, riscam ca formula sa ne intoarca rezultatul de pe alta coloana. Vezi animatia:

3. Gaseste o linie intreaga Metoda 3: Vlookup cu MATCH

Putem sa folosim si functia Match pentru a afla numarul coloanei de unde se doreste rezultatul folosind functia MATCH (care poate cauta coloana respectiva dupa numele sau)

=VLOOKUP($A3,$A$6:$H$10, MATCH(E2,$A$5:$H$5,0), FALSE)

Observati ca nu mai conteaza ordinea coloanelor din cele doua tabele. Functia Match cauta eticheta de coloana si returneaza dinamic indexul sau. In cazul de mai sus, cand am cautat Profesia, formula MATCH(E2,$A$5:$H$5,0) intoarce indexul coloanei Profesia (care in acest caz este 6 si nu 5) 😊

4. Afiseaza linia intreaga gasita pe verticala: cu functia ROWS

Dorim sa gasim intreg randul insa vrem ca acesta sa fie afisat pe verticala (nu pe orizontala).

Folosim functia Rows care intoarce numarul de randuri al unui bloc de celule dat:

=VLOOKUP(B$8,$A$2:$H$6, ROWS(A$2:H2) +1,0)

In cazul de mai sus: ROWS(A$2:H2) +1 va returna 2, iar Vlookup va returna valoarea de pe coloana 2 (adica Nume)

Cand o sa copiem formula in jos: pentru Prenume vom avea ROWS(A$2:H3) +1 care returneaza 3 😊(observati ca H3 nu a fost blocat la nivel de rand, cu alte cuvinte o data cu copierea in jos a formulei numarul de randuri pentru block-ul pasat functiei Rows creste).

Avantajul acestei metode consta in faptul ca avem linia de rezultat facuta Transpose automat.

5. Cautare pe doua coloane Metoda 1: Cu concatenarea lor pe o coloana ajutatoare

Primesc des la clasa urmatoarea intrebare, cum putem sa gasim o linie intr-un tabel in cazul cautarii facute dupa doua valori (aflate pe 2 coloane). Fie tabelul urmator:

Sa se gaseasca salariul pentru cel care are Numele Radulescu, iar Prenumele Ion (presupunem ca in tabelul unde cautam exista un singur astfel de om). Observati ca in tabel exista mai multi oameni cu Numele Radulescu si mai multi cu Prenumele Ion, insa numai unul care are in acelasi timp Numele si Prenumele cautate.

Pasul 1: Facem o coloana ajutatoare in tabelul unde urmeaza sa facem cautarea. Aceasta coloana ajutatoare va contine valorile concatenate din cele doua coloane (Nume si Prenume) – vezi animatia:

Vom face acum cautarea pe coloana ajutatoare, vom cauta concatenarea Nume cu Prenume in coloana noua:

=VLOOKUP(A9&B9, $C$2:$D$6, 2,0)

6. Cautare pe mai multe coloane simultan cu Vlookup Metoda 2: folosind functia CHOOSE

Metoda de mai sus nu este eleganta. De fapt orice metoda care presupune crearea unei coloane ajutatoare este neprofesionista.

Dorim sa facem cautarea pe doua (sau mai multe coloane) folosind VLOOKUP si FARA coloana ajutatoare.

Cu ajutorul functiei CHOOSE o sa creem un range dinamic format din 2 coloane:

–  prima coloana reprezinta o concatenare a coloanelor unde se va face cautarea

– a doua coloana este cea de pe care se doreste rezultatul

CHOOSE({1,2}, Coloana1 & Coloana2, Coloana de unde vreau rezultatul)

Atentie! Trebuie introdusa combinatia de taste CTRL + SHIFT + Enter deoarece aceasta formula este una bazata pe vectori

Pentru a intelege mai sus cum functioneaza formula de mai sus, va rog ca dupa ce o scrieti inainte de a da CTRL + SHIFT + ENTER sa selectati CHOOSE({1,2},A2:A6&B2:B6,D2:D6) si sa apasati tasta F9, vedeti animatia:

Practic, functia CHOOSE intoarce dinamic intreg tabelul unde se face cautarea (cu cele doua coloane, prima deja concatenata si a doua de unde se doreste valoarea cautata).

7. Cum gasesc cu VLOOKUP valoarea aflata in stanga coloanei unde fac cautarea

Va supun atentiei tabelul de mai jos:

Dorim sa gasim Salariul corespondent lui Radulescu (pe baza Nr Crt 3) si nu dorim sa mutam coloana Nr Crt in stanga coloanei Salariul.

Tot cu ajutorul functiei CHOOSE creem dinamic tabelul unde se va face cautarea (care va incepe cu coloana Nr Crt, iar a doua este Salariul)

{=VLOOKUP(A9,CHOOSE({1,2},C2:C6,B2:B6),2,FALSE)}

Atentie! Aceasta este o formula bazata pe vectori si trebuie ca la final sa apasati CTRL + SHIFT + ENTER (nu scrieti acoladele manual ca nu functioneaza).

8. TRIM si VLOOKUP: tratarea spatiilor inainte si dupa Vlookup

Se poate intampla ca celula care contine valoarea de cautat sa contina spatii aditionale (inaintea, dupa sau in interior).

Scenariul 1: Valoarea de cautat contine spatii aditionale:

=VLOOKUP(TRIM(A9), A2:B6, 2,0)

In exemplul de mai sus Vlookup cauta ce returneaza functia Trim (fara spatii inutile in Nume).

Scenariul 2: Coloana unde se cauta contine spatii inutile

Observati in tabelul de mai sus ca in tabelul unde se cauta exista multe spatii ce trebuiesc ignorate la cautare:

{=VALUE(VLOOKUP(A9, TRIM(A2:B6), 2,0))}

Atentie! Aceasta este o formula bazata pe vectori si trebuie ca la final sa apasati CTRL + SHIFT + ENTER (nu scrieti acoladele manual ca nu functioneaza).

Formula TRIM(A2:B6) returneaza un vector cu coloanele tabelului (fara spatii aditionale in ele)

Functia Trim converteste toate coloanele la TEXT, inclusiv ultima coloana (Salariul), asa incat suntem nevoiti cu functia Value sa facem din nou valoarea returnata numar.

Functia Value transforma textul „4500” in numarul 4500

9. VLOOKUP in 3 tabele diferite: functia CHOOSE

=VLOOKUP(I14,CHOOSE(IF(J11=2018,1,IF(J11=2019,2,3)), $A$3:$E$8,$G$3:$K$8,$C$13:$G$18),MATCH(J13,$A$2:$E$2,0),0)

Explicatii pentru formula de mai sus:

• In functie de anul ales functia IF returneaza dinamic un anumit numar de la 1 la 3. De pilda, daca se alege anul 2019, functia IF returneaza numarul 2

• Functia CHOOSE, pe baza numarului returnat de IF, returneaza un anumit block , de exemplu, daca IF returneaza 2 (adica anul 2019) atunci functia Choose din cele trei tabele enumerate, il va returna pe al doilea ($G$3:$K$8)

• Functia MATCH cauta indexul de coloana pe baza Trimestrului. De exemplu daca se cauta Trimestrul 2, functia MATCH returneaza numarul 3 (pentru ca in oricare dintre cele 3 tabele, coloana Trimestrul 2 este situata a treia)

• Functia VLOOKUP cauta o filiala in tabelul returnat de CHOOSE si returneaza valoarea aflata pe coloana intoarsa de MATCH (in cazul nostru cautarea filialei Brasov se face in tabelul cu anul 2019 si se intoarce valoarea aflata pe coloana a treia – Trimestrul 2)

Verificam daca formula este scrisa corect (vedeti animatia):

10. VLOOKUP imbricat (nested VLOOKUP) sau VLOOKUP in cascada

Avem trei tabele, insa de data aceasta cele trei tabele sunt relationate (contin coloane in comun):

Pe baza ID-ului de Client stiut (3) se gaseste usor numele Clientului deoarece trebuie facuta o simpla cautare cu Vlookup in tabela de Clienti:

=VLOOKUP(A16,$A$3:$B$5,2,0)

Insa pentru gasirea numelui de Produs cumprat trebuie sa facem doua cautari:

• O cautare in tabela de Tranzactii pentru a afla pe baza id-ului de client care este id-ul de produs

=VLOOKUP(A16,C10:D11,2,0)

• O cautare in tabela de Produse pentru a gasi numele produsului pe baza ID-ului de produs gasit mai sus

=VLOOKUP(VLOOKUP(A16,C10:D11,2,0),F3:G6,2,0)

11. Cum sa-l fac pe Vlookup sa functioneze case-sensitive?

Vlookup ca majoritatea functiilor Excel, NU este case-sensitive, cu alte cuvinte nu stie sa faca diferente intre caracterele majuscule sau minuscule.

In exemplul de mai sus, daca incercam sa gasim Vanzarile lui ION, o sa ne dea 102 (pentru ca este primul Ion din tabel) si nu 155 (al doilea ION, cel scris cu majuscule)

Pasul 1: Avem nevoie de o coloana ajutatoare care sa contina un numar unic al randului curent:

Formula de pe aceasta coloana este:

=ROW()

Functia ROW() va intoarce nr liniei curente din tabel

Pasul 2: Scriem formula cu vectori care aduce valoarea Vanzarii pentru ION

{=VLOOKUP(MAX(EXACT(E2,A2:A6)*(ROW(A2:A6))),B2:C6,2,0)}

Explic pe bucatele formula de mai sus:

EXACT(E2,A2:A6) – functia EXACT compara valoarea de cautare din E2 cu toate valorile din A2:A6. Acesta returneaza un vector de TRUE / FALSE in care TRUE este returnat acolo unde exista o potrivire exacta. In acest caz, s-ar intoarce urmatorul tablou: {FALSE;FALSE;FALSE;TRUE;FALSE}

EXACT(E2,A2:A6)*(ROW(A2:A6))– Se inmulteste vectorul explicat mai sus cu numarul de rand. Oriunde exista un TRUE, acesta da numarul randului , altfel da 0. In acest caz, s-ar intoarce {0;0;0;5;0}

MAX(EXACT(E2,A2:A6)*(ROW(A2:A6))) – Aceasta parte returneaza valoarea maxima din tabloul de numere de mai sus. In acest caz, s-ar intoarce 5 (care este numarul de rand in care exista o potrivire exacta).

Acum folosim acest numar ca valoare de cautare si in tabelul B2:C6

Atentie! Deoarece aceasta este o formula vectoriala, utilizati Ctrl + Shift + Enter in loc sa introduceti doar ENTER (nu scrieti acoladele manual pentru ca nu functioneaza)

12. Limitele Vlookup

Cateva observatii la finalul articolului VLOOKUP pentru avansati:

• Vlookup are nevoie la al treilea argument de un numar ce reprezinta index-ul de coloana de unde dorim rezultatul

• Indexul de coloana de mai sus se poate afla asa cum ati vazut in articolul curent cu functia COLUMNS sau cu functia MATCH

• Vlookup stie sa returneze rezultatul doar dintr-o coloana aflata la dreapta coloanei unde se cauta. Se poate trece peste aceasta limitare folosind functia CHOOSE (asa cum ati vazut in acest articol)

• Functia VLOOKUP stie sa caute doar pe verticala (doar de sus in jos), daca dorim sa cautam pe orizontala (stanga-dreapta) putem sa folosim functia HLOOKUP sau o combinatie intre functiile INDEX si MATCH

• Functia VLOOKUP NU este case-sensitive. Daca ne intereseaza sa cautam exact si sa se tina cont de tipul literelor (majuscule/minuscule) trebuie sa lucram vectorial cu o combinatie de functii MAX/EXACT si VLOOKUP (asa cum am aratat in acest articol)

• Vlookup intoarce doar prima valoare gasita (de sus in jos), daca dorim a doua valoarea, a treia sau a N-a trebuie sa lucram vectorial cu combinatia de functii INDEX si MATCH

13. Solutii alternative la limitele Vlookup

Cele mai puternice functii de cautare din Excel sunt INDEX si MATCH. Aceste doua functii lucreaza in tandem pentru rezolvarea oricarui tip de cautare.

Daca doriti sa treceti peste limitele native VLOOKUP (o parte dintre ele amintite mai sus), trebuie a invatati sa folositi functiile INDEX-MATCH

In partea a treia adresata functiilor LOOKUP am sa va arat cum se lucreaza cu cele mai puternice functii de regasire date din EXCEL.

Va multumesc ca ati avut rabdare sa ma urmariti pana la final si va astept cu drag saptamana viitoare 😊