Tabellen samenvoegen met INDEX en MATCH
Stel dat je twee of meer tabellen hebt met een gemeenschappelijke kolom, maar de gegevens in de gemeenschappelijke kolommen staan niet in dezelfde volgorde. In dit geval kun je, om de tabellen samen te voegen, combineren of joinen terwijl je de gegevens matcht, gebruik maken van de functies INDEX en MATCH.
Hoe samenvoegen van tabellen met INDEX en MATCH?
Om tabel 1 en tabel 2 samen te voegen en alle informatie in de nieuwe tabel te combineren zoals te zien is in de bovenstaande schermafbeelding, moet je eerst de gegevens uit tabel 1 of tabel 2 kopiëren naar de nieuwe tabel (hier heb ik de gegevens uit tabel 1 gekopieerd, zie onderstaande schermafbeelding). Neem bijvoorbeeld het eerste Student ID 23201 in de nieuwe tabel: de functies INDEX en MATCH helpen je om op deze manier de score en ranginformatie op te halen: MATCH retourneert de rijpositie van het student ID dat overeenkomt met 23201 in tabel 2. Deze rij-informatie wordt doorgegeven aan INDEX om de waarde op het snijpunt van die rij en de gespecificeerde kolom (score- of rangkolom) op te halen.
Algemene syntaxis
=INDEX(return_table;MATCH(lookup_value;lookup_array;0);col_num)
√ Opmerking: Aangezien we de informatie uit tabel 1 al hebben ingevuld, hoeven we nu alleen nog maar overeenkomende gegevens uit tabel 2 op te halen.
- return_table: De tabel waaruit de scores van de studenten moeten worden opgehaald. Hier verwijst dit naar tabel 2.
- lookup_value: De waarde die je gebruikt om de informatie in de return_table te matchen. Hier verwijst dit naar de student ID-waarde in de nieuwe tabel.
- lookup_array: Het bereik van cellen met de waarden waarmee vergeleken moet worden met de lookup_value. Hier verwijst dit naar de student ID-kolom in de return_table.
- col_num: Het kolomnummer dat aangeeft uit welke kolom van de return_table de overeenkomstige informatie moet worden opgehaald.
- 0: Het match_type 0 dwingt MATCH om een exacte overeenkomst uit te voeren.
Om overeenkomende gegevens uit tabel 2 op te halen om alle informatie in de nieuwe tabel te combineren, kopieer of voer de onderstaande formules in de cellen F16 en G16 in en druk op Enter om de resultaten te krijgen:
Cel F16 (Score)
=INDEX($F$5:$H$11;MATCH(C16;$F$5:$F$11;0);2)
Cel G16 (Rang)
=INDEX($F$5:$H$11;MATCH(C16;$F$5:$F$11;0);3)
√ Opmerking: De dollartekens ($) hierboven geven absolute verwijzingen aan, wat betekent dat de return_table en lookup_array in de formule niet veranderen wanneer je de formules naar andere cellen verplaatst of kopieert. Er zijn echter geen dollartekens toegevoegd aan de lookup_value omdat je wilt dat deze dynamisch blijft. Nadat je de formule hebt ingevoerd, sleep je het vulhandvat naar beneden om de formule toe te passen op de onderliggende cellen.
Uitleg van de formule
Hier gebruiken we de onderstaande formule als voorbeeld:
=INDEX($F$5:$H$11;MATCH(C16;$F$5:$F$11;0);2)
- MATCH(C16;$F$5:$F$11;0): Het match_type 0 dwingt de MATCH-functie om een exacte overeenkomst uit te voeren. De functie retourneert vervolgens de positie van de overeenkomstige waarde van 23201 (de waarde in C16) in de zoekarray $F$5:$F$11. Dus zal de functie 3 retourneren, omdat de overeenkomstige waarde zich op de 3e positie in het bereik bevindt.
- INDEX($F$5:$H$11$F$5:$H$11;MATCH(C16;$F$5:$F$11;0);22) = INDEX($F$5:$H$11$F$5:$H$11;3;22): De INDEX-functie retourneert de waarde op het snijpunt van de 3e rij en de 2e kolom van de return-tabel $F$5:$H$11, wat 91 is.
Gerelateerde functies
De Excel INDEX-functie retourneert de weergegeven waarde op basis van een bepaalde positie uit een bereik of een matrix.
De Excel MATCH-functie zoekt naar een specifieke waarde in een bereik van cellen en retourneert de relatieve positie van de waarde.
Gerelateerde formules
Exacte overeenkomst met INDEX en MATCH
Als je informatie in Excel moet vinden over een specifiek product, film of persoon, etc., kun je goed gebruik maken van de combinatie van INDEX- en MATCH-functies.
Benaderende overeenkomst met INDEX en MATCH
Er zijn momenten waarop we benaderende overeenkomsten in Excel moeten vinden om de prestaties van werknemers te evalueren, de cijfers van studenten te beoordelen, postkosten te berekenen op basis van gewicht, enz. In deze handleiding bespreken we hoe we de functies INDEX en MATCH kunnen gebruiken om de resultaten die we nodig hebben, op te halen.
Zoeken naar dichtstbijzijnde overeenkomstige waarde met meerdere criteria
In sommige gevallen moet je mogelijk de dichtstbijzijnde of benaderende overeenkomstige waarde zoeken op basis van meer dan één criterium. Met de combinatie van INDEX, MATCH en ALS-functies kun je dit snel doen in Excel.
De Beste Office-productiviteitstools
Kutools voor Excel - Helpt U Om Uit Te Blinken In De Menigte
Kutools voor Excel Beschikt Over Meer Dan 300 Functies, Waardoor Wat U Nodig Hebt Maar Een Klik Verwijderd Is...
Office Tab - Schakel Tabbladgestuurd Lezen en Bewerken in Microsoft Office (inclusief Excel)
- Eén seconde om te schakelen tussen tientallen open documenten!
- Verminder honderden muisklikken voor u elke dag, zeg vaarwel tegen muisarm.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt Efficiënte Tabbladen naar Office (inclusief Excel), Net Als Chrome, Edge en Firefox.