1. Functia INDEX: gaseste valoarea pe baza numarului liniei/coloanei

Functia INDEX este una dintre cele mai usor de inteles functii din Excel, insa retineti ca ea functioneaza in tandem cu functia MATCH pentru a realiza o cautare completa intr-un tabel Excel.

Dorim sa ajungem cumva la valoarea 11608 din tabelul de mai jos. Daca am sti numarul liniei (4) si al coloanei (3), folosim functia INDEX pentru a ni se returna valoarea dorita:

=INDEX(A2:E10,4,3)

Mai jos aveti sintaxa functiei INDEX:

= INDEX (tabel, numar_rand, [numar_coloana])

tabel – tabelul cu celule unde cautam

numar_rand – numarul de rand din care trebuie obtinuta valoarea.

[numar_coloana] – numarul coloanei din care trebuie obtinuta valoarea.

Va rog sa fiti de acord cu mine ca functia INDEX este extrem de usor de inteles, atat timp cat avem un numar de linie si unul de coloana, putem ajunge usor la valoarea cautata dintr-un tabel (valoare aflata la intersectia liniei cu a coloanei)

Insa in practica NU avem acel numar de linie/coloana… Si aici intervine functia MATCH:

2. Functia MATCH: gaseste numarul liniei/coloana pe baza valorii de cautare

Daca functia INDEX este un aparat GPS care iti arata locatia, functia MATCH este satelitul care furnizeaza coordonatele aparatului GPS!

Functia MATCH este cea care arata linia/coloana de unde ne intereseaza valoarea cautata.

In tabelul de mai jos cautam cu functia MATCH orasul Bucuresti in setul de celule indicat cu rosu:

=MATCH(„Bucuresti”, A2:A10,0)

Retineti ca functia MATCH in cazul de mai sus returneaza numarul liniei unde se afla orasul Bucuresti (raportat la setul de celule A2:A10).

Functia MATCH incepe sa caute de sus in jos valoarea de cautare (care este „Bucuresti”) in intervalul specificat (care este A2:A10 in acest caz).

Imediat ce gaseste numele, returneaza pozitia raportata la intervalul respectiv.

Mai jos este sintaxa functiei MATCH din Excel.

= MATCH (valoare de cautare, arie_cautare, [tipul cautarii])

valoare de cautare – este valoarea cautata in setul de celule de la argumentul 2

arie_cautare – setul de celule in care cautati valoarea de la argumentul 1

[tipul cautarii] – (Optional) Aceasta specifica modul in care Excel trebuie sa caute o valoare potrivita. Poate lua trei valori -1, 0 sau 1.

Mai jos este o explicatie a modului in care functioneaza aceste trei valori de la argumentul 3:

0 – aceasta va cauta o potrivire exacta a valorii. Daca se gaseste o potrivire exacta, functia MATCH va intoarce pozitia celulei. In rest, va intoarce o eroare. Este echivalentul valorii FALSE de la argumentul numarul 4 de la VLOOKUP

1 – aceasta gaseste cea mai mare valoare care este mai mica sau egala cu valoarea de cautare. Pentru ca acest lucru sa functioneze, intervalul dvs. de date trebuie sortat in ordine crescatoare. Este echivalentul valorii TRUE de la argumentul numarul 4 de la VLOOKUP

-1 – aceasta gaseste cea mai mica valoare care este mai mare sau egala cu valoarea de cautare. Pentru ca acest lucru sa functioneze, intervalul dvs. de date trebuie sortat in ordine descrescatoare. NU are corespondent in VLOOKUP

3. Sa le combinam pentru a face o cautare completa (INDEX + MATCH)

Acum ca aveti o idee de baza a modului in care functioneaza individual functiile INDEX si MATCH, sa le combinam pe acestea si sa aflam despre toate lucrurile minunate pe care le poate face IMPREUNA.

Pentru a intelege mai bine acest lucru, am cateva exemple care folosesc combinatia INDEX+MATCH.

Voi incepe cu un exemplu simplu si va voi arata si cateva cazuri de utilizare avansate.

Exemplul 1: O simpla cautare folosind INDEX/MATCH

=INDEX(A2:E10, MATCH(„Bucuresti”, A2:A10, 0), 3)

Acum, daca credeti ca acest lucru se putea face mai usor folosind functia VLOOKUP, aveti dreptate!

Exemplul de mai sus nu reprezinta cea mai buna utilizare a combinatiei INDEX+MATCH.

