INDEX en MATCH met meerdere arrays
Laten we zeggen dat u meerdere tabellen heeft met dezelfde bijschriften als hieronder weergegeven, om waarden op te zoeken die overeenkomen met de criteria voor geven van deze tabellen, kan een zware taak voor u zijn. In deze zelfstudie zullen we het hebben over het opzoeken van een waarde in meerdere arrays, bereiken of groepen door specifieke criteria te matchen met de INDEX, MATCH en KIEZEN functies.
Hoe een waarde opzoeken in meerdere arrays?
Om de leiders van verschillende groepen die tot verschillende afdelingen behoren, kunt u eerst de functie KIEZEN gebruiken om de tafel te targeten waarvandaan de naam van de leider wordt geretourneerd. De MATCH-functie zal dan de positie van de leider in de tabel vinden waar hij/zij bij hoort. Ten slotte zal de INDEX-functie de leider ophalen op basis van de positie-informatie plus de specifieke kolom waarin de namen van de leiders worden vermeld.
Algemene syntaxis
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- array_getal: Het nummer CHOOSE dat wordt gebruikt om een array uit de lijst aan te geven reeks1,reeks2,… om het resultaat van te retourneren.
- array1,array2,...: De arrays waaruit het resultaat moet worden geretourneerd. Hier wordt verwezen naar de drie tabellen.
- opzoekwaarde: De waarde die de combinatieformule heeft gebruikt om de positie van de bijbehorende leider te vinden. Hier verwijst naar de gegeven groep.
- lookup_array: Het bereik van cellen waar de opzoekwaarde wordt vermeld. Hier wordt verwezen naar het groepsbereik. Opmerking: u kunt het groepsbereik van elke afdeling gebruiken, omdat ze allemaal hetzelfde zijn en we alleen het positienummer nodig hebben.
- kolom_getal: De kolom die u aangeeft waaruit u gegevens wilt ophalen.
Om de leider van groep D die behoort tot de afdeling A, kopieer of voer de onderstaande formule in de cel G5 in en druk op Enter om het resultaat te krijgen:
=INDEX(KIES(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),BIJ ELKAAR PASSEN(F5,$ B $ 5: $ B $ 8,0),2)
√ Opmerking: De dollartekens ($) hierboven geven absolute verwijzingen aan, wat betekent dat de naam en het klassenbereik 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 onderstaande cellen toe te passen en wijzigt u vervolgens de array_getal overeenkomstig.
Verklaring 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 1st array uit de drie arrays die in de formule worden vermeld. Dus het zal terugkeren $B$5:$C$8, dat wil zeggen, de gegevensbereik van afdeling A.
- WEDSTRIJD(F5,$B$5:$B$8,0): Het match_type 0 dwingt de MATCH-functie om de positie van de eerste match van . te retourneren Groep D, de waarde in de cel F5, in de array $ B $ 5: $ B $ 8Dit is 4.
- INHOUDSOPGAVE(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),WEDSTRIJD(F5,$B$5:$B$8,0),2) = INDEX($B$5:$C$8,4,2): De INDEX-functie haalt de waarde op het snijpunt van de 4de rij en 2e kolom van het bereik $B$5:$C$8Dit is Emily.
Om te voorkomen dat u verandert: array_getal in de formule elke keer dat u deze kopieert, kunt u de helperkolom gebruiken, de kolom D. De formule zou als volgt zijn:
=INDEX(KIES(D5,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),BIJ ELKAAR PASSEN(F5,$ B $ 5: $ B $ 8,0),2)
√ Opmerking: de cijfers 1, 2, 3 geef in de helperkolom de . aan array1, array2, array3 binnen de KIEZEN-functie.
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 celbereik en retourneert de relatieve positie van de waarde.
De CHOOSE-functie retourneert een waarde uit de lijst met waardeargumenten op basis van het opgegeven indexnummer. CHOOSE(3,"Apple","Perzik","Orange") retourneert bijvoorbeeld Oranje, het indexnummer is 3 en Oranje is de derde waarde na het indexnummer in de functie.
Verwante formules
Zoek waarden op uit een ander werkblad of een andere werkmap
Als u weet hoe u de functie VERT.ZOEKEN moet gebruiken om naar waarden in een werkblad te zoeken, zullen vlookup-waarden uit een ander werkblad of een andere werkmap geen probleem voor u zijn.
Vlookup met dymanische bladnaam
In veel gevallen moet u voor een samenvatting gegevens verzamelen over meerdere werkbladen. Met de combinatie van de functie VERT.ZOEKEN en de functie INDIRECTE, kunt u een formule maken om specifieke waarden op te zoeken in werkbladen met een dynamische bladnaam.
Opzoeken met meerdere criteria met INDEX en MATCH
Als je te maken hebt met een grote database in een Excel-spreadsheet met meerdere kolommen en rijbijschriften, is het altijd lastig om iets te vinden dat aan meerdere criteria voldoet. In dit geval kunt u een matrixformule gebruiken met de functies INDEX en VERGELIJKEN.
De beste tools voor kantoorproductiviteit
Kutools for Excel - Helpt u zich te onderscheiden van de menigte
Wilt u uw dagelijkse werkzaamheden snel en perfect afronden? Kutools voor Excel biedt 300 krachtige geavanceerde functies (werkmappen combineren, optellen op kleur, celinhoud splitsen, datum converteren, enzovoort ...) en 80% tijd voor u besparen.
- Ontworpen voor 1500 werkscenario's, helpt u 80% Excel-problemen op te lossen.
- Verminder elke dag duizenden toetsenbord- en muisklikken, verlicht uw vermoeide ogen en handen.
- Word een Excel-expert in 3 minuten. U hoeft geen pijnlijke formules en VBA-codes meer te onthouden.
- 30 dagen onbeperkte gratis proefperiode. 60 dagen geld-terug-garantie. Gratis upgrade en ondersteuning gedurende 2 jaar.
Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)
- Een seconde om te schakelen tussen tientallen geopende documenten!
- Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt efficiënte tabbladen naar Office (inclusief Excel), net als Chrome, Firefox en New Internet Explorer.
