Generische Formel
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Zusammenfassung
Um einen Wert durch Abgleichen über mehrere Spalten hinweg zu suchen, können Sie eine Array-Formel verwenden, die auf MMULT, TRANSPOSE, COLUMN und INDEX basiert. In dem gezeigten Beispiel lautet die Formel in H4:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
Dabei ist "Namen" der benannte Bereich C4: E7 und "Gruppen" der benannte Bereich B4: B7. Die Formel gibt die Gruppe zurück, zu der jeder Name gehört.
Hinweis: Dies ist eine Array-Formel und muss mit Control Shift Enter eingegeben werden.
Erläuterung
Die von innen nach außen arbeitenden logischen Kriterien für diese Formel lauten:
--(names=G4)
Dabei ist names der benannte Bereich C4: E7. Dies erzeugt ein TRUE / FALSE-Ergebnis für jeden Wert in Daten, und das doppelte Negativ zwingt die TRUE FALSE-Werte auf 1 und 0, um ein Array wie das folgende zu erhalten:
(0,0,0;1,0,0;0,0,0;0,0,0)
Dieses Array besteht aus 4 Zeilen x 3 Spalten und entspricht der Struktur von "Namen".
Mit diesem Ausdruck wird ein zweites Array erstellt:
TRANSPOSE(COLUMN(names)^0))
Mit der Funktion COLUMN wird ein numerisches Array mit 3 Spalten und 1 Zeile erstellt. TRANSPOSE konvertiert dieses Array in 1 Spalte und 3 Zeilen. Wenn Sie auf die Potenz Null setzen, werden einfach alle Zahlen im Array in 1 konvertiert. Die MMULT-Funktion wird dann verwendet, um eine Matrixmultiplikation durchzuführen:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
und das Ergebnis geht in die MATCH-Funktion als Array mit 1 als Suchwert:
MATCH(1,(0;1;0;0),0)
Die MATCH-Funktion gibt die Position der ersten Übereinstimmung zurück, die der Zeile der ersten übereinstimmenden Zeile entspricht, die die angegebenen Kriterien erfüllt. Dies wird als Zeilennummer in INDEX mit dem benannten Bereich "groups" als Array eingespeist:
=INDEX(groups,2)
Schließlich gibt INDEX "Bär" zurück, die Gruppe, zu der Adam gehört.
Literal enthält Kriterien
Um nach bestimmten Textwerten anstelle einer exakten Übereinstimmung zu suchen, können Sie die Funktionen ISNUMBER und SEARCH zusammen verwenden. Zum Abgleichen von Zellen, die "Apfel" enthalten, können Sie beispielsweise Folgendes verwenden:
=ISNUMBER(SEARCH("apple",data))
Diese Formel wird hier erklärt.