Chiar daca sunt un mare fan al combinatiei INDEX/MATCH, trebuie sa recunosc ca folosirea acestor doua functii este mai dificil de facut decat VLOOKUP.

Obs 1: Combinatia INDEX/MATCH stie sa caute si pe orizontala si pe verticala:

Va rog sa nu renuntati la parcurgerea acest document pana la capat si va promit ca merita!

Exemplul 2: Cautati spre stanga

Ce se intampla daca ne intereseaza o valoare aflata la stanga coloanei unde facem cautarea?

Cu functia VLOOKUP aveam reale probleme in acest scenariu.

=INDEX(A2:E10, MATCH(„Bucuresti”,E2:E10, 0), 2)

Obs. 2: Combinatia de functii INDEX/MATCH stie sa returneze si valorile aflate pe coloanele din stanga coloanei unde se cauta

Exemplul 3: Cautare in doua directii (matrix lookup)

Dorim sa cautam valoarea de la un anumit Oras de la o anumita ora (urmariti animatia pentru a vedea cum a fost facut panoul de control):

Scriem formula in celula H4:

=INDEX(A2:E10, MATCH(G4,A2:A10, 0), MATCH(H3,A1:E1,0))

Permiteti-mi sa explic rapid formula de mai sus.

Formula INDEX foloseste A2:E10 ca interval. Toate celelalte pozitii si valori sunt raportate la A2:E10

Primul MATCH cauta Brasov (din celula G4) si preia pozitia acestuia din coloana cu Filialele (A2: A10). Acesta devine numarul de rand din care trebuie obtinute datele. In cazul nostru linia cu numarul 3

A doua formula MATCH foloseste Ora 11 (din celula H3) pentru a obtine pozitia numarul de coloana specific in A1:E1. In cazul nostru Ora 11 se afla pe coloana 4

Deoarece aceste pozitii MATCH sunt introduse in functia INDEX, acesta returneaza scorul in functie de numele studentului si numele subiectului.

=INDEX(A2:E10, 3, 4)

 returneaza 14157

Obs 3.: Formulele Index/Match sunt dinamice, nu conteaza daca schimbati intre ele coloanele, daca inserati sau stergeti din coloane etc.

Exemplul 4: Cautarea pe mai multe coloane / criterii

In tabelul de mai jos exista mai multi oameni cu acelasi nume sau acelasi prenume, insa noi dorim sa cautam si dupa nume si dupa prenume in acelasi timp.

Cu functia VLOOKUP trebuia sa facem o coloana ajutatoare (formata din concatenarea celor doua folosite la cautare).

{=INDEX($C$2:$C$6,MATCH(„RadulescuIon”,$A$2:A6 & $B$2:$B$6,0),1)}

Atentie! Formula de mai sus este una bazata pe vectori, la final tastati CTRL + SHIFT + ENTER (nu un simplu ENTER si nici nu scrieti manual acoladele pentru ca nu functioneaza)

Explic formula de mai sus:

$A$2:A6 & $B$2:$B$6 – reprezinta un vector format din concatenarile coloanelor Nume si Prenume, arata astfel: {„RadulescuRadu”; „PopescuAna”; „RadulescuIon”; „VasilescuMirela”; „AntonescuIon”}

MATCH(„RadulescuIon”,$A$2:A6 & $B$2:$B$6,0) – se cauta exact textul concatenat in vectorul de mai sus, iar functia Match returneaza pozitia unde a gasit ” RadulescuIon „ (in cazul nostru nr 3)

INDEX($C$2:$C$6,3,1) – de pe coloana Salariul se returneaza valoarea aflata pe randul 3 (adica 4500)

Obs.4: Puteti face acest tip de cautare cu mai multe criterii si cu VLOOKUP, dar trebuie sa utilizati o coloana ajutatoare. In acest caz combinatia INDEX+MATCH este mai comod de folosit pentru ca nu presupune existenta unei coloane de ajutor.

Exemplul 5: Calcule pe un rand/coloana intreaga

In tabelul de mai jos dorim sa adunam toate valorile corespondente Orasului Timisoara:

=SUM(INDEX(B2:E10, MATCH(„Timisoara”, A2:A10,0),0))

Va vine sa credeti sau nu, daca punem 0 la numarul coloanei pentru functia Index (ultimul argument), atunci aceasta returneaza un vector de valori pentru tot randul gasit, in cazul nostru: {9011,10443,12302,6578}. Apoi functia SUM aduna aduna toate elementele vectorului😊

