INDEX en MATCH over meerdere kolommen

Om een waarde te zoeken door over meerdere kolommen te matchen, zal een arrayformule gebaseerd op de functies INDEX en MATCH die MMULT, TRANSPOSE, en KOLOM gebruikt u een dienst bewijzen.

Hoe zoek je een waarde door over meerdere kolommen te matchen?

Om de bijbehorende klasse van elke student in te vullen zoals in de bovenstaande tabel weergegeven, waarbij de informatie over meerdere kolommen is vermeld, kun je eerst de truc van de functies MMULT, TRANSPOSE en KOLOM gebruiken om een matrixarray te produceren. Vervolgens geeft de functie MATCH je de positie van je zoekwaarde, die wordt doorgegeven aan INDEX om de waarde die je zoekt in de array op te halen.

Algemene syntaxis

=INDEX(terugkeer_bereik,(MATCH(1,MMULT(--(zoek_array=zoek_waarde),TRANSPOSE(KOLOM(zoek_array)^0)),0)))

√ Opmerking: Dit is een arrayformule die vereist dat je deze invoert met Ctrl + Shift + Enter.

Om de klas van Jimmy te vinden, kopieer of voer de onderstaande formule in cel H5 in en druk op Ctrl + Shift + Enter om het resultaat te krijgen:

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(KOLOM($C$5:$E$7)^0)),0)))

√ Opmerking: De dollartekens ($) hierboven geven absolute verwijzingen aan, wat betekent dat de naam- en klassenbereiken in de formule niet veranderen wanneer je de formule naar andere cellen verplaatst of kopieert. Let erop dat je geen dollartekens moet toevoegen aan de celverwijzing die de zoekwaarde vertegenwoordigt, omdat je wilt dat deze relatief blijft wanneer je deze naar andere cellen kopieert. Nadat je de formule hebt ingevoerd, sleep je de vulgreep naar beneden om de formule op de onderliggende cellen toe te passen.

Uitleg van de formule

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(KOLOM($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 cel G5, en genereert een WAAR en ONWAAR array zoals deze:
  {TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
  De dubbele negatie zal vervolgens de WAARs en ONWAARs converteren naar 1-en en 0-en om een array als deze te produceren:
  {1,0,0;0,0,0;0,0,0}.
• KOLOM($C$5:$E$7): De functie KOLOM retourneert de kolomnummers voor het bereik $C$5:$E$7 in een array zoals deze: {3,4,5}.
• TRANSPOSE(TRANSPOSE(KOLOM($C$5:$E$7)^0)^0) = TRANSPOSE(TRANSPOSE({3,4,5}^0)^0): Na het verhogen tot de macht 0, 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 zoals deze: {1;1;1}.
• MMULT(MMULT(--($C$5:$E$7=G5),,TRANSPOSE(KOLOM($C$5:$E$7)^0))) = MMULT(MMULT({1,0,0;0,0,0;0,0,0},,{1;1;1})): De functie MMULT retourneert het matrixproduct van de twee arrays zoals deze: {1;0;0}.
• MATCH(1,MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(KOLOM($C$5:$E$7)^0)),0),0) = MATCH(1,MATCH(1,{1;0;0},0),0): De match_type 0 dwingt de functie MATCH om de positie van de eerste match van 1 in de array {1;0;0} te retourneren, wat 1 is.
• INDEX($B$5:$B$7$B$5:$B$7,,,(MATCH(1,MMULT(--($C$5:$E$7=G5),,,TRANSPOSE(KOLOM($C$5:$E$7)^0)),0))) = INDEX($B$5:$B$7$B$5:$B$7,,,1): De functie INDEX retourneert de 1e waarde in het klassenbereik $B$5:$B$7, wat A is.

Om gemakkelijk een waarde te zoeken door over meerdere kolommen te matchen, kun je ook ons professionele Excel-invoegtoepassing Kutools For Excel gebruiken. Zie hier de instructies om de missie te voltooien.

Gerelateerde functies

Gerelateerde formules