Functii: INDEX, MATCH


In acest articol vom vorbi despre formulele INDEX si MATCH, formule de cautare foarte utile care vin in ajutorul nostru atunci cand alte formule (nu dau nume ca sa nu se supere VLOOKUP) isi ating limitele. Haideti sa nu vorbim teoretic, sa luam un exemplu.

Exemplu

Vom lua exemplul de mai jos, stiu, e foarte complicat, greu de inteles, va explic acum. Vedem acolo o casuta libera la vanzari, am vrea acolo, in functie de numarul lunii scris putin mai jos, sa ne apara valoarea de vanzari.

Formule index 1Functia INDEX

Pentru asta vom folosi functia INDEX, hai sa vedem mai exact cum arata sintaxa.



=INDEX(array, row_num, [column_num])

  • Array: zona de celule unde facem cautarea
  • Row_num: numarul randului pe care vrem sa il returnam
  • Column_num: numarul coloanei pe care o returnam (optional in cazurile in care zona de celule are o singura coloana)

Revenind la exemplu

In functie de valoarea introdusa in E1, vrem sa returnam valoarea de vanzari corespunzatoare. Daca ati citit descrierea de mai sus probabil ca sunteti de adcord cu mine ca atunci cand scriem formula INDEX:

  • Array: sunt valorile de la A2 la A7, adica numerele de vanzari
  • Row_num: va fi egal cu ceea ce scriem in E1
  • Column_num: nu ne intereseaza in acest caz, zona de celule selectata de noi e pe o singura coloana

Formula noastra va fi urmatoarea:

=INDEX(A2:A7,E1)

Formule index 2Hai sa complicam putin lucrurile

Acum ca ati inteles ideea de baza dupa care functioneaza INDEX, hai sa vedem un exemplu putin mai complex. Firma noastra s-a extins si acum vinde in 3 zone: Oradea, Cluj si Bucuresti. Acum raportul arata asa:

Formule index 3

Inainte sa dai scroll mai jos, gandeste-te tu cum ai proceda.

Am vrea ca in functie de zona si luna pe care o scriem, sa ne arate vanzarile.

Functia MATCH

Probabil ca v-ati dat seama ca functia INDEX nu va functiona de una singura daca ii vom da ca si argumente Oradea si Feb. Functia asta are nevoie de cifre. Am avea nevoie de inca o functie care sa caute zona si luna si sa ne returneze un numar. Exista, se numeste MATCH si uite cum arata:

=MATCH(lookup_value, lookup_array,[match_type])

  • lookup_value: valoarea pe care o cautam
  • lookup_array: zona de celule in care cautam
  • match_type (optional): tip de cautare, mai mare, mai mic, egal

Pentru a vedea a cata valoare este G1 din A1:C2 avem:

=MATCH(G1,A1:C1,0)

Pentru a vedea a cata valoare este G2 din D2:D7 avem:

=MATCH(G2,D2:D7,0)

INDEX+MATCH

Aflam valoarea vanzarilor in functie de zona si luna vom folosi INDEX si MATCH in combinatie. Trec direct la subiect, ecce formula:

=INDEX(A2:C7,MATCH(G2,D2:D7,0),MATCH(G1,A1:C1,0))

Formule index 4

Descarca fisier

Aici poti descarca fisierul pe care am lucrat si eu.

Legatura cu VLOOKUP

Unii dintre voi probabil ca v-ati lovit de acest lucru, formula VLOOKUP nu stie sa caute decat in dreapta in cadrul table array, asta ne poate pune probleme in anumite cazuri. Din fericire, folosind INDEX si MATCH putem rezolva aceasta problema, cu aceste formule putem face un fel de cautare VLOOKUP in stanga.

Randul tau

Spune-mi in sectiunea de comentarii de mai jos in ce alte situatii am mai putea folosi formulele INDEX si MATCH.

Vrei sa afli mai multe despre functii in Excel?

Am un eBook pentru tine, afla mai multe aici.



  • Azi mi-am stors mintea să găsească soluții și am ”intors internetul pe dos”. Aveam nevoie de o formulă care să verifice dacă, pe spatiul H16:H19, exista vreo valoare și dacă o găsește să o aducă in celula formulei sau in oricare alta.

    Singura varinta gasita a fost =INDEX(H16:H19;MATCH(TRUE;H16:H19″”;0))

    Stii vreo varianta mai ”scurtă”? Menționez că în spațiul H16:H19 sunt conditionale care lasă goală celula dacă rezultatul nu este pozitiv și că o singură celulă din cele 4 poate avea valoare.

    Merci și succes în continuare!

    • Salut

      In momentul de fata nu imi vine in minte alta solutie. Mai incearca si altcineva?

      • Marius C

        =IF(ISNA(VLOOKUP($B2;’X:erp[produse.xlsx]prod’!$A$2:$E$1302;2;FALSE));””;VLOOKUP($B2;’X:erp[produse.xlsx]prod’!$A$2:$E$1302;2;FALSE))

  • Ciprian

    Salut! Am un fisier in care exista urmatoarele coloane: cod/denumire/culoare/pret. Doresc ca intr-un alt 2-lea fisier sa adaug doar codul din primul fisier si randul sa se completeze automat cu informatiile din primul fisier. Este posibil acest lucru cu functiile prezentate? Cum s-ar putea face? Multumesc pentru articol!

    • Se poate cu index si match, poate totusi e mai simplu cu VLOOKUP, vezi acest video: https://www.youtube.com/watch?v=GJj1bP0IjSs

    • Marius C

      =IF(ISNA(VLOOKUP($B2;’X:erp[produse.xlsx]prod’!$A$2:$E$1302;2;FALSE));””;VLOOKUP($B2;’X:erp[produse.xlsx]prod’!$A$2:$E$1302;2;FALSE))

  • Ana

    Buna! am un fisier in care am functiile INDEX si MATCH si nu inteleg sintaxa (chiar daca am studiat materialul de mai sus). sintaxa arata cam asa: =INDEX(‘list price’!$C$2:$C$961;MATCH(1;(A5=’list price’!$A$2:$A$961)*(D5=’list price’!$B$2:$B$961);0)). Ar fi mai util sa va trimit si excelul? Multumesc.

    • Ana, scrie-mi pe pagina de contact. Continuam discutia acolo.
      PS: scuze de delay, dar de cateva zile site-ul nu ma mai anunta de comentariile noi … nu stiu de ce.

  • Maria Hagiu

    Mi s-a spus cum ca functia Index traduce din romana in engleza.Asa e? Pentru ca nu stiu cum sa o folosesc ca sa-mi genereze rezultatul..

    • E posibil sa folosim index si match si pentru traduceri, dar functiile astea sunt mai degraba functii de cautare.

  • Balint Ciprian

    Salut Radu. Considerand ca in coloana luna trecem de 2 ori o luna calendaristica putem adauga la formula propunsa sumif (chiar sumifs) astfel incat sa aduna cele doua valori pentru o luna folosind si index/match? Cum ar arata formula? Multumesc.

    • Index si Match nu prea poate face adunare. In schimb e chiar mai usor sa folosim doar SUMIF / SUMIFS pentru asta.
      https://excelninja.ro/functia-sumif-sumifs-excel/

      • Balint Ciprian

        Multumesc. Intr-adevar, functioneaza si cu SUMIF. Eu as fi vrut sa folosesc adunarea in functie de o perioada calendaristica astfel incat intr-o perioada anume sa adune anumite sume din coloana cu lunile, in perioada a doua sa aduna alte sume afisate la lunile respective.

  • alina agavriloaie

    Buna Radu. Nu cred ca la acest text trebuie lasat commentul dar am mare nevoie de ajutor pentur ca nu reusesc sa o scot la capat. In coloana A am 2 produse grupate pe cate 5 celule iar in coloana B, in dreptul fiecaruia am cate un produs. Ma intereseaza ca in coloana C sa aduc intr-o singura celula toate caracteristicile in functie de numarul produsului din coloana A. In mod normal folosesc functia Transpose, dar cand am 200 de produse si fiecare cu cate 3 caracteristici, cum fac?

    • Alina, scuze, dar am vazut abia acum comentariul tau. Mai e de actualitate intrebarea?

      • alina agavriloaie

        Buna Radu. Pentru mine nu dar poate ajuta pentru alti cititori. solutia identificata a fost sa folosesc functia If : IF(celulacelula,celula,” “). Multumesc mult pt raspuns. sincer, nu ma asteptam;)

        • De obicei sunt mai prompt, dar am niste probleme cu site-ul in ultima vreme, nu ma anunta cand am comentarii noi cum ar trebui