Formula de mai sus NU este una bazata pe vectori! Cu alte cuvinte nu apasati la final CTRL + SHIFT + Enter (ci numai un Enter normal)

Exemplul 6: Gasiti gradul (potrivire aproximativa)

In tabelul de mai jos se doreste afisarea calificativului in functie de Total (grila cu calificativele se afla in tabelul din dreapta):

=INDEX($E$4:$F$7, MATCH(A2, $E$4:$E$7, 1), 2)

In acest caz functia MATCH face o cautare aproximativa, adica cauta cea mai apropiata valoare MAI MICA decat CEA cautata (argumentul al treilea este setat pe valoarea 1)

Exemplul de mai sus se putea face si cu VLOOKUP (punand valoarea TRUE la ultimul argument):

=VLOOKUP(A2, $E$4:$F$7, 2, TRUE)

Retineti ca tabelul unde se cauta (in cazul nostru cel din dreapta) TREBUIE SA FIE SORTAT ASCENDENT DUPA COLOANA UNDE SE CAUTA (Coloana Total din tabelul din dreapta)

Sa ducem mai departe exemplul de mai sus. In plus fata de calificativele stabilite, presupunem ca avem si o valoare minimala (tip target) de indeplinit. Sa presupunem ca aceasta valoare este: 34999

Intrebare: Care este Filiala „unde s-a tras linia”, cu alte cuvinte ne intereseaza numele Filialei ULTIMA care se incadreaza in TARGET (adica cea cu totalul cel mai mic dintre cele mai mari decat targetul fixat)

=INDEX(A2:B10, MATCH(D2, A2:A10, -1), 2)

Trebuie sortata coloana unde se face cautarea DESCENDENT (coloana TOTAL din tabelul din stanga)

Argumentul al treilea al functiei Match in acest caz trebuie sa fie -1

Atentie! Vlookup nu are posibilitatea sa ne intoarca valoarea aproximativa MAI MARE DECAT CEA CAUTATA (doar pe cea mai mica, asa cum am aratat in exemplul de dinainte).

Exemplul 7: Cautari sensibile la tipul literelor

Pana in prezent, toate cautarile pe care le-am facut au fost insensibile la tipul literelor (majuscule/minuscule).

Aceasta inseamna ca indiferent daca valoarea cautarii a fost Bucuresti sau BuCUREsti, nu a contat. Veti obtine acelasi rezultat.

Dar daca va doriti o cautare sensibila la majuscule si/sau minuscule?

De exemplu: in tabelul de mai jos dorim sa gasim Vanzarile lui ION (cel scris cu litere mari, nu primul scris cu litere mici):

{=INDEX(A2:B6,MATCH(TRUE,EXACT(D2,A2:A6),0),2)}

Atentie! Formula de mai sus este una bazata pe vectori, la final tastati CTRL + SHIFT + ENTER (nu un simplu ENTER si nici nu scrieti manual acoladele pentru ca nu functioneaza)

Permiteti-mi sa va explic cum functioneaza aceasta formula.

Functia EXACT verifica o potrivire exacta a valorii de cautare (care este in acest caz „ION” – scris cu litere mari).  Ea parcurge toate numele si returneaza FALSE daca nu este o potrivire si TRUE daca este o potrivire.

In cazul nostru rezultatul functiei EXACT din acest exemplu este – {FALSE;FALSE;FALSE;TRUE;FALSE}

Retineti ca exista un singur TRUE, care apare atunci cand functia EXACT a gasit o potrivire perfecta.

Functia MATCH gaseste apoi pozitia TRUE in tabloul returnat de functia EXACT, care este al 4-lea in acest caz.

Odata ce avem pozitia, functia INDEX o foloseste pentru a gasi Vanzarile (de pe coloana a 2-a).

Exemplul 8: Cautari INDEX MATCH cu metacaractere (wildcards)

Daca doriti sa cautati o valoare atunci cand exista o potrivire partiala, atunci trebuie sa utilizati metacaractere (sau wildcards).

De exemplu, mai jos este prezentat un set de date cu numele companiei si totalul lor de vanzari:

Deoarece cautarea nu se face dupa potriviri exacte, nu puteti face o cautare obisnuita in acest caz.

Dar puteti obtine in continuare datele corecte folosind un asterisc (*), care aici este pe post de „Joker” (la cartile de joc, inlocuieste celelalte carti)

