Ersten übereinstimmenden Wert in einer Zelle anhand einer Liste abrufen

Angenommen, Sie verfügen über eine Liste von Schlüsselwörtern und möchten das erste Schlüsselwort ermitteln, das in einer bestimmten Zelle vorkommt – obwohl diese Zelle noch weitere Werte enthält –, benötigen Sie eine INDEX-und MATCH-Formel unter Verwendung der AGGREGATE-und SEARCH-Funktionen.

Wie ruft man den ersten übereinstimmenden Wert aus einer Liste in einer Zelle ab?

Um das erste übereinstimmende Schlüsselwort in einer Zelle anhand der Schlüsselwortliste, wie in der obigen Tabelle gezeigt, abzurufen, müssen Sie eine Teilmengenübereinstimmung („enthält“) statt einer exakten Übereinstimmung durchführen. Verwenden Sie dazu die SEARCH-Funktion, um die Positionen der in der Zelle gefundenen Schlüsselwörter als Zahlen an die AGGREGATE-Funktion zu übergeben. Anschließend ermittelt AGGREGATE die kleinste Zahl, indem function_num auf 15 und das ref2-Argument auf 1 gesetzt wird. Danach nutzen Sie MATCH, um den ersten kleinsten Wert zu lokalisieren, und übergeben die Positionsnummer an INDEX, um den entsprechenden Wert 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 Arrayformel, die mit Strg+Umschalttaste+Eingabetasteeingegeben werden muss.

Um das erste übereinstimmende Schlüsselwort, das in Zelle B5 gegenüber der Spalte „Schlüsselwörter“ erscheint, abzurufen, kopieren oder geben Sie die folgende Formel in Zelle C5 ein und drücken Sie Strg+Umschalttaste+Eingabetaste, 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 kennzeichnen absolute Bezüge, was bedeutet, dass sich der keyword_rngin der Formel nicht ändert, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Für die lookup_cellwurden hingegen keine Dollarzeichen verwendet, da dieser Bezug dynamisch sein soll. Ziehen Sie nach Eingabe der Formel den Ausfüllknauf nach unten, um die Formel auf die darunterliegenden Zellen anzuwenden.

Erläuterung 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 als Zahl zurück, sobald es gefunden wird, und liefert den Fehler #VALUE!, wenn kein Treffer vorliegt. Das Ergebnis erscheint in einem Array wie folgt: {15;11;#VALUE!}.
• AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1)=AGGREGATE(15,6,){15;11;#VALUE!},1):Die AGGREGATE-Funktion mit einer function_num von 15 und einer Option von 6 gibt den kleinsten Wert im Array entsprechend dem ref2-Argument 1 zurück und ignoriert dabei Fehlerwerte. Somit liefert der Ausschnitt 11.
• MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)=MATCH()11,{15;11;#VALUE!},0): Die match_type-Option 0 zwingt die MATCH-Funktion, eine exakte Übereinstimmung durchzuführen und gibt die Position von 11 im Array {15;11;#VALUE!} zurück. Die Funktion liefert daher 2.
• INDEX($E$5:$E$7,)MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)) = INDEX($E$5:$E$7,2):Die INDEX-Funktion gibt dann den 2. Wert im Bereich $E$5:$E$7 zurück, nämlich bbb.

Hinweis

• Wenn keine Schlüsselwörter in einer Zelle vorhanden sind, wird ein #NUM!-Fehler zurückgegeben.
• Die Formel berücksichtigt keine Groß-/Kleinschreibung. Um eine groß-/kleinschreibungsabhängige Übereinstimmung durchzuführen, ersetzen Sie einfach die SEARCH-Funktion durch FIND.

Verwandte Funktionen

Verwandte Formeln