În acest articol, vom învăța cum să utilizați funcția INDEX și MATCH pentru a căuta valoarea în Excel.
Funcția INDEX & MATCH pentru a înlocui funcția vlookup
De obicei folosim funcția VLOOKUP pentru a găsi o valoare cunoscând rândul Index și numărul coloanei. Pentru funcția VLOOKUP trebuie să avem numărul coloanei și criteriile de potrivire a rândurilor și cel mai crucial lucru este valoarea pe care trebuie să o aflăm în dreapta valorii de potrivire. Dar datele nu acceptă întotdeauna acest lucru. De multe ori trebuie să căutăm valori fără să știm indicele rândului sau coloanei, având doar valorile pe care să le potrivim. De exemplu: găsirea salariului unui angajat cu ID de angajat. În aceasta vom căuta ID-ul angajaților pentru indexul rândului și Salariul pentru indexul coloanelor. Să aflăm cum putem face această căutare folosind INDEX & MATCH.
Cum se rezolvă problema?
Pentru ca formula să înțeleagă mai întâi, trebuie să revizuim puțin despre următoarele funcții
- Funcția INDEX
- Funcția MATCH
Acum vom face o formulă folosind funcțiile de mai sus. Funcția de potrivire va returna indexul valorii de căutare1 în câmpul antetului rândului. Și o altă funcție MATCH va returna indexul valorii de căutare 2 din câmpul antet coloană. Numerele index vor fi acum introduse în funcția INDEX pentru a obține valorile sub valoarea de căutare din datele tabelului.
Formula generică:
| = INDEX (date, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0))) |
date : matrice de valori în interiorul tabelului fără anteturi
lookup_value1 : valoare de căutat în rândul_antet.
head_headers : Matrice de indexuri de rânduri pentru căutare.
lookup_value1 : valoare de căutat în header_coloană.
header_coloane : coloană Index matrice pentru căutare.
Exemplu:
Afirmațiile de mai sus pot fi complicate de înțeles. Deci, să înțelegem acest lucru folosind formula dintr-un exemplu
Aici avem o listă a scorurilor obținute de studenți cu lista lor de subiecte. Trebuie să găsim Scorul pentru un anumit student (Gary) și subiect (studii sociale), așa cum se arată în instantaneul de mai jos.
Valoarea Student1 trebuie să se potrivească matricei Row_header și valoarea Subject2 trebuie să se potrivească matricei Column_header.
Utilizați formula din celula J6:
| = INDEX (tabel, MATCH (J5, rând, 0, MATCH (J4, coloană, 0))) |
Explicaţie:
- Funcția MATCH se potrivește cu valoarea Student din celula J4 cu matricea de antet de rând și își returnează poziția 3 ca număr.
- Funcția MATCH se potrivește cu valoarea Subiectului din celula J5 cu matricea de antet de coloană și își returnează poziția 4 ca număr.
- Funcția INDEX ia numărul de index al rândului și coloanei și caută în datele din tabel și returnează valoarea potrivită.
- Argumentul de tip MATCH este fixat la 0. Deoarece formula va extrage potrivirea exactă.
Aici valorile formulei sunt date ca referințe de celulă și rândul_antet, tabelul și coloana_antet dat ca intervale denumite.
După cum puteți vedea în instantaneul de mai sus, am obținut Scorul obținut de student Gary în Subiect Studii Sociale ca 36. Și dovedește că formula funcționează bine și pentru îndoieli consultați notele de mai jos pentru înțelegere.
Acum vom folosi potrivirea aproximativă cu anteturile de rând și antetele de coloană ca numere. Potrivirea aproximativă ia doar valorile numerice, deoarece nu există nicio modalitate de a se aplica valorilor text
Aici avem un preț al valorilor conform Înălțimii și Lățimii produsului. Trebuie să găsim prețul pentru o anumită înălțime (34) și lățime (21), așa cum se arată în instantaneul de mai jos.
Valoarea Height1 trebuie să se potrivească matricei Row_header și Width value2 trebuie să se potrivească matricei Column_header.
Utilizați formula din celula K6:
| = INDEX (date, MATCH (K4, Înălțime, 1, MATCH (K5, Lățime, 1))) |
Explicaţie:
- Funcția MATCH se potrivește cu valoarea Înălțimii din celula K4 cu matricea de antet de rând și își returnează poziția 3 ca număr.
- Funcția MATCH se potrivește cu valoarea Lățimii din celula K5 cu matricea de antet de coloană și își returnează poziția 2 ca număr.
- Funcția INDEX ia numărul de index al rândului și coloanei și caută în datele din tabel și returnează valoarea potrivită.
- Argumentul de tip MATCH este fixat la 1. Deoarece formula va extrage potrivirea aproximativă.
Aici valorile formulei sunt date ca referințe de celulă și rând_antet, date și coloană_antet dat ca intervale denumite așa cum se menționează în instantaneul de mai sus.
De asemenea, puteți efectua căutări potrivite exacte utilizând funcțiile INDEX și MATCH în Excel. Aflați mai multe despre cum se face căutarea sensibilă la majuscule și minuscule folosind funcția INDEX & MATCH în Excel. De asemenea, puteți căuta potrivirile parțiale folosind metacaracterele din Excel.
După cum puteți vedea în instantaneul de mai sus, am obținut prețul obținut în funcție de înălțime (34) & Lățime (21) ca 53.10. Și dovedește că formula funcționează bine și, pentru îndoieli, consultați notele de mai jos pentru a înțelege mai bine.
Note:
- Funcția returnează eroarea #NA dacă argumentul matricei de căutare la funcția MATCH este matricea 2 D care este câmpul antet al datelor …
- Funcția se potrivește cu valoarea exactă, deoarece argumentul tipului de potrivire cu funcția MATCH este 0.
- Valorile de căutare pot fi date ca referință de celulă sau direct folosind simbolul ghilimelei (") în formulă ca argumente.
Sper că acest articol despre Cum se utilizează Utilizarea funcției INDEX și MATCH pentru a căuta valoarea în Excel este explicativ. Găsiți mai multe articole despre calcularea valorilor și formulele Excel conexe aici. Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail.
Utilizați INDEX și MATCH pentru a căuta valoarea : Formula INDEX-MATCH este utilizată pentru a căuta dinamic și precis o valoare dintr-un tabel dat. Aceasta este o alternativă la funcția VLOOKUP și depășește neajunsurile funcției VLOOKUP.
Utilizați VLOOKUP din două sau mai multe tabele de căutare : Pentru a căuta din mai multe tabele putem lua o abordare IFERROR. Pentru a căuta din mai multe tabele, este nevoie de eroare ca un comutator pentru tabelul următor. O altă metodă poate fi abordarea If.
Cum se face o căutare sensibilă la majuscule și minuscule în Excel : Funcția VLOOKUP a Excel nu este sensibilă la majuscule și minuscule și va returna prima valoare potrivită din listă. INDEX-MATCH nu face excepție, dar poate fi modificat pentru a face sensibil la majuscule. Să vedem cum …
Căutați text care apare frecvent cu criterii în Excel : Căutarea apare cel mai frecvent în text într-un interval pe care îl folosim funcția INDEX-MATCH cu MOD. Iată metoda.
Articole populare:
Cum se utilizează funcția IF în Excel : Instrucțiunea IF din Excel verifică starea și returnează o valoare specifică dacă condiția este ADEVĂRATĂ sau returnează o altă valoare specifică dacă este FALSĂ.
Cum se utilizează funcția VLOOKUP în Excel : Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se utilizează funcția SUMIF în Excel : Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.
Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.