INDEX und MATCH über mehrere Spalten

Um einen Wert durch Übereinstimmung über mehrere Spalten zu finden, kann eine Array-Formel, die auf den Funktionen INDEX und MATCH basiert und MMULT, TRANSPOSE, und COLUMN verwendet, Ihnen helfen.

Wie kann man einen Wert durch Übereinstimmung über mehrere Spalten finden?

Um die entsprechende Klasse jedes Schülers wie in der obigen Tabelle gezeigt auszufüllen, wo die Informationen über mehrere Spalten verteilt sind, können Sie zunächst den Trick mit den Funktionen MMULT, TRANSPOSE und COLUMN verwenden, um ein Matrix-Array zu erstellen. Dann gibt die MATCH-Funktion die Position Ihres Suchwerts zurück, die an INDEX weitergegeben wird, um den gesuchten Wert im Array abzurufen.

Allgemeine Syntax

=INDEX(Rückgabebereich,(MATCH(1,MMULT(--(Suchbereich=Suchwert),TRANSPOSE(COLUMN(Suchbereich)^0)),0)))

√ Hinweis: Dies ist eine Array-Formel, die Sie mit Strg + Umschalt + Eingabe eingeben müssen.

Um die Klasse von Jimmy zu finden, kopieren Sie bitte die folgende Formel in Zelle H5 oder geben Sie sie ein, und drücken Sie Strg + Umschalt + Eingabe, um das Ergebnis zu erhalten:

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

√ Hinweis: Die Dollarzeichen ($) oben zeigen absolute Bezüge an, was bedeutet, dass sich die Namen- und Klassenbereiche in der Formel nicht ändern, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Beachten Sie, dass Sie keine Dollarzeichen zu der Zellreferenz hinzufügen sollten, die den Suchwert darstellt, da Sie möchten, dass er relativ bleibt, wenn Sie ihn in andere Zellen kopieren. Nachdem Sie die Formel eingegeben haben, ziehen Sie den Ausfüllknauf nach unten, um die Formel auf die darunter liegenden Zellen anzuwenden.

Erklärung der Formel

=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): Dieser Abschnitt überprüft jeden Wert im Bereich $C$5:$E$7 ob sie gleich dem Wert in Zelle G5 sind, und generiert ein WAHR- und FALSCH-Array wie folgt:
  {TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
  Das doppelte Negative wandelt dann die WAHR- und FALSCH-Werte in 1en und 0en um, um ein Array wie dieses zu erzeugen:
  {1,0,0;0,0,0;0,0,0}.
• COLUMN($C$5:$E$7): Die COLUMN-Funktion gibt die Spaltennummern für den Bereich $C$5:$E$7 in einem Array wie diesem zurück: {3,4,5}.
• TRANSPOSE(TRANSPOSE(COLUMN($C$5:$E$7)^0)^0) = TRANSPOSE(TRANSPOSE({3,4,5}^0)^0): Nachdem die Potenz auf 0 erhöht wurde, werden alle Zahlen im Array {3,4,5} in 1 umgewandelt: {1,1,1}. Die TRANSPOSE-Funktion konvertiert dann das Spaltenarray in ein Zeilenarray wie folgt: {1;1;1}.
• MMULT(MMULT(--($C$5:$E$7=G5),,TRANSPOSE(COLUMN($C$5:$E$7)^0))) = MMULT(MMULT({1,0,0;0,0,0;0,0,0},,{1;1;1})): Die MMULT-Funktion gibt das Matrixprodukt der beiden Arrays wie folgt zurück: {1;0;0}.
• MATCH(1,MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0),0) = MATCH(1,MATCH(1,{1;0;0},0),0): Der Übereinstimmungstyp 0 zwingt die MATCH-Funktion, die Position des ersten Treffers von 1 im Array {1;0;0} zurückzugeben, welches 1 ist.
• INDEX($B$5:$B$7$B$5:$B$7,,,(MATCH(1,MMULT(--($C$5:$E$7=G5),,,TRANSPOSE(COLUMN($C$5:$E$7)^0)),0))) = INDEX($B$5:$B$7$B$5:$B$7,,,1): Die INDEX-Funktion gibt den ersten Wert im Klassenbereich $B$5:$B$7 zurück, welcher A ist.

Um ganz einfach einen Wert durch Übereinstimmung über mehrere Spalten zu finden, können Sie auch unser professionelles Excel-Add-in Kutools für Excel verwenden. Sehen Sie sich hier die Anweisungen an, um die Aufgabe zu erledigen.

Verwandte Funktionen

Verwandte Formeln