Invatare Atomica

Functii de cautare si referinta: VLOOKUP, HLOOKUP, INDEX si MATCH

Cum gasesti automat o informatie intr-un tabel mare, fara sa cauti manual cu ochii

Progres lectie:
0%
🎯

De ce conteaza aceasta lectie

Orice registru de firma — evidenta silvica pe parcele, buletine de analiza a probelor de mediu, fisele tehnice dintr-un atelier de mecanica — creste in timp la sute sau mii de randuri. Cand ai nevoie de o singura informatie (pretul unui produs, tariful pentru o specie de arbore, incadrarea unui indicator de calitate a apei), nu o cauti cu ochii randand-o pe randand. Foaia de calcul poate cauta ea insasi, instant, daca ii spui exact ce cauta, unde cauta si ce sa aduca inapoi. Aceasta lectie te invata cele patru functii cu care se face acest lucru: VLOOKUP, HLOOKUP, INDEX si MATCH.

Dupa aceasta lectie vei putea:

  • Sa folosesti VLOOKUP pentru a cauta o valoare pe verticala, cu potrivire exacta
  • Sa folosesti VLOOKUP cu potrivire aproximativa pentru incadrarea in intervale (transe, categorii, praguri)
  • Sa folosesti HLOOKUP pentru a cauta o valoare atunci cand tabelul e organizat pe orizontala, cu antete pe randuri
  • Sa combini INDEX si MATCH pentru a cauta o valoare in orice directie, inclusiv spre stanga
  • Sa explici de ce INDEX+MATCH rezista la inserarea unei coloane noi, iar VLOOKUP se poate strica
  • Sa recunosti si sa repari cea mai frecventa greseala la aceste functii: eroarea #N/A din referinte care aluneca la copiere

Incearca singur!

Provocare — inainte sa citesti:

Esti tehnician la un ocol silvic si ai un tabel cu 400 de parcele forestiere, fiecare cu un cod (ex. UP3-45) si specia dominanta. Seful iti da un cod de parcela si iti cere pe loc specia dominanta. Cum ai proceda in Excel ca sa nu derulezi manual prin 400 de randuri de fiecare data cand primesti un cod nou? Scrie mai jos ce crezi ca ar trebui sa faca formula: ce ii dai ca informatie de intrare si ce ar trebui sa iti aduca inapoi.

💡 Ai nevoie de un indiciu?

Gandeste-te la un cabinet cu dosare numerotate: ii dai unui coleg numarul dosarului si el stie exact in ce raft si pe ce pozitie sa caute, fara sa rasfoiasca tot cabinetul. Functiile de cautare fac exact asta — primesc un cod (numarul dosarului), stiu in ce "raft" (coloana sau tabel) sa caute si ce informatie sa scoata de langa el.

Subiectul lectiei de azi raspunde exact la aceasta intrebare!

1

VLOOKUP — cautare verticala cu potrivire exacta

VLOOKUP (Vertical Lookup — cautare verticala) cauta o valoare in prima coloana a unui tabel si aduce inapoi o valoare de pe acelasi rand, dintr-o coloana pe care o alegi tu, numarand de la stanga tabelului. Sintaxa completa:
=VLOOKUP(valoare_cautata; tabel; nr_coloana; [potrivire_exacta])
Cele 4 argumente, pe rand:

valoare_cautata — ce cauti (o celula sau un text intre ghilimele), trebuie sa se afle in prima coloana a tabelului.
tabel — intervalul de celule in care se cauta (ex. A5:D400); prima coloana a acestui interval este cea in care se cauta.
nr_coloana — un numar: a cata coloana din tabel (numarand de la 1 = prima coloana a intervalului) contine rezultatul dorit.
potrivire_exacta — FALS (sau 0) pentru potrivire exacta, ADEVARAT (sau 1, sau omis) pentru potrivire aproximativa. Acest argument e opul urmator.

Exemplu numeric complet — evidenta parcelelor forestiere:

Tabel in A1:C6 (A=Cod parcela, B=Specie, C=Suprafata ha):

