INDEX en MATCH over meerdere kolommen
Om een waarde op te zoeken door meerdere kolommen te matchen, wordt een matrixformule gebaseerd op de INDEX en MATCH functies die MMULT, TRANSPONEREN en COLUMN zal je een plezier doen.
Hoe een waarde opzoeken door meerdere kolommen te matchen?
Om de . in te vullen overeenkomstige klas van elke student zoals de bovenstaande tabel laat zien, waarbij de informatie wordt weergegeven in meerdere kolommen, kunt u eerst de truc van de MMULT-, TRANSPOSE- en COLUMN-functie gebruiken om een matrixarray te produceren. Vervolgens geeft de MATCH-functie u de positie van uw opzoekwaarde, die naar INDEX wordt gevoerd om de waarde op te halen waarnaar u in de array zoekt.
Algemene syntaxis
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Opmerking: dit is een matrixformule waarvoor u moet invoeren met Ctrl + Shift + Enter.
- return_range: Het bereik waarvan u wilt dat de formule de klasinformatie retourneert. Hier verwijst naar het klassenbereik.
- opzoekwaarde: De waarde die de formule heeft gebruikt om de bijbehorende klasse-informatie te vinden. Hier verwijst naar de opgegeven naam.
- lookup_array: Het bereik van cellen waar de opzoekwaarde wordt vermeld; Het bereik met de waarden om te vergelijken met de opzoekwaarde. Hier verwijst naar het naambereik.
- match_type 0: Dwingt MATCH om de eerste waarde te vinden die exact gelijk is aan de opzoekwaarde.
Om het vinden klasse van Jimmy, kopieer of voer de onderstaande formule in de cel H5 in en druk op Ctrl + Shift + Enter om het resultaat te krijgen:
=INDEX($ B $ 5: $ B $ 7,(WEDSTRIJD(1,MMULT(--($ C $ 5: $ E $ 7=G5),TRANSPOSEREN(KOLOM($ C $ 5: $ E $ 7)^0)),0)))
√ 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. Merk op dat u geen dollartekens moet toevoegen aan de celverwijzing die de opzoekwaarde vertegenwoordigt, omdat u wilt dat deze relatief is wanneer u deze naar andere cellen kopieert. Nadat u de formule hebt ingevoerd, sleept u de vulgreep naar beneden om de formule toe te passen op de onderstaande cellen.
Verklaring van de formule
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- --($C$5:$E$7=G5): Dit segment controleert elke waarde in het bereik $ C $ 5: $ E $ 7 als ze gelijk zijn aan de waarde in de cel G5, en genereert een TRUE en FALSE array als volgt:
{WAAR, ONWAAR, ONWAAR; ONWAAR, ONWAAR, ONWAAR; ONWAAR, ONWAAR, ONWAAR}.
De dubbele ontkenning converteert dan de TRUE's en FALSE's naar 1s en 0s om een array als deze op te leveren:
{1,0,0; 0,0,0; 0,0,0}. - KOLOM($C$5:$E$7): De functie COLUMN retourneert de kolomnummers voor het bereik $ C $ 5: $ E $ 7 in een array als deze: 3,4,5 {}.
- TRANSPEREN(KOLOM($C$5:$E$7)^0) = TRANSPEREN(3,4,5 {}^0): Nadat de macht naar 0 is verhoogd, worden alle getallen in de array {3,4,5} geconverteerd naar 1: {1,1,1}. De functie TRANSPOSE converteert vervolgens de kolomarray naar een rijarray als volgt: {1; 1; 1}.
- MULT(--($C$5:$E$7=G5),TRANSPEREN(KOLOM($C$5:$E$7)^0)) = MULT({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): De functie MMULT retourneert het matrixproduct van de twee arrays als volgt: {1; 0; 0}.
- VERGELIJKEN(1,MULT(--($C$5:$E$7=G5),TRANSPEREN(KOLOM($C$5:$E$7)^0)), 0) = VERGELIJKEN(1,{1; 0; 0}, 0): Het match_type 0 dwingt de MATCH-functie om de positie van de eerste match van . te retourneren 1 in de reeks {1; 0; 0}Dit is 1.
- INHOUDSOPGAVE($ B $ 5: $ B $ 7,(VERGELIJKEN(1,MULT(--($C$5:$E$7=G5),TRANSPEREN(KOLOM($C$5:$E$7)^0)), 0))) = INDEX($ B $ 5: $ B $ 7,1): De INDEX-functie retourneert de 1st waarde in het klassebereik $ B $ 5: $ B $ 7Dit is A.
Om eenvoudig een waarde op te zoeken door meerdere kolommen te matchen, kunt u ook onze professionele Excel-invoegtoepassing gebruiken Kutools voor Excel. Bekijk hier de instructie om de missie te volbrengen.
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 Excel MMULT-functie retourneert het matrixproduct van twee arrays. Het arrayresultaat heeft hetzelfde aantal rijen als array1 en hetzelfde aantal kolommen als array2.
De Excel TRANSPOSE-functie roteert de oriëntatie van een bereik of matrix. Het kan bijvoorbeeld een tabel roteren die horizontaal in rijen is gerangschikt naar verticaal in kolommen of omgekeerd.
De functie KOLOM retourneert het aantal kolommen waarin de formule wordt weergegeven of retourneert het kolomnummer van de opgegeven verwijzing. Formule =COLUMN(BD) retourneert bijvoorbeeld 56.
Verwante formules
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.
Opzoeken in twee richtingen met INDEX en MATCH
Om iets te zoeken in zowel rijen als kolommen in Excel, of we zeggen om een waarde op te zoeken op de kruising van de specifieke rij en kolom, kunnen we de hulp van INDEX- en MATCH-functies gebruiken.
Zoek de meest overeenkomende waarde op met meerdere criteria
In sommige gevallen moet u mogelijk de dichtstbijzijnde of geschatte overeenkomstwaarde opzoeken op basis van meer dan één criterium. Met de combinatie van INDEX-, MATCH- en IF-functies kunt u het snel voor elkaar krijgen in Excel.
De beste tools voor kantoorproductiviteit
Kutools for Excel - Helpt u zich te onderscheiden van de menigte
Wilt u uw dagelijkse werkzaamheden snel en perfect uitvoeren? Kutools voor Excel brengt
- Ontworpen voor
1500werkscenario's, helpt u bij het oplossen80%Excel-problemen.
- 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-dag onbeperkt gratis proefperiode. 60 dagen geld-terug-garantie. Gratis upgrade en ondersteuning voor 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.
