În Excel, recuperarea cu precizie a anumitor date este adesea o necesitate frecventă. În timp ce funcțiile INDEX și MATCH au fiecare propriile puncte forte, combinarea acestora deblochează un set de instrumente puternic pentru căutarea datelor. Împreună, ele facilitează o gamă largă de capabilități de căutare, de la căutări de bază orizontale și verticale până la funcționalități mai avansate, cum ar fi căutări în două sensuri, cu diferențe de majuscule și mai multe criterii. Oferind capabilități îmbunătățite în comparație cu VLOOKUP, împerecherea dintre INDEX și MATCH permite o gamă mai largă de opțiuni de căutare a datelor. În acest tutorial, să ne aprofundăm în profunzimea posibilităților pe care le pot realiza împreună.

• Funcția INDEX
• Funcția MATCH
• Combinați INDEX și MATCH

• Căutare în două sensuri
• Căutare din stânga
• Căutare sensibilă la majuscule
• Cel mai apropiat meci
• Căutare cu mai multe criterii
• Căutați pe mai multe coloane
• Căutați prima valoare care nu este goală
• Căutați prima valoare numerică
• Căutați asociații MAX sau MIN
• Sfat: personalizați-vă propriile mesaje de eroare #N/A

Cum se utilizează INDEX și MATCH în Excel

Înainte de a utiliza funcțiile INDEX și MATCH, să ne asigurăm că știm cum ne pot ajuta INDEX și MATCH să căutăm mai întâi valorile.

Cum se utilizează funcția INDEX în Excel

INDEX funcția din Excel returnează valoarea la o locație dată într-un interval specific. Sintaxa funcției INDEX este următoarea:

• mulțime (obligatoriu) se referă la intervalul din care doriți să returnați valoarea.
• rând_num (obligatoriu, cu excepția cazului în care coloană_num este prezent) se referă la numărul de rând al matricei.
• coloană_num (opțional, dar obligatoriu dacă rând_num este omis) se referă la numărul coloanei matricei.

De exemplu, să știe scorul lui Jeff, 6student din listă, puteți utiliza funcția INDEX astfel:

=INDEX(C2:C11,6)

√ Notă: intervalul C2: C11 este locul în care sunt enumerate scorurile, în timp ce numărul 6 găsește scorul de examen al 6elevul.

Aici să facem un mic test. Pentru formula =INDEX(A1:C1,2), ce valoare va returna? --- Da, se va întoarce Data nașterii, 2nd valoare pe rândul dat.

Acum ar trebui să știm că funcția INDEX poate funcționa perfect cu domenii orizontale sau verticale. Dar dacă avem nevoie de el pentru a returna o valoare într-un interval mai mare, cu mai multe rânduri și coloane? Ei bine, în acest caz, ar trebui să aplicăm atât un număr de rând, cât și un număr de coloană. De exemplu, pentru a afla Scorul lui Jeff în intervalul tabelului în loc de o singură coloană, îi putem localiza scorul cu a numărul de rând 6 și numărul coloanei 3 în celule prin A2 până la C11 asa:

=INDEX(A2:C11,6,3)

Cu toate acestea, pentru o bază de date foarte mare, cu mai multe rânduri și coloane, cu siguranță nu este convenabil pentru noi să aplicăm formula cu un număr de rând și un număr de coloane exacte. Și asta atunci când ar trebui să combinăm utilizarea funcției MATCH.

Uimitor! Utilizarea filelor eficiente în Excel, cum ar fi Chrome, Edge, Firefox și Safari!
Economisiți 50% din timp și reduceți mii de clicuri de mouse pentru dvs. în fiecare zi!

Cum se utilizează funcția MATCH în Excel

Funcția MATCH din Excel returnează o valoare numerică, locația unui anumit element în intervalul dat. Sintaxa funcției MATCH este următoarea:

• lookup_value (obligatoriu) se referă la valoarea care trebuie să se potrivească în matrice_căutare.
• matrice_căutare (obligatoriu) se referă la intervalul de celule în care doriți să caute MATCH.
• tip_potrivire (opțional): 1, 0 or -1.
  • 1 (implicit), MATCH va găsi cea mai mare valoare mai mică sau egală cu lookup_value. Valorile din matrice_căutare trebuie plasate în ordine crescătoare.
  • 0, MATCH va găsi prima valoare care este exact egală cu lookup_value. Valorile din matrice_căutare poate fi în orice ordine. (Pentru cazurile în care tipul de potrivire este setat la 0, puteți utiliza caractere joker.)
  • -1, MATCH va găsi cea mai mică valoare care este mai mare sau egală cu lookup_value. Valorile din matrice_căutare trebuie plasate în ordine descrescătoare.

De exemplu, să știe poziţia Verei în lista de nume, puteți folosi formula MATCH astfel:

=MATCH("Vera",A2:A11,0)

√ Notă: Rezultatul „4” indică faptul că numele „Vera” se află pe a 4-a poziție a listei.

Acum, că știm despre utilizările de bază ale funcțiilor INDEX și MATCH în Excel, să ne răsucim mânecile și să ne pregătim să combinăm cele două funcții.

Cum să combinați INDEX și MATCH în Excel

Vă rugăm să consultați exemplul de mai jos pentru a afla cum putem combina funcțiile INDEX și MATCH:

A găsi Scorul lui Evelyn, știind că punctajele la examene sunt în 3a doua coloană, putem utilizați funcția MATCH pentru a determina automat poziția rândului fără a fi nevoie să-l numărați manual. Ulterior, putem folosi funcția INDEX pentru a prelua valoare la intersecția rândului identificat și a coloanei a 3-a:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Ei bine, deoarece formula poate părea puțin complicată, să trecem prin fiecare parte a ei.

INDEX formula conține trei argumente:

• rând_num: MATCH(„Evelyn”, A2:A11,0) furnizează INDEX poziția de rând a valorii "Evelyn" în intervalul A2: A11, Care este 5.
• coloană_num: 3 specifică 3a treia coloană pentru INDEX pentru a localiza scorul în cadrul matricei.
• mulțime: A2: C11 solicită INDEX să returneze valoarea potrivită la intersecția rândului și coloanei specificate, în intervalul de la A2 până la C11. În sfârșit, obținem rezultatul 90.

În formula de mai sus, am folosit o valoare codificată, "Evelyn". Cu toate acestea, în practică, valorile codificate nu sunt practice, deoarece ar necesita modificări de fiecare dată când căutăm date diferite, cum ar fi scorul pentru alt student. În astfel de scenarii, putem folosi referințe de celule pentru a crea formule dinamice. De exemplu, în acest caz, o voi face schimbați „Evelyn” în F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Exemple de formule INDEX și MATCH

În această parte, vom vorbi despre circumstanțe diferite pentru a utiliza funcțiile INDEX și MATCH pentru a satisface diferite nevoi.

INDEX și MATCH pentru a aplica o căutare bidirecțională

În exemplul anterior, știam numărul coloanei și am folosit o formulă MATCH pentru a găsi numărul rândului. Dar dacă nu suntem siguri și de numărul coloanei?

În astfel de cazuri, putem efectua o căutare bidirecțională, cunoscută și sub numele de căutare de matrice, folosind două funcții MATCH: una pentru a găsi numărul rândului și cealaltă pentru a determina numărul coloanei. De exemplu, să știe Scorul lui Evelyn, ar trebui să folosim formula:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX și MATCH pentru a aplica o căutare la stânga

Acum, să luăm în considerare un scenariu în care trebuie să determinați clasa lui Evelyn. Poate ați observat că coloana de clasă este poziționată în stânga coloanei de nume, situație care depășește capacitățile unei alte funcții puternice de căutare Excel, CĂUTARE V.

De fapt, abilitatea de a efectua căutări din partea stângă este unul dintre aspectele în care combinația dintre INDEX și MATCH eclipsează VLOOKUP.

A găsi clasa lui Evelyn, folosiți următoarea formulă pentru caută-o pe Evelyn în B2:B11 și preluați valoarea corespunzătoare din A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX și MATCH pentru a aplica o căutare sensibilă la majuscule