=INDEX(A2:B6, MATCH(„*” & D2 & „*”, A2:A6,0), 2)

Permiteti-mi sa va explic cum functioneaza aceasta formula.

Deoarece nu exista o potrivire exacta a valorilor de cautare, am folosit D2 si ”*” ca valoare de cautare in functia MATCH.

Un asterisc este un caracter special care reprezinta orice alt caracter sau succesiune de caractere (si numere). Aceasta inseamna ca „*Microsoft*” inseamna tot ceea ce contine Microsoft.

Daca scriam:

– „Microsoft*”  – ne gasea prima valoare care INCEPE cu cuvantul Microsoft

– „*Microsoft”  – ne gasea prima valoare care se TERMINA cu cuvantul Microsoft

4. De ce este mai buna combinatia INDEX / MATCH decat VLOOKUP?

Sau este?

Da, este – in majoritatea cazurilor!

Dar, inainte sa va demonstrez asta, permiteti-mi sa spun ca – VLOOKUP este o functie extrem de utila si imi place.

Vlookup poate face multe lucruri in Excel si il folosesc din cand in cand. Asadar, daca doriti sa faceti unele cautari de baza, va recomand sa folositi VLOOKUP.

Insa dupa ce inveti INDEX / MATCH, este posibil sa nu te mai intorci niciodata la VLOOKUP.

Iata motivele mele ce stau la baza afirmatiei ca „tandemul” INDEX / MATCH este mai bun decat VLOOKUP:

INDEX / MATCH poate privi spre stanga (precum si spre dreapta) valoarea cautarii

Am aratat intr-un exemplu mai sus.

Daca aveti o valoare situata in stanga valorii de cautare, nu o puteti gasi cu VLOOKUP. Ba puteti, insa cu o formula cat China!

INDEX / MATCH, pe de alta parte, sunt facute pentru cautari in orice directie (sus/ jos sau stanga/dreapta)

INDEX / MATCH poate cauta si pe verticala si pe orizontala

La urma urmei, V in VLOOKUP este vertical. Ai nevoie de HLOOKUP daca vrei cautare si pe orizontala.

VLOOKUP poate cauta doar pe verticala, in timp ce INDEX / MATCH poate parcurge datele atat pe verticala cat si pe orizontala.

VLOOKUP nu poate functiona cu date descendente aproximative

Cand vine vorba de cautari aproximativ, VLOOKUP si INDEX / MATCH sunt la acelasi nivel cu observatia ca VLOOKUP poate returna doar valoarea mai mica decat cea cautata (nu si pe cea mai mare)

INDEX / MATCH poate fi mai rapid

Diferenta de viteza in VLOOKUP si INDEX / MATCH este greu de observat atunci cand aveti seturi de date mici. Dar daca aveti mii sau sute de mii de randuri si foarte multe coloane, acesta poate fi un factor decisiv.

INDEX / MATCH sunt independente de pozitiile actuale ale coloanelor

Daca aveti un set de date si cautati cu VLOOKUP, stiti ca argumentul al treilea reprezinta numarul coloanei de pe care se doreste rezultatul.

Dar daca sterg una dintre coloanele de dinaintea coloanei de unde doresc rezultatul? In acest caz, formula VLOOKUP va returna valoarea de pe alta coloana.

Folosind INDEX / MATCH, in acest caz, este mult mai indicat, deoarece puteti sa aflati dinamic numarul coloanei cu functia MATCH cu cautarea pe orizontala (am aratat in exemplele de mai sus)

Cu siguranta, puteti face asta si combinand VLOOKUP cu MATCH, dar daca deja combinati de ce sa nu o faceti direct cu INDEX? 😊

Cand utilizati INDEX / MATCH, puteti introduce / sterge coloane in siguranta in setul dvs. de date.

In ciuda tuturor acestor factori, exista un motiv pentru care VLOOKUP este atat de popular.

Si este un mare motiv.

VLOOKUP este mai usor de utilizat

VLOOKUP are doar maxim patru argumente.

INDEX / MATCH, pe de alta parte, este putin mai dificil de folosit.

Asadar, daca sunteti nou in lumea cautarilor Excel si nu stiti cum sa folositi functia VLOOKUP, mai bine familiarizati-va cu ea inainte de a incepe sa folositi INDEX/MATCH.

Sper ca ati gasit util acest articol.

Pe saptamana viitoare!