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.

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

Verwante formules

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.

Lees meer DOWNLOAD

---

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.

Lees meer DOWNLOAD