Funcțiile MATCH nu sunt în mod inerent sensibile la majuscule. Cu toate acestea, atunci când aveți nevoie de formula pentru a face diferența între caracterele majuscule și minuscule, o puteți îmbunătăți prin încorporarea CORECT funcţie. Combinând funcția MATCH cu EXACT într-o formulă INDEX, puteți efectua în mod eficient o căutare sensibilă la majuscule, după cum se arată mai jos:

• mulțime se referă la intervalul din care doriți să returnați valoarea.
• lookup_value se referă la valoarea de potrivire, luând în considerare cazul caracterelor, în matrice_căutare.
• matrice_căutare se referă la intervalul de celule cu care doriți să comparați MATCH lookup_value.

De exemplu, să știe scorul la examen al lui JIMMY, utilizați următoarea formulă:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Notă: Aceasta este o formulă matrice care necesită să introduceți cu Ctrl + Schimba + Intrați, cu excepția Excel 365 și Excel 2021.

INDEX și MATCH pentru a găsi cea mai apropiată potrivire

În Excel, este posibil să întâlniți situații în care trebuie să găsiți cea mai apropiată sau cea mai apropiată potrivire de o anumită valoare dintr-un set de date. În astfel de scenarii, utilizarea unei combinații de funcții INDEX și MATCH, împreună cu funcțiile ABS și MIN, poate fi incredibil de utilă.

• mulțime se referă la intervalul din care doriți să returnați valoarea.
• matrice_căutare se referă la intervalul de valori în care doriți să găsiți cea mai apropiată potrivire lookup_value.
• lookup_value se referă la valoarea pentru a găsi cea mai apropiată potrivire.

De exemplu, pentru a afla al cărui scor este cel mai apropiat de 85, utilizați următoarea formulă pentru a caută cel mai apropiat scor de 85 în C2:C11 și preluați valoarea corespunzătoare din A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Notă: Aceasta este o formulă matrice care necesită să introduceți cu Ctrl + Schimba + Intrați, cu excepția Excel 365 și Excel 2021.

Uimitor! Utilizarea filelor eficiente în Excel, cum ar fi Chrome, Edge, Firefox și Safari!
Economisiți 50% din timp și reduceți mii de clicuri de mouse pentru dvs. în fiecare zi!

INDEX și MATCH pentru a aplica o căutare cu mai multe criterii

Pentru a găsi o valoare care îndeplinește mai multe condiții, care necesită să căutați în două sau mai multe coloane, utilizați următoarea formulă. Formula vă permite să efectuați o căutare cu mai multe criterii prin specificarea diferitelor condiții în diferite coloane, ajutându-vă să găsiți valoarea dorită care îndeplinește toate criteriile specificate.

√ Notă: Aceasta este o formulă matrice care necesită să introduceți cu Ctrl + Schimba + Intrați. O pereche de paranteze cretate va apărea apoi în bara de formule.

• mulțime se referă la intervalul din care doriți să returnați valoarea.
• (lookup_value=lookup_array) reprezintă o singură condiție. Această condiție verifică dacă un anume lookup_value se potrivește cu valorile din matrice_căutare.

De exemplu, pentru a găsi scorul clasei A Coco, a cărui dată de naștere este 7/2/2008, puteți folosi următoarea formulă:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX și MATCH pentru a aplica o căutare pe mai multe coloane

Imaginați-vă un scenariu în care aveți de-a face cu mai multe coloane de date. Prima coloană acționează ca o cheie pentru a clasifica datele în celelalte coloane. Pentru a determina categoria sau clasificarea pentru o anumită intrare, va trebui să efectuați o căutare în coloanele de date și să o asociați cu cheia relevantă din coloana de referință.

