INDEX en MATCH met meerdere matrices
Stel dat u meerdere tabellen hebt met dezelfde bijschriften, zoals hieronder weergegeven. Dan kan het lastig zijn om het juiste zoekwaardebereik te vinden dat voldoet aan de opgegeven criteria. In deze handleiding laten we u zien hoe u een waarde kunt opzoeken in meerdere matrices, bereiken of groepen door specifieke criteria te vergelijken met de INDEX-, MATCH- en CHOOSE-functies.
Hoe zoekt u een waarde op in meerdere matrices?
Om de leiders van verschillende groepen uit verschillende afdelingen te achterhalen, gebruikt u eerst de functie CHOOSE om aan te geven uit welke tabel de naam van de leider moet worden opgehaald. Vervolgens bepaalt de functie MATCH de positie van de leider in de bijbehorende tabel. Ten slotte haalt de functie INDEX de naam van de leider op op basis van die positie en de opgegeven kolom waarin de leidersnamen staan.
Algemene syntaxis
=INDEX(CHOOSE())array_num,array1 ,array2 ,…),MATCH(lookup_value,lookup_array,0),column_num)
- array_num: Het getal dat CHOOSE gebruikt om een array aan te geven uit de lijst array1, array2, …, waaruit het resultaat moet worden geretourneerd.
- array1, array2, …: De arrays waaruit het resultaat moet worden geretourneerd. In dit geval verwijzen ze naar de drie tabellen.
- Zoekwaarde: De waarde die de combinatieformule gebruikt om de positie van de bijbehorende leider te vinden. In dit geval verwijst dat naar de opgegeven groep.
- Zoekmatrix: Het celbereik waarin de zoekwaarde is opgenomen. Hier verwijst dit naar het groepsbereik. Opmerking: u kunt het groepsbereik van elke afdeling gebruiken, aangezien deze allemaal identiek zijn en we alleen het positienummer nodig hebben.
- kolom_num: De kolom die u opgeeft om gegevens uit op te halen.
Om de leider van Groep D van afdeling Ate achterhalen, kopieert of typt u de onderstaande formule in cel G5 en drukt u op Enterom het resultaat te verkrijgen:
=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 bereiken voor namen en klassen in de formule niet veranderen wanneer u de formule naar andere cellen verplaatst of kopieert. Nadat u de formule hebt ingevoerd, sleept u de vulgreep omlaag om de formule op de cellen eronder toe te passen en wijzigt u vervolgens de array_numdienovereenkomstig.
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 functie CHOOSE retourneert de 1e array uit de drie arrays die in de formule zijn opgenomen. Er wordt dus $B$5:$C$8 geretourneerd: het gegevensbereik van afdeling A.
- MATCH(F5,$B$5:$B$8,0): Het argument match_type 0 dwingt de functie MATCH om de positie van de eerste overeenkomst van Groep D — de waarde in cel F5 — in de matrix $B$5:$B$8 te vinden, 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),2) = INDEX($B$5:$C$8,4,2De functie INDEX haalt de waarde op op het kruispunt van de 4e rij en de 2e kolom van het bereik $B$5:$C$8, wat Emily is.
Om te voorkomen dat u elke keer dat u de formule kopieert de array_numhoeft te wijzigen, kunt u de hulpkolom (kolom D) gebruiken. De formule ziet er dan als volgt uit:
=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,3in de hulpkolom geven de array1,array2,array3binnen de functie CHOOSE aan.
Gerelateerde functies
De Excel-functie INDEX retourneert de weergegeven waarde op basis van een opgegeven positie binnen een bereik of matrix.
De Excel-functie MATCH zoekt naar een specifieke waarde in een celbereik en geeft de relatieve positie van die waarde terug.
De functie CHOOSE retourneert een waarde uit een lijst op basis van het opgegeven indexnummer. Bijvoorbeeld: CHOOSE(3, "Apple", "Peach", "Orange") geeft „Orange" terug — het indexnummer is 3, en „Orange" is de derde waarde in de lijst.
Gerelateerde formules
Zoekwaardebereik uit een ander werkblad of een andere werkmap
Als u weet hoe u de functie VLOOKUP gebruikt om waarden in een werkblad te zoeken, is het geen enkel probleem om waarden op te zoeken uit een ander werkblad of zelfs een andere werkmap.
VLOOKUP met dynamische bladnaam
In veel gevallen moet u gegevens verzamelen uit meerdere werkbladen voor een samenvatting. Door de functies VLOOKUP en INDIRECT te combineren, kunt u een krachtige formule bouwen die specifieke waarden dynamisch opzoekt in verschillende werkbladen op basis van de bladnaam.
Opzoeken met meerdere criteria met INDEX en MATCH
Bij het werken met een grote database in een Excel-werkblad vol kolommen en rijbijschriften is het vaak lastig om gegevens te vinden die aan meerdere criteria voldoen. In zo’n geval kunt u een matrixformule combineren met de functies INDEX en MATCH voor een doelgerichte zoekopdracht.
De beste Office-productiviteitshulpmiddelen
Kutools voor Excel – Helpt u om op te vallen tussen de massa
Kutools voor Excel Beschikt over meer dan 300 functies,zodat u alles wat u nodig heeft binnen één klik bereik heeft...
Office Tab – Schakel tabbladenlezen en -bewerken in Microsoft Office (inclusief Excel) in
- Schakel in één seconde tussen tientallen geopende documenten!
- Bespaar honderden muisklikken per dag en zeg vaarwel tegen muisarm.
- Verhoog uw productiviteit met 50 % bij het bekijken en bewerken van meerdere documenten.
- Brengt efficiënte tabs naar Office (inclusief Excel), net als in Chrome, Edge en Firefox.