INDEX und VERGLEICH über mehrere Spalten hinweg

Um einen Wert durch Abgleich über mehrere Spalten hinweg zu suchen, verwenden Sie eine Matrixformel auf Basis der INDEX- und VERGLEICH-Funktionen, die zudem MMULT, MTRANS und SPALTE einsetzt.

Wie sucht man einen Wert durch Abgleich über mehrere Spalten hinweg?

Um die jeweilige Klasse jedes Schülers gemäß der oben gezeigten Tabelle auszufüllen – wobei die Informationen über mehrere Spalten verteilt sind –, verwenden Sie zunächst die Kombination der Funktionen MMULT, MTRANS und SPALTE, um ein Matrixarray zu erstellen. Anschließend ermittelt die VERGLEICH-Funktion die Position Ihres Suchwerts und übergibt diese an INDEX, um den gesuchten Wert aus dem Array abzurufen.

Allgemeine Syntax

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

√ Hinweis: Dies ist eine Matrixformel, die mit Strg+Umschalt+Eingabeeingegeben werden muss.

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

=INDEX()$B$5:$B$7,(VERGLEICH(1,MMULT(--())))$C$5:$E$7=G5),MTRANS(SPALTE()$C$5:$E$7)^0)),0)))

√ Hinweis: Die Dollarzeichen ($) oben kennzeichnen absolute Bezüge, was bedeutet, dass sich die Bereiche für Name und Klasse in der Formel nicht ändern, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Beachten Sie, dass Sie keine Dollarzeichen zu dem Zellbezug hinzufügen sollten, der den Suchwert darstellt, da dieser relativ bleiben soll, wenn Sie ihn in andere Zellen kopieren. Ziehen Sie nach der Eingabe der Formel am Ausfüllkästchen nach unten, um die Formel auf die darunterliegenden Zellen anzuwenden.

Erläuterung 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 Ausdruck prüft jeden Wert im Bereich $C$5:$E$7 darauf, ob er dem Wert in Zelle G5 entspricht, und erzeugt ein WAHR/FALSCH-Array wie folgt:
  {WAHR,FALSCH,FALSCH;FALSCH,FALSCH,FALSCH;FALSCH,FALSCH,FALSCH}.
  Das doppelte Minuszeichen wandelt anschließend die WAHR- und FALSCH-Werte in 1 und 0 um und liefert ein Array wie folgt:
  {1,0,0;0,0,0;0,0,0}.
• SPALTE($C$5:$E$7): Die SPALTE-Funktion gibt die Spaltennummern für den Bereich $C$5:$E$7 in einem Array wie folgt zurück: {3,4,5}.
• MTRANS()SPALTE($C$5:$E$7)^0)=MTRANS(){3,4,5}^0):Nach Anwendung der Potenz 0 werden alle Zahlen im Array {3,4,5} in 1 umgewandelt: {1,1,1}. Anschließend wandelt die MTRANS-Funktion das Spaltenarray in ein Zeilenarray um – und zwar wie folgt:{1;1;1}.
• MMULT()--($C$5:$E$7=G5),MTRANS()SPALTE($C$5:$E$7)^0))=MMULT(){1,0,0;0,0,0;0,0,0},{1;1;1}): Die MMULT-Funktion liefert das Matrixprodukt der beiden Arrays wie folgt zurück: {1;0;0}.
• VERGLEICH(1,)MMULT()--($C$5:$E$7=G5),MTRANS()SPALTE($C$5:$E$7)^0)),0)=VERGLEICH(1,){1;0;0},0): Der Vergleichstyp 0 zwingt die VERGLEICH-Funktion, die Position des ersten Vorkommens von 1 im Array {1;0;0} zurückzugeben, was 1 ergibt.
• INDEX()$B$5:$B$7,(VERGLEICH(1,))MMULT()--($C$5:$E$7=G5),MTRANS()SPALTE($C$5:$E$7)^0)),0))) = INDEX($B$5:$B$7 Die INDEX-Funktion gibt den 1. Wert im Klassenbereich $B$5:$B$7 zurück, was A ergibt.

Um einen Wert einfach durch Übereinstimmung über mehrere Spalten hinweg nachzuschlagen, können Sie auch unser professionelles Excel-Add-In verwenden Kutools für Excel.Lesen Sie hier die Anleitung, um diese Aufgabe zu erfüllen.

Verwandte Funktionen

Verwandte Formeln