INDEX en VERGELIJKEN over meerdere kolommen

Om een waarde op te zoeken door meerdere kolommen te vergelijken, gebruik je een matrixformule op basis van de INDEX- en VERGELIJKEN-functies, waarin ook MMULT, TRANSPONEREN en KOLOM zijn opgenomen. Deze combinatie helpt je enorm.

Hoe zoekt u een waarde op door over meerdere kolommen te vergelijken?

Om de bijbehorende klas van elke student in te vullen, zoals weergegeven in de bovenstaande tabel — waarbij de informatie over meerdere kolommen is verspreid — past u eerst een slimme truc toe met de functies MMULT, TRANSPONEREN en KOLOM om een matrix te genereren. Vervolgens geeft de VERGELIJKEN-functie u de positie van uw opzoekwaarde, die wordt doorgegeven aan INDEX om de gewenste waarde uit de matrix op te halen.

Algemene syntaxis

=INDEX()return_range,(MATCH(1,MMULT(--())))lookup_array=lookup_value),TRANSPOSE(COLUMN()lookup_array)^0)),0)))

√ Opmerking: Dit is een matrixformule die u moet invoeren met Ctrl+Shift+Enter.

Om de klas van Jimmyte vinden, kopieert of typt u de onderstaande formule in cel H5 en drukt u op Ctrl+Shift+Enterom het resultaat te verkrijgen:

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

√ Opmerking: De dollartekens ($) hierboven geven absolute verwijzingen aan, wat betekent dat de bereiken voor naam en klas in de formule niet veranderen wanneer u de formule naar andere cellen verplaatst of kopieert. Let op: u mag geen dollartekens toevoegen aan de celverwijzing die de opzoekwaarde vertegenwoordigt, omdat u wilt dat deze relatief blijft wanneer u deze naar andere cellen kopieert. Nadat u de formule hebt ingevoerd, sleept u de vulgreep omlaag om de formule toe te passen op de cellen eronder.

Uitleg 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 gedeelte controleert elke waarde in het bereik $C$5:$E$7 op gelijkheid met de waarde in cel G5 en genereert een WAAR/ONWAAR-matrix als volgt:
  {WAAR,ONWAAR,ONWAAR;ONWAAR,ONWAAR,ONWAAR;ONWAAR,ONWAAR,ONWAAR}.
  De dubbele mintekens zetten vervolgens de WAAR- en ONWAAR-waarden om in 1-en en 0-en, resulterend in een matrix als deze:
  {1,0,0;0,0,0;0,0,0}.
• KOLOM($C$5:$E$7): De functie KOLOM retourneert de kolomnummers van het bereik $C$5:$E$7 als een matrix: {3,4,5}.
• TRANSPONEREN()KOLOM($C$5:$E$7)^0)=TRANSPONEREN(){3,4,5}^0):Na machtsverheffing tot de macht 0 worden alle getallen in de matrix {3,4,5} omgezet naar 1: {1,1,1}. De functie TRANSPONEREN zet deze kolommatrix vervolgens om in een rijmatrix als volgt:{1;1;1}.
• MMULT()--($C$5:$E$7=G5),TRANSPONEREN()KOLOM($C$5:$E$7)^0))=MMULT(){1,0,0;0,0,0;0,0,0},{1;1;1}):De functie MMULT retourneert het matrixproduct van de twee matrices als volgt: {1;0;0}.
• VERGELIJKEN(1,)MMULT()--($C$5:$E$7=G5),TRANSPONEREN()KOLOM($C$5:$E$7)^0)),0)=VERGELIJKEN(1,){1;0;0},0):Met match_type 0 dwingt de VERGELIJKEN-functie de positie af van de eerste overeenkomst van 1 in de matrix {1;0;0}, wat 1 is.
• INDEX()$B$5:$B$7,(VERGELIJKEN(1,))MMULT()--($C$5:$E$7=G5),TRANSPONEREN()KOLOM($C$5:$E$7)^0)),0))) = INDEX($B$5:$B$7De INDEX-functie retourneert de 1e waarde in het klassebereik $B$5:$B$7, wat A is.

Om gemakkelijk een waarde op te zoeken door over meerdere kolommen te vergelijken, kunt u ook onze professionele Excel-add-in gebruiken Kutools voor Excel.Bekijk hier de instructies om de taak uit te voeren.

Gerelateerde functies

Gerelateerde formules