Opzoeken in 2D-tabel met de functie INDEX & MATCH

In dit artikel zullen we leren hoe u waarden in 2D-tabel kunt opzoeken met behulp van een INDEX-MATCH-MATCH-functie in Excel.

Scenario:

Stel dat u meerdere opzoekingen moet doen vanuit een tabel die honderden kolommen heeft. In dergelijke gevallen zal het gebruik van verschillende formules voor elke zoekopdracht te veel tijd kosten. Hoe zit het met het maken van een dynamische opzoekformule die u kunt opzoeken door de opgegeven koptekst. Ja, we kunnen dit. Deze formule wordt INDEX MATCH MATCH-formule genoemd, of zeg maar een 2D-opzoekformule.

Hoe het probleem op te lossen?

Om de formule eerst te begrijpen, moeten we de volgende functies een beetje herzien:

1. INDEX-functie
2. MATCH-functie

De functie INDEX retourneert de waarde bij een bepaalde index in een array.

MATCH functie retourneert de index van de eerste verschijning van de waarde in een array ( single dimension array ).

Nu gaan we een formule maken met behulp van de bovenstaande functies. Match-functie retourneert de index van de opzoekwaarde1 in het rijkopveld. En een andere MATCH-functie retourneert de index van de opzoekwaarde2 in het kolomkopveld. De indexnummers worden nu ingevoerd in de INDEX-functie om de waarden onder de opzoekwaarde uit de 2D-tabelgegevens te halen.

Generieke formule:

= INDEX (data, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0)))

Gegevens: reeks waarden in de tabel zonder kopteksten

lookup_value1 : waarde om op te zoeken in de row_header.

rij_headers : Rij-indexarray om op te zoeken.

lookup_value1 : waarde om op te zoeken in de column_header.

column_headers : kolom Indexarray om op te zoeken.

Voorbeeld:

De bovenstaande uitspraken kunnen ingewikkeld zijn om te begrijpen. Laten we dit dus begrijpen door de formule in een voorbeeld te gebruiken

Hier hebben we een lijst met scores die studenten hebben behaald met hun lijst met onderwerpen. We moeten de score voor een specifieke student (Gary) en onderwerp (sociale studies) vinden, zoals weergegeven in de onderstaande snapshot.

De Student-waarde1 moet overeenkomen met de Row_header-array en Subject-waarde2 moet overeenkomen met de Column_header-array.
Gebruik de formule in de J6-cel:

= INDEX (tabel, MATCH (J5, rij, 0, MATCH (J4, kolom, 0)))

Uitleg:

• De MATCH-functie komt overeen met de Student-waarde in J4-cel met de rijkoparray en retourneert zijn positie 3 als een nummer.
• De MATCH-functie komt overeen met de Subject-waarde in J5-cel met de kolomkopmatrix en retourneert de positie 4 als een nummer.
• De INDEX-functie neemt het rij- en kolomindexnummer en zoekt op in de tabelgegevens en retourneert de overeenkomende waarde.
• Het MATCH-typeargument is vast ingesteld op 0. Omdat de formule de exacte overeenkomst zal extraheren.

Hier worden waarden voor de formule gegeven als celverwijzingen en row_header, table en column_header gegeven als benoemde bereiken.
Zoals je kunt zien in de bovenstaande momentopname, hebben we de score verkregen door student Gary in Onderwerp Sociale wetenschappen als 36 .
Het bewijst dat de formule goed werkt en voor twijfels zie de onderstaande opmerkingen voor begrip.

Nu zullen we de geschatte overeenkomst met rijkoppen en kolomkoppen als getallen gebruiken. Geschatte overeenkomst neemt alleen de getalwaarden omdat het op geen enkele manier van toepassing is op tekstwaarden

Hier hebben we een prijs van waarden volgens de hoogte en breedte van het product. We moeten de prijs vinden voor een specifieke hoogte (34) en breedte (21) zoals weergegeven in de onderstaande snapshot.

De hoogtewaarde1 moet overeenkomen met de rij_header-array en de breedtewaarde2 moet overeenkomen met de kolom_header-array.
Gebruik de formule in de K6-cel:

= INDEX (gegevens, MATCH (K4, Hoogte, 1 , MATCH (K5, Breedte, 1)))

Uitleg:

• De MATCH-functie komt overeen met de hoogtewaarde in K4-cel met de rijkoparray en retourneert zijn positie 3 als een nummer.
• De MATCH-functie komt overeen met de breedtewaarde in K5-cel met de kolomkopmatrix en retourneert zijn positie 2 als een nummer.
• De INDEX-functie neemt het rij- en kolomindexnummer en zoekt op in de tabelgegevens en retourneert de overeenkomende waarde.
• Het MATCH-typeargument is vast ingesteld op 1. Omdat de formule de geschatte overeenkomst zal extraheren.

Hier worden waarden voor de formule gegeven als celverwijzingen en row_header, data en column_header gegeven als benoemde bereiken zoals vermeld in de bovenstaande snapshot.

Zoals je kunt zien in de bovenstaande momentopname, hebben we de prijs verkregen door hoogte (34) & Breedte (21) als 53.10 . Het bewijst dat de formule goed werkt en voor twijfels zie de opmerkingen hieronder voor meer begrip.
Opmerkingen:

1. De functie retourneert de #NA-fout als het opzoekarrayargument voor de MATCH-functie een 2D-array is, het kopveld van de gegevens …
2. De functie komt overeen met de exacte waarde, aangezien het argument voor het type overeenkomst met de functie VERGELIJKEN 0 is.
3. De opzoekwaarden kunnen worden opgegeven als celverwijzing of rechtstreeks met het aanhalingsteken ( " ) in de formule als argumenten.

Ik hoop dat je hebt begrepen hoe je de Lookup in 2D-tabel kunt gebruiken met de INDEX & MATCH-functie in Excel. Ontdek hier meer artikelen in Excel-opzoekwaarde. Aarzel niet om uw vragen hieronder in het opmerkingenveld te vermelden. We zullen je zeker helpen.

Gebruik INDEX en MATCH om waarde op te zoeken : INDEX & MATCH-functie om desgewenst de waarde op te zoeken.

SUM-bereik met INDEX in Excel : Gebruik de INDEX-functie om de SUM van de waarden te vinden zoals vereist.

Hoe de SOM-functie in Excel te gebruiken? : Vind de SOM van getallen met behulp van de SOM-functie uitgelegd met een voorbeeld.

Hoe de INDEX-functie in Excel te gebruiken? : Zoek de INDEX van array met behulp van de INDEX-functie uitgelegd met een voorbeeld.

Hoe de MATCH-functie in Excel te gebruiken? : Zoek de MATCH in de array met behulp van de INDEX-waarde in de MATCH-functie uitgelegd met een voorbeeld.

Hoe de LOOKUP-functie in Excel te gebruiken? : Zoek de opzoekwaarde in de array met behulp van de LOOKUP-functie die met een voorbeeld wordt uitgelegd.

Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? : Zoek de opzoekwaarde in de array met behulp van de functie VERT.ZOEKEN, uitgelegd aan de hand van een voorbeeld.

Hoe de HORIZ.ZOEKEN-functie in Excel te gebruiken? : Zoek de opzoekwaarde in de array met behulp van de functie HORIZ.ZOEKEN, uitgelegd met een voorbeeld.

populaire artikels

50 Excel-snelkoppeling om uw productiviteit te verhogen

Een vervolgkeuzelijst bewerken

Absolute referentie in Excel

Indien met voorwaardelijke opmaak

Indien met jokertekens

Opzoeken op datum

Voeg voor- en achternaam toe in Excel