A1: Cod    B1: Specie    C1: Suprafata_ha
A2: UP1-10    B2: Fag    C2: 12,4
A3: UP1-11    B3: Molid    C3: 8,7
A4: UP2-05    B4: Stejar    C4: 15,1
A5: UP2-06    B5: Fag    C5: 9,3
A6: UP3-01    B6: Brad    C6: 22,0

In celula E1 scrii codul cautat: UP2-05. In F1 scrii:
=VLOOKUP(E1;A2:C6;2;0)
Excel cauta "UP2-05" in coloana A (A2:A6), il gaseste pe randul 4, si aduce valoarea din a 2-a coloana a intervalului A2:C6 — adica din coloana B, randul 4: Stejar.

2

VLOOKUP cu potrivire aproximativa — incadrarea pe transe

Cand ultimul argument este ADEVARAT (sau 1, sau il omiti), VLOOKUP nu mai cauta o potrivire identica, ci cauta cea mai mare valoare din prima coloana care este mai mica sau egala cu valoarea cautata. Se foloseste pentru incadrarea intr-un interval (transa de pret, categorie, prag), nu pentru cautarea unui cod exact. Conditie obligatorie: prima coloana a tabelului trebuie sortata crescator, altfel rezultatul e gresit si Excel nu te avertizeaza.
Exemplu numeric complet — incadrare tarif dupa debit masurat (probe de mediu):

Tabel de transe in A1:B4 (sortat crescator dupa A), reprezentand pragul minim al debitului (litri/secunda) si tariful de analiza corespunzator:

A1: 0    B1: 50
A2: 10    B2: 80
A3: 25    B3: 120
A4: 50    B4: 180

Ai masurat un debit de 32 l/s in celula D1. In E1 scrii:
=VLOOKUP(D1;A1:B4;2;1)
Excel cauta cea mai mare valoare din A1:A4 care este ≤ 32. Aceasta este 25 (de pe randul 3), deci aduce tariful de pe acelasi rand din coloana B: 120. Desi 32 nu apare exact in tabel, valoarea a fost incadrata corect in transa "25 si peste, sub 50".

Greseala tipica si cum o recunosti:

Daca tabelul de transe NU este sortat crescator (de exemplu ai adaugat o transa noua la sfarsit fara sa o rearanjezi), VLOOKUP aproximativ poate returna un tarif complet gresit, fara nicio eroare vizibila — rezultatul pare valid dar e fals. Verificare: selecteaza coloana de praguri si verifica manual, sau sorteaza-o din nou (Date → Sortare) inainte de a folosi formula.

3

HLOOKUP — cautare orizontala

HLOOKUP (Horizontal Lookup) face exact ce face VLOOKUP, dar pe orizontala: cauta valoarea in primul rand al tabelului si aduce rezultatul de pe un rand de mai jos, numarand randurile de sus in jos. Se foloseste cand antetele tabelului sunt asezate pe un rand, nu pe o coloana (de exemplu lunile anului insirate pe orizontala). Sintaxa:
=HLOOKUP(valoare_cautata; tabel; nr_rand; [potrivire_exacta])
Exemplu numeric complet — productie lunara la un atelier mecanic:

Tabel in A1:D3, cu lunile pe randul 1 si doi indicatori pe randurile 2 si 3:

A1: Luna    B1: Ianuarie    C1: Februarie    D1: Martie
A2: Piese produse    B2: 340    C2: 355    D2: 410
A3: Rebuturi    B3: 12    C3: 9    D3: 15

In F1 scrii luna cautata: Martie. In F2 scrii:
=HLOOKUP(F1;A1:D3;2;0)
Excel cauta "Martie" pe randul 1 al tabelului A1:D3, il gaseste in coloana D, si aduce valoarea de pe randul 2 al intervalului (al doilea rand numarand de sus) din aceeasi coloana: 410.

Cand alegi VLOOKUP si cand HLOOKUP:

Regula simpla: uita-te la unde stau titlurile categoriilor (Cod parcela, Luna, Produs). Daca stau intr-o coloana (una sub alta) — VLOOKUP. Daca stau pe un rand (unul langa altul) — HLOOKUP. Majoritatea registrelor de evidenta (liste lungi de inregistrari) au titlurile pe coloana, deci VLOOKUP e mai des folosit; tabelele lunare/trimestriale au adesea titlurile pe rand.

