Ga naar hoofdinhoud

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.

index komt overeen met meerdere kolommen 1

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.

  • retour_bereik: 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.

index komt overeen met meerdere kolommen 2

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

Excel INDEX-functie

De Excel INDEX-functie retourneert de weergegeven waarde op basis van een bepaalde positie uit een bereik of een matrix.

Excel MATCH-functie

De Excel MATCH-functie zoekt naar een specifieke waarde in een celbereik en retourneert de relatieve positie van de waarde.

Excel MMULT-functie

De Excel MMULT-functie retourneert het matrixproduct van twee arrays. Het arrayresultaat heeft hetzelfde aantal rijen als array1 en hetzelfde aantal kolommen als array2.

Excel TRANSPOSE-functie

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.

Excel COLUMN-functie

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

Populaire functies: Zoek, markeer of identificeer duplicaten  |  Verwijder lege rijen  |  Combineer kolommen of cellen zonder gegevens te verliezen  |  Ronde zonder formule ...
Super VLookup: Meerdere criteria  |  Meerdere waarde  |  Over meerdere vellen  |  Fuzzy opzoeken...
Gev. Keuzelijst: Gemakkelijke vervolgkeuzelijst  |  Afhankelijke vervolgkeuzelijst  |  Multi-select vervolgkeuzelijst...
Kolom Beheerder: Voeg een specifiek aantal kolommen toe  |  Kolommen verplaatsen  |  Schakel de zichtbaarheidsstatus van verborgen kolommen in  Vergelijk Kolommen met Selecteer dezelfde en verschillende cellen ...
Uitgelichte functies: Raster focus  |  Ontwerpweergave  |  Grote formulebalk  |  Werkmap- en bladbeheer | resource Library (Auto-tekst)  |  Datumkiezer  |  Combineer werkbladen  |  Cellen coderen/decoderen  |  Stuur e-mails per lijst  |  Super filter  |  Speciaal filter (filter vet/cursief/doorhalen...) ...
Top 15 gereedschapsets12 Tekst Tools (toe te voegen tekst, Tekens verwijderen ...)  |  50+ tabel Types (Gantt Chart ...)  |  40+ Praktisch Formules (Bereken leeftijd op basis van verjaardag ...)  |  19 Invoeging Tools (QR-code invoegen, Afbeelding invoegen vanaf pad ...)  |  12 Camper ombouw Tools (Getallen naar woorden, Currency Conversion ...)  |  7 Samenvoegen en splitsen Tools (Geavanceerd Combineer rijen, Excel-cellen splitsen ...)  |  ... en meer

Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...


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, Edge en Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations