Generische Formel
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Zusammenfassung
Um eine Zelle auf eine von mehreren Zeichenfolgen zu testen und ein benutzerdefiniertes Ergebnis für die erste gefundene Übereinstimmung zurückzugeben, können Sie eine INDEX / MATCH-Formel verwenden, die auf der SEARCH-Funktion basiert. Im gezeigten Beispiel lautet die Formel in C5:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
Dabei werden Dinge (E5: E8) und Ergebnisse (F5: F8) als Bereiche bezeichnet.
Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabetaste eingegeben werden.
Erläuterung
Diese Formel verwendet zwei benannte Bereiche: Dinge und Ergebnisse . Wenn Sie diese Formel direkt portieren, müssen Sie benannte Bereiche mit denselben Namen verwenden (basierend auf Ihren Daten definiert). Wenn Sie keine benannten Bereiche verwenden möchten, verwenden Sie stattdessen absolute Referenzen.
Der Kern dieser Formel ist dieses Snippet:
ISNUMBER(SEARCH(things,B5)
Dies basiert auf einer anderen Formel (hier ausführlich erläutert), die eine Zelle auf einen einzelnen Teilstring überprüft. Wenn die Zelle den Teilstring enthält, gibt die Formel TRUE zurück. Wenn nicht, gibt die Formel FALSE zurück.
Weil wir die Suchfunktion mehr als eine Sache zu suchen, in den genannten Bereich geben Dinge , wird es uns mehr das ein Ergebnis in einem Array, das wie folgt aussieht:
(#VALUE!;9;#VALUE!;#VALUE!)
Zahlen stehen für Übereinstimmungen in Dingen , Fehler für Elemente, die nicht gefunden wurden.
Um das Array zu vereinfachen, verwenden wir die ISNUMBER-Funktion, um alle Elemente im Array in TRUE oder FALSE zu konvertieren. Jede gültige Zahl wird WAHR und jeder Fehler (dh eine Sache, die nicht gefunden wurde) wird FALSCH. Das Ergebnis ist ein Array wie folgt:
(FALSE;TRUE;FALSE;FALSE)
Dies geht in die MATCH-Funktion als lookup_array-Argument mit einem lookup_value von TRUE ein:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH gibt dann die Position des ersten gefundenen TRUE zurück, in diesem Fall 2.
Schließlich verwenden wir die Funktion INDEX ein Ergebnis aus den benannten Bereich abzurufen Ergebnissen in der gleichen Position:
=INDEX(results,2) // returns "found red"
Sie können den Ergebnisbereich mit den Werten anpassen, die in Ihrem Anwendungsfall sinnvoll sind.
Falsche Übereinstimmungen verhindern
Ein Problem bei diesem Ansatz mit dem ISNUMBER + SEARCH-Ansatz besteht darin, dass Sie möglicherweise falsche Übereinstimmungen aus Teilübereinstimmungen in längeren Wörtern erhalten. Wenn Sie beispielsweise versuchen, "dr" zuzuordnen, finden Sie möglicherweise auch "Andrea", "trank", "tropfen" usw., da "dr" in diesen Wörtern vorkommt. Dies geschieht, weil SEARCH automatisch eine Übereinstimmung vom Typ "enthält" durchführt.
Für eine schnelle Lösung können Sie Suchwörter in Leerzeichen (dh "dr" oder "dr") einschließen, um zu vermeiden, dass "dr" in einem anderen Wort gefunden wird. Dies schlägt jedoch fehl, wenn "dr" zuerst oder zuletzt in einer Zelle angezeigt wird.
Wenn Sie eine robustere Lösung benötigen, können Sie den Text zuerst in einer Hilfsspalte normalisieren und einen führenden und einen nachfolgenden Bereich hinzufügen. Verwenden Sie dann die Formel auf dieser Seite für den Text in der Hilfsspalte anstelle des Originaltextes.