4

MATCH — nu aduce o valoare, ci o pozitie

MATCH este diferita fundamental de VLOOKUP/HLOOKUP: nu aduce o valoare din tabel, ci returneaza numarul pozitiei pe care se afla valoarea cautata in interiorul unui interval, numarand de la 1. Sintaxa:
=MATCH(valoare_cautata; interval_cautare; tip_potrivire)
unde tip_potrivire este 0 pentru potrivire exacta (cel mai des folosit), 1 pentru "cea mai mare valoare ≤" (interval sortat crescator), -1 pentru "cea mai mica valoare ≥" (interval sortat descrescator).
Exemplu numeric complet:

Folosind tabelul din Atomul 1 (specii in B2:B6: Fag, Molid, Stejar, Fag, Brad):
=MATCH("Molid";B2:B6;0)
Excel cauta "Molid" in intervalul B2:B6. Este a doua valoare din interval (B3), deci MATCH returneaza 2 — nu "B3", nu "Molid", ci numarul 2. Acest numar de pozitie este exact ce are nevoie functia INDEX din atomul urmator ca sa gaseasca rezultatul.

De ce conteaza aceasta diferenta:

MATCH e ca un coleg care iti spune "e al doilea din stiva", nu care iti aduce el foaia. Singura, MATCH pare limitata; combinata cu INDEX (atomul urmator), devine mai puternica decat VLOOKUP.

5

INDEX — aduce valoarea de pe o pozitie data

INDEX face inversul lui MATCH: primeste un numar de pozitie si aduce valoarea aflata pe acea pozitie in interiorul unui interval. Forma simpla, pe un singur rand sau o singura coloana:
=INDEX(interval; nr_pozitie)
Pentru un tabel cu mai multe randuri si coloane:
=INDEX(tabel; nr_rand; nr_coloana)
Exemplu numeric complet:

Folosind coloana de suprafete din Atomul 1 (C2:C6: 12,4 / 8,7 / 15,1 / 9,3 / 22,0):
=INDEX(C2:C6;4)
Aduce valoarea de pe a 4-a pozitie din interval, adica celula C5: 9,3.

Singura, INDEX nu cauta nimic:

INDEX are nevoie sa ii spui TU numarul pozitiei — nu stie sa caute un cod sau un nume. De aceea, folosita singura, INDEX e limitata la "adu-mi valoarea de pe pozitia N". Puterea reala apare cand pozitia N este chiar rezultatul unui MATCH, calculat automat.

6

INDEX + MATCH combinate — cautare in orice directie

Combinatia INDEX + MATCH foloseste rezultatul lui MATCH (o pozitie) ca argument de pozitie pentru INDEX. Practic, MATCH gaseste "pe ce rand se afla" valoarea cautata, iar INDEX aduce valoarea dorita de pe acel rand, din orice coloana — inclusiv una aflata la stanga coloanei de cautare, lucru pe care VLOOKUP nu il poate face niciodata (VLOOKUP cauta doar in prima coloana a tabelului si aduce rezultate spre dreapta ei).
Exemplu numeric complet — cautare "spre stanga", buletin de analiza a apei:

Tabel in A1:C4, unde coloana C este Codul probei si coloana A este Parametrul masurat, aflat la STANGA fata de cod — exact situatia in care VLOOKUP nu poate ajuta:

A1: Parametru    B1: Valoare_masurata    C1: Cod_proba
A2: pH    B2: 7,2    C2: PR-101
A3: Turbiditate    B3: 3,4    C3: PR-102
A4: Oxigen dizolvat    B4: 8,9    C4: PR-103

Vrei sa afli Parametrul (coloana A, aflata la STANGA) pornind de la Codul probei "PR-102" (coloana C, aflata la dreapta). VLOOKUP nu poate face asta direct. Cu INDEX+MATCH:
=INDEX(A2:A4;MATCH("PR-102";C2:C4;0))
Pas cu pas: MATCH("PR-102";C2:C4;0) cauta "PR-102" in C2:C4 si gaseste ca e a doua pozitie → returneaza 2. Formula devine INDEX(A2:A4;2), care aduce a doua valoare din A2:A4: Turbiditate.

