Ersten übereinstimmenden Wert in einer Zelle im Vergleich zu einer Liste abrufen

Angenommen, Sie haben eine Liste von Schlüsselwörtern und möchten das erste Schlüsselwort abrufen, das in einer bestimmten Zelle erscheint, während die Zelle mehrere andere Werte enthält. Dazu müssen Sie eine INDEX- und MATCH-Formel mit Hilfe der AGGREGATE- und SEARCH-Funktionen verwenden.

Wie ruft man den ersten übereinstimmenden Wert in einer Zelle im Vergleich zu einer Liste ab?

Um das erste übereinstimmende Schlüsselwort in einer Zelle im Vergleich zur Schlüsselwortliste wie in der obigen Tabelle zu erhalten, bedeutet dies, dass wir eine Teilvergleich durchführen müssen, anstatt einen genauen Vergleich. Um dies zu tun, können Sie die SEARCH-Funktion verwenden, um die Positionen der Schlüsselwörter, die in der Zelle erscheinen, als numerische Werte in die AGGREGATE-Funktion einzufügen. Dann erhält AGGREGATE die kleinste Zahl, indem sein function_num auf 15 und das ref2-Argument auf 1 gesetzt wird. Verwenden Sie dann MATCH, um die erste kleinste Zahl zu lokalisieren und die Positionsnummer an INDEX weiterzuleiten, um den Wert an dieser Position abzurufen.

Allgemeine Syntax

=INDEX(keyword_rng;MATCH(AGGREGATE(15;6;SEARCH(keyword_rng;lookup_cell);1);SEARCH(keyword_rng;lookup_cell);0))

√ Hinweis: Dies ist eine Array-Formel, die erfordert, dass Sie sie mit Strg + Umschalt + Enter eingeben.

Um das erste übereinstimmende Schlüsselwort, das in Zelle B5 im Vergleich zur Schlüsselwortspalte erscheint, abzurufen, kopieren Sie bitte die folgende Formel oder geben Sie sie in Zelle C5 ein, und drücken Sie Strg + Umschalt + Enter, um das Ergebnis zu erhalten:

=INDEX($E$5:$E$7;MATCH(AGGREGATE(15;6;SEARCH($E$5:$E$7;B5);1);SEARCH($E$5:$E$7;B5);0))

√ Hinweis: Die Dollarzeichen ($) oben zeigen absolute Bezüge an, was bedeutet, dass sich der keyword_rng in der Formel nicht ändert, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Es wurden jedoch keine Dollarzeichen zum lookup_cell hinzugefügt, da es dynamisch sein soll. 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($E$5:$E$7;MATCH(AGGREGATE(15;6;SEARCH($E$5:$E$7;B5);1);SEARCH($E$5:$E$7;B5);0))

• SEARCH($E$5:$E$7;B5): Die SEARCH-Funktion gibt die Position jedes Schlüsselworts aus dem Bereich $E$5:$E$7 zurück, wenn es gefunden wird, und den Fehler #WERT!, wenn es nicht gefunden wird. Das Ergebnis wäre in einem Array wie diesem: {15;11;#WERT!}.
• AGGREGATE(15;6;SEARCH($E$5:$E$7;B5);1) = AGGREGATE(15;6;{15;11;#WERT!};1): Die AGGREGATE-Funktion mit einem function_num von 15 und einer Option von 6 gibt den kleinsten Wert im Array entsprechend dem ref2-Argument 1 zurück, wobei Fehlerwerte ignoriert werden. Daher würde dieses Snippet 11 zurückgeben.
• MATCH(AGGREGATE(15;6;SEARCH($E$5:$E$7;B5);1);SEARCH($E$5:$E$7;B5);0) = MATCH(11;{15;11;#WERT!};0): Der match_type 0 zwingt die MATCH-Funktion, eine genaue Übereinstimmung durchzuführen und die Position von 11 im Array {15;11;#WERT!} zurückzugeben. Daher gibt die Funktion 2 zurück.
• INDEX($E$5:$E$7;MATCH(AGGREGATE(15;6;SEARCH($E$5:$E$7;B5);1);SEARCH($E$5:$E$7;B5);0)) = INDEX($E$5:$E$7;2): Die INDEX-Funktion gibt dann den zweiten Wert im Bereich $E$5:$E$7 zurück, welcher bbb ist.

Anmerkung

• Wenn es keine Schlüsselwörter in einer Zelle gibt, wird ein #ZAHL!-Fehler zurückgegeben.
• Die Formel unterscheidet nicht zwischen Groß- und Kleinschreibung. Um einen Schreibweise-sensitiven Abgleich durchzuführen, können Sie einfach die SEARCH-Funktion durch FIND ersetzen.

Verwandte Funktionen

Verwandte Formeln