Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

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.

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

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}.
  • MATCH(1,MULT(--($C$5:$E$7=G5),TRANSPEREN(KOLOM($C$5:$E$7)^0)), 0) = MATCH(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

Wilt u uw dagelijkse werkzaamheden snel en perfect afronden? Kutools for Excel biedt 300 krachtige geavanceerde functies (combineer werkmappen, som op kleur, celinhoud splitsen, datum converteren, enzovoort ...) en bespaar 80% tijd voor u.

  • 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.
Lint van Excel (met Kutools voor Excel geïnstalleerd)

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.
Schermopname van Excel (met Office-tabblad geïnstalleerd)
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.