De exemplu, în tabelul de mai jos, cum putem potrivi elevul Shawn cu clasa sa corespunzătoare folosind INDEX și MATCH? Ei bine, o puteți obține cu o formulă, dar formula este destul de extinsă și poate fi dificil de înțeles, darămite să vă amintiți și să tastați.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Acolo Kutools pentru Excel's Indexați și potriviți pe mai multe coloane caracteristica este utilă. Simplifică procesul, făcându-se rapidă și ușoară potrivirea unor intrări specifice cu categoriile corespunzătoare. Pentru a debloca acest instrument puternic și pentru a-l potrivi fără efort pe Shawn cu clasa sa, pur și simplu descărcați și instalați suplimentul Kutools pentru Excel, apoi procedați după cum urmează:

1. Selectați celula de destinație în care doriți să afișați clasa potrivită.
2. Pe Kutools fila, faceți clic pe Formula Helper > Căutare și referințe > Indexați și potriviți pe mai multe coloane.
3. 
3. În caseta de dialog pop-up, procedați după cum urmează:
   1. Faceți clic pe primul butonul de lângă Căutați_col pentru a selecta coloana care conține informațiile cheie pe care doriți să le returnați, adică numele claselor. (Puteți selecta doar o singură coloană aici.)
   2. Faceți clic pe al doilea butonul de lângă Table_rng pentru a selecta celulele pentru a se potrivi cu valorile din cele selectate Căutați_col, adică numele elevilor.
   3. Faceți clic pe al treilea butonul de lângă Căutare_valor pentru a selecta celula care conține numele elevului pe care doriți să o potriviți cu clasa sa, în acest caz, Shawn.
   4. Clic OK.
   5. 

Rezultat

Kutools a generat automat formula și veți vedea imediat numele clasei lui Shawn afișat în celula de destinație.

INDEX și MATCH pentru a căuta prima valoare care nu este goală

Pentru a prelua prima valoare care nu este goală, ignorând erorile, dintr-o coloană sau un rând, puteți utiliza o formulă bazată pe funcțiile INDEX și MATCH. Cu toate acestea, dacă nu doriți să ignorați erorile din intervalul dvs., adăugați funcția ISBLANK.

• Obțineți prima valoare care nu este goală dintr-o coloană sau un rând ignorând erorile:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Obțineți prima valoare care nu este goală dintr-o coloană sau un rând, inclusiv erori:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX și MATCH pentru a căuta prima valoare numerică

Pentru a prelua prima valoare numerică dintr-o coloană sau un rând, utilizați formula bazată pe funcțiile INDEX, MATCH și ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX și MATCH pentru a căuta asociații MAX sau MIN

Dacă trebuie să preluați o valoare asociată cu valoarea maximă sau minimă dintr-un interval, puteți utiliza funcția MAX sau MIN împreună cu funcțiile INDEX și MATCH.

• INDEX și MATCH pentru a prelua o valoare asociată cu valoarea maximă:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX și MATCH pentru a prelua o valoare asociată cu valoarea minimă:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• Există două argumente în formulele de mai sus:
• • mulțime se referă la intervalul din care doriți să returnați informațiile aferente.
  • matrice_căutare reprezintă setul de valori care trebuie examinate sau căutate după criterii specifice, adică valori maxime sau minime.

De exemplu, dacă doriți să determinați care are cel mai mare punctaj, folosiți următoarea formulă:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Sfat: personalizați-vă propriile mesaje de eroare #N/A

Când lucrați cu funcțiile INDEX și MATCH ale Excel, este posibil să întâmpinați eroarea #N/A când nu există niciun rezultat care să corespundă. De exemplu, în tabelul de mai jos, atunci când încercați să găsiți scorul unei eleve pe nume Samantha, apare o eroare #N/A, deoarece nu este prezentă în setul de date.

Pentru a face foile de calcul mai ușor de utilizat, puteți personaliza acest mesaj de eroare încadrând formula INDEX MATCH în funcția IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Mai sus este tot conținutul relevant legat de funcțiile INDEX și MATCH din Excel. Sper că veți găsi tutorialul de ajutor. Dacă doriți să explorați mai multe sfaturi și trucuri Excel, rugăm să faceți clic aici pentru a accesa colecția noastră extinsă de peste mii de tutoriale.