În acest articol, vom învăța cum să căutăm valori în tabelul 2d folosind o funcție INDEX-MATCH-MATCH în Excel.
Scenariu:
Să presupunem că trebuie să faceți mai multe căutări dintr-un tabel care are sute de coloane. În astfel de cazuri, utilizarea unor formule diferite pentru fiecare căutare va dura prea mult timp. Ce zici de crearea unei formule de căutare dinamică pe care o puteți căuta cu antetul furnizat. Da, putem face asta. Această formulă se numește formula INDEX MATCH MATCH sau spune o formulă de căutare 2d.
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
Funcția INDEX returnează valoarea la un indice dat dintr-o matrice.
Funcția MATCH returnează indexul primei apariții a valorii într-o matrice (matrice cu o singură dimensiune).
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ăutare2 în câmpul antet coloană. Numerele index vor fi introduse acum în funcția INDEX pentru a obține valorile sub valoarea de căutare din datele din tabelul 2D.
Formula generică:
= INDEX (date, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0)))
Date: matrice de valori din interiorul tabelului fără anteturi
lookup_value1 : valoare de căutat în rândul_antet.
head_headers : Matrice de indexuri de rânduri pentru a căuta în sus.
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ând_header, tabel și column_header date 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.
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 se aplică valorilor textului
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.
După cum puteți vedea în instantaneul de mai sus, avem prețul obținut în funcție de înălțime (34) & Lățime (21) ca 53.10. 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ă ați înțeles cum să utilizați tabelul Căutare în 2 D folosind funcția INDEX & MATCH în Excel. Explorați aici mai multe articole în valoarea de căutare Excel. Vă rugăm să nu ezitați să vă expuneți întrebările de mai jos în caseta de comentarii. Cu siguranță vă vom ajuta.
Utilizați INDEX și MATCH pentru a căuta valoarea : Funcția INDEX & MATCH pentru a căuta valoarea după cum este necesar.
Gama SUMĂ cu INDEX în Excel : Utilizați funcția INDEX pentru a găsi SUMA valorilor, după cum este necesar.
Cum se utilizează funcția SUM în Excel : Găsiți SUMA de numere utilizând funcția SUM explicată cu un exemplu.
Cum se utilizează funcția INDEX în Excel : Găsiți INDEX-ul matricei folosind funcția INDEX explicată cu un exemplu.
Cum se utilizează funcția MATCH în Excel : Găsiți MATCH-ul în matrice folosind valoarea INDEX din funcția MATCH explicată cu un exemplu.
Cum se utilizează funcția de căutare în Excel : Găsiți valoarea de căutare în matrice folosind funcția LOOKUP explicată cu un exemplu.
Cum se utilizează funcția VLOOKUP în Excel : Găsiți valoarea de căutare în matrice utilizând funcția VLOOKUP explicată cu un exemplu.
Cum se utilizează funcția HLOOKUP în Excel : Găsiți valoarea de căutare în matrice utilizând funcția HLOOKUP explicată cu un exemplu.
Articole populare
50 Comandă rapidă Excel pentru a vă crește productivitatea
Editați o listă derulantă
Referință absolută în Excel
Dacă cu formatare condiționată
Dacă cu wildcards
Vizualizați după dată
Alăturați-vă prenumelui și prenumelui în Excel
