INDEX en MATCH met meerdere arrays
Stel dat je verschillende tabellen hebt met dezelfde koppen zoals hieronder weergegeven, om waarden te zoeken die overeenkomen met de gegeven criteria in deze tabellen kan een lastige taak zijn. In deze tutorial bespreken we hoe je een waarde kunt opzoeken in meerdere arrays, bereiken of groepen door specifieke criteria te matchen met de INDEX-, MATCH- en CHOOSE-functies.
Hoe zoek je een waarde op in meerdere arrays?
Om de leiders van verschillende groepen die bij verschillende afdelingen horen te achterhalen, kun je eerst de CHOOSE-functie gebruiken om de tabel te selecteren waaruit de naam van de leider moet worden geretourneerd. De MATCH-functie zal vervolgens de positie van de leider in de tabel vinden waar hij/zij bij hoort. Tenslotte zal de INDEX-functie de leider ophalen op basis van de positie-informatie plus de specifieke kolom waar de namen van de leiders staan.
Algemene syntaxis
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(zoek_waarde,zoek_bereik,0),kolom_num)
- array_num: Het nummer dat CHOOSE gebruikt om een array uit de lijst array1,array2,… aan te duiden om het resultaat uit te retourneren.
- array1,array2,…: De arrays waaruit het resultaat wordt geretourneerd. Hier verwijzen ze naar de drie tabellen.
- zoek_waarde: De waarde die de combinatieformule gebruikt om de positie van de bijbehorende leider te vinden. Hier verwijst het naar de gegeven groep.
- zoek_bereik: Het bereik van cellen waarin de zoek_waarde wordt vermeld. Hier verwijst het naar het groepsbereik. Opmerking: Je kunt het groepsbereik van elke afdeling gebruiken omdat ze allemaal hetzelfde zijn en we alleen het positienummer nodig hebben.
- kolom_num: De kolom die je aangeeft waaruit je gegevens wilt ophalen.
Om de leider van Groep D die behoort tot Afdeling A te weten, kopieer of voer de onderstaande formule in cel G5 in, en druk op Enter om het resultaat te krijgen:
=INDEX(CHOOSE(1,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
√ Opmerking: De dollartekens ($) hierboven geven absolute verwijzingen aan, wat betekent dat de naam- en klassebereiken in de formule niet zullen veranderen wanneer je de formule naar andere cellen verplaatst of kopieert. Nadat je de formule hebt ingevoerd, sleep je de vulgreep naar beneden om de formule toe te passen op de onderliggende cellen, en pas je vervolgens de array_num dienovereenkomstig aan.
Uitleg van de formule
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): De CHOOSE-functie retourneert de eerste array uit de drie arrays die in de formule zijn vermeld. Dus het zal $B$5:$C$8 retourneren, oftewel het databereik van Afdeling A.
- MATCH(F5,$B$5:$B$8,0): Het match_type 0 dwingt de MATCH-functie om de positie van de eerste overeenkomst van Groep D, de waarde in cel F5, in het array $B$5:$B$8 te retourneren, wat 4 is.
- INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),22) = INDEX($B$5:$C$8,4,22): De INDEX-functie haalt de waarde op het snijpunt van de 4e rij en 2e kolom van het bereik $B$5:$C$8 op, wat Emily is.
Om te voorkomen dat je de array_num in de formule elke keer als je deze kopieert moet wijzigen, kun je de hulpkolom, kolom D, gebruiken. De formule zou er dan als volgt uitzien:
=INDEX(CHOOSE(D5,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
√ Opmerking: De getallen 1, 2, 3 in de hulpkolom geven de array1, array2, array3 binnen de CHOOSE-functie aan.
Gerelateerde functies
De Excel INDEX-functie retourneert de weergegeven waarde op basis van een bepaalde positie uit een bereik of een array.
De Excel MATCH-functie zoekt naar een specifieke waarde in een bereik van cellen en retourneert de relatieve positie van de waarde.
De CHOOSE-functie retourneert een waarde uit de lijst met waardeargumenten op basis van het gegeven indexnummer. Bijvoorbeeld, CHOOSE(3,”Apple”,”Peach”,”Orange”) retourneert Orange, het indexnummer is 3, en Orange is de derde waarde na het indexnummer in de functie.
Gerelateerde formules
Waarden opzoeken in een ander werkblad of werkmap
Als je weet hoe je de VLOOKUP-functie moet gebruiken om waarden in een werkblad te zoeken, zal het geen probleem zijn om waarden op te zoeken in een ander werkblad of werkmap.
Vlookup met dynamische bladnaam
In veel gevallen moet je mogelijk gegevens verzamelen uit meerdere werkbladen voor samenvatting. Met de combinatie van de VLOOKUP-functie en de INDIRECT-functie kun je een formule maken om specifieke waarden op te zoeken in werkbladen met een dynamische bladnaam.
Opzoeken met meerdere criteria met INDEX en MATCH
Wanneer je te maken hebt met een grote database in een Excel-spreadsheet met verschillende kolommen en rijkoppen, is het altijd lastig om iets te vinden dat aan meerdere criteria voldoet. In dit geval kun je een matrixformule gebruiken met de INDEX- en MATCH-functies.
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.