De ce INDEX+MATCH rezista la inserarea unei coloane, iar VLOOKUP nu:

VLOOKUP foloseste un numar fix de coloana (al treilea argument, ex. "3"). Daca cineva insereaza o coloana noua in mijlocul tabelului (de exemplu adauga "Data recoltarii" intre Valoare_masurata si Cod_proba), toate coloanele de dupa punctul de inserare se muta cu o pozitie la dreapta, dar numarul "3" din formula VLOOKUP ramane acelasi — asa ca formula incepe sa aduca date din coloana gresita, fara nicio eroare vizibila.

INDEX+MATCH nu foloseste un numar fix de coloana: foloseste direct referinta la coloana (A2:A4). Daca se insereaza o coloana noua oriunde in tabel, Excel actualizeaza automat referintele de celule, iar formula continua sa aduca din coloana corecta. Din acest motiv, in registre care se modifica des (se adauga coloane noi de-a lungul anului), INDEX+MATCH este alegerea mai sigura pe termen lung.

7

Greseala frecventa — #N/A si referinte care aluneca

Cea mai frecventa problema la aceste functii este eroarea #N/A ("valoarea nu a fost gasita"), chiar si atunci cand valoarea "pare" sa existe in tabel. Doua cauze domina in practica.
Cauza 1 — diferenta de format sau spatii invizibile:

Daca in E1 scrii codul "UP2-05" ca text, dar in coloana A codurile sunt stocate ca numere (sau invers), sau daca una dintre valori are un spatiu in plus la inceput/sfarsit, VLOOKUP nu le considera identice, desi arata la fel pe ecran. Recunoastere: #N/A apare desi vezi valoarea cu ochii in tabel. Reparare: verifica alinierea celulei (textul se alinieaza implicit la stanga, numerele la dreapta — un indiciu vizual rapid) si foloseste functia =TRIM(E1) pentru a elimina spatiile suplimentare (nu confunda cu CURATARE/CLEAN, care sterge caractere netiparibile, nu spatii).

Cauza 2 — tabelul de cautare "aluneca" la copierea formulei:

Daca scrii =VLOOKUP(E1;A2:C400;2;0) in randul 2 si tragi formula in jos pe alte 50 de randuri, Excel muta automat si intervalul A2:C400 cu fiecare rand (devine A3:C401, apoi A4:C402...), iar la un moment dat tabelul "iese" din datele reale si formula incepe sa dea #N/A sau rezultate gresite. Reparare: blocheaza intervalul tabelului cu simbolul $ inainte de a copia formula: =VLOOKUP(E1;$A$2:$C$400;2;0). Semnul $ inainte de litera coloanei si de numarul randului fixeaza acea referinta, astfel incat la copiere ramane neschimbata, in timp ce E1 (fara $) se adapteaza la fiecare rand nou.

8

Recapitulare si conexiuni

Toate cele patru functii rezolva aceeasi nevoie de baza — gasirea automata a unei informatii intr-un tabel — dar difera prin directie, flexibilitate si rezistenta la modificari ulterioare ale tabelului.
Rezumat comparativ:

VLOOKUP — cauta pe verticala, in prima coloana; rezultat obligatoriu la dreapta; simplu de scris, dar fragil la coloane inserate.
HLOOKUP — identic cu VLOOKUP, dar pe orizontala (cauta in primul rand).
MATCH — nu aduce o valoare, ci o POZITIE (un numar).
INDEX — aduce valoarea de pe o pozitie DATA, in orice directie.
INDEX+MATCH — combinatia lor: cauta in orice directie (inclusiv spre stanga) si nu se strica la inserarea unei coloane, pentru ca nu foloseste un numar fix de coloana.

Regula practica de alegere:

Pentru cautari rapide, ocazionale, intr-un tabel stabil (care nu se modifica structural) — VLOOKUP ramane suficient si mai usor de citit. Pentru registre de lucru care cresc si se modifica des (coloane adaugate, reordonate) sau cand ai nevoie sa cauti spre stanga — INDEX+MATCH este alegerea profesionala.

Conexiune cu urmatoarea lectie:

Ai invatat sa gasesti si sa aduci informatii dintr-un tabel existent. In lectia urmatoare vei invata functii pe siruri de caractere si functii financiare, plus cum sa iti construiesti propriile functii de utilizator, atunci cand niciuna dintre functiile predefinite nu face exact ce ai nevoie.

Exercitii practice

Exercitiul 1 (Nivel minim) — Cautare exacta cu VLOOKUP

Ai urmatorul tabel de evidenta a uneltelor din atelier, in A1:C6 (A=Cod_unealta, B=Denumire, C=Stare):

A1: Cod_unealta, B1: Denumire, C1: Stare
A2: U01, B2: Polizor unghiular, C2: Functional
A3: U02, B3: Presa hidraulica, C3: In reparatie
A4: U03, B4: Compresor aer, C4: Functional
A5: U04, B5: Masina de sudura, C5: Functional
A6: U05, B6: Strung, C6: Defect

Scrie formula VLOOKUP care, pentru codul "U04" scris in celula E1, aduce in F1 Starea uneltei. Precizeaza tipul de potrivire folosit si de ce.

Vezi rezolvarea

Date: tabel A1:C6, cod cautat U04 in E1 (Starea se afla in coloana C, a 3-a coloana a tabelului).

Formula in F1: =VLOOKUP(E1;A1:C6;3;0)

Tip de potrivire: exacta (ultimul argument 0/FALS), pentru ca un cod de unealta este un identificator unic - nu are sens o cea-mai-apropiata potrivire; codul trebuie gasit identic sau formula trebuie sa dea eroare #N/A.

Rezultat: pentru U04 (randul A4:C4), coloana 3 (Stare) contine Functional.

Exercitiul 2 (Nivel standard) — Incadrare pe transe cu VLOOKUP aproximativ

Un ocol silvic plateste colectorii de fructe de padure dupa cantitatea adunata, pe transe (tabel cu antet in A1:B1 si datele sortate crescator dupa prag in A1:B5):

A1: Prag_kg, B1: Tarif_lei_per_kg
A2: 0, B2: 3
A3: 20, B3: 4
A4: 50, B4: 5
A5: 100, B5: 6

Un colector a adunat 73 kg. Scrie formula VLOOKUP cu potrivire aproximativa, cu intervalul de cautare A2:B5 (fara randul de antet), care determina tariful per kg aplicabil, precizeaza rezultatul exact si explica de ce tabelul trebuie sa fie sortat crescator pentru ca formula sa functioneze corect.

Vezi rezolvarea

Date: transe in A2:B5 (0->3, 20->4, 50->5, 100->6 lei/kg), cantitate adunata = 73 kg.

Formula: =VLOOKUP(73;A2:B5;2;1) (ultimul argument 1/ADEVARAT = potrivire aproximativa).

Excel cauta in coloana A cea mai mare valoare mai mica sau egala cu 73: aceasta este 50 (randul A4), deci aduce tariful de pe acelasi rand, coloana B: 5 lei/kg.

De ce trebuie sortat crescator: potrivirea aproximativa presupune ca Excel parcurge coloana A in ordine crescatoare si se opreste la ultima valoare care nu depaseste cautarea; daca pragurile nu sunt in ordine, Excel se opreste la o valoare gresita fara sa afiseze nicio eroare - rezultatul pare valid, dar e incorect.

Exercitiul 3 (Nivel performanta) — INDEX+MATCH vs. VLOOKUP pe un tabel care se schimba

Ai tabelul urmator in A1:C5 (A=Specie, B=Diametru_mediu_cm, C=Volum_mc_per_ha):

A1: Specie, B1: Diametru_mediu_cm, C1: Volum_mc_per_ha
A2: Fag, B2: 32, C2: 280
A3: Molid, B3: 28, C3: 340
A4: Stejar, B4: 45, C4: 210
A5: Brad, B5: 30, C5: 300

a) Scrie o formula INDEX+MATCH care, pentru specia "Stejar" cautata intr-o celula E1, aduce Diametrul_mediu_cm.
b) Sefa de ocol iti spune ca peste o saptamana va insera o coloana noua "An_inventariere" intre Specie si Diametru_mediu_cm. Explica, folosind referinte concrete la coloane, ce s-ar intampla cu formula ta INDEX+MATCH dupa inserare fata de ce s-ar fi intamplat cu o formula VLOOKUP scrisa ca =VLOOKUP(E1;A2:C5;2;0). Care formula ramane corecta fara nicio modificare si de ce?

Vezi rezolvarea

Nivel performanta - schita:

  1. a) Structura formulei: =INDEX(interval_rezultat;MATCH(E1;interval_cautare;0)) - MATCH cauta Stejar in coloana Specie (A2:A5) si intoarce POZITIA lui; INDEX foloseste acea pozitie ca sa aduca valoarea de pe acelasi rand din coloana Diametru_mediu_cm (B2:B5). Scrie tu formula completa cu aceste doua intervale.
  2. b) Puncte de comparat: VLOOKUP =VLOOKUP(E1;A2:C5;2;0) foloseste un NUMAR fix de coloana (2); dupa inserarea unei coloane noi intre Specie si Diametru, coloana 2 devine An_inventariere, nu mai e Diametrul - VLOOKUP aduce rezultatul gresit, fara nicio eroare vizibila. INDEX+MATCH foloseste un INTERVAL (B2:B5), nu un numar - Excel actualizeaza automat acest interval cand se insereaza o coloana in mijlocul lui.

Criteriu de evaluare: explicatia trebuie sa numeasca exact DE CE una supravietuieste (referinta la interval, ajustata automat de Excel) si cealalta nu (referinta la un numar fix de pozitie).

Ce ai invatat astazi

  • VLOOKUP(valoare;tabel;nr_coloana;potrivire) cauta pe verticala, in prima coloana a tabelului, si aduce rezultate spre dreapta
  • Potrivire exacta = FALS/0; potrivire aproximativa = ADEVARAT/1, cere tabel sortat crescator (incadrare pe transe)
  • HLOOKUP face acelasi lucru ca VLOOKUP, dar pe orizontala, cautand in primul rand al tabelului
  • MATCH(valoare;interval;tip) NU aduce o valoare — returneaza pozitia (numarul) unde a fost gasita valoarea in interval
  • INDEX(interval;nr_pozitie) aduce valoarea de pe pozitia data, in orice directie
  • INDEX+MATCH combinate cauta in orice directie (inclusiv spre stanga) si nu depind de un numar fix de coloana
  • INDEX+MATCH rezista la inserarea unei coloane noi in tabel; VLOOKUP cu numar fix de coloana se poate strica silentios
  • #N/A frecvent din: format diferit/spatii invizibile intre valorile comparate, sau interval de tabel care aluneca la copiere (se repara cu $ pentru referinte absolute)

Vrei mai mult?

INDEX+MATCH are un succesor mai nou in Excel 365/2021, XLOOKUP — acelasi rezultat, un singur apel de functie.

  • Provocare: la tabelul din Atomul 7 (A=Parametru, B=Valoare_masurata, C=Cod_proba), ai cautat Parametrul dupa "PR-102" cu INDEX+MATCH. Daca ai Excel 365, rescrie aceeasi cautare intr-o singura formula: =XLOOKUP("PR-102";C2:C4;A2:A4). Compara rezultatul cu cel obtinut prin INDEX+MATCH — trebuie sa fie identic (pH).
  • De ce? INDEX+MATCH nu se strica la inserarea unei coloane noi intre A si C, dar VLOOKUP cu nr_coloana=3 scris fix, da. Ce se intampla, mecanic, cu fiecare dintre cele doua formule in momentul in care inserezi o coloana noua?
  • Deschidere: exact acest mecanism — un cod scanat care cauta instant pretul sau descrierea dintr-un catalog cu mii de randuri — sta la baza oricarei case de marcat sau sistem de gestiune de stoc; diferenta e doar ca acolo cautarea ruleaza intr-o baza de date, nu intr-o foaie de calcul.

Urmatoarea lectie

Continua cu Functii pe siruri de caractere, functii financiare si functii definite de utilizator — cum prelucrezi texte, cum calculezi rate si dobanzi, si cum iti construiesti propriile functii cand cele predefinite nu ajung.

Continua →