Generische Formel
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Zusammenfassung
Um eine Zelle auf eines von mehreren Dingen zu überprüfen und die erste in der Liste gefundene Übereinstimmung zurückzugeben, können Sie eine INDEX / MATCH-Formel verwenden, die SEARCH oder FIND verwendet, um eine Übereinstimmung zu finden. Im gezeigten Beispiel lautet die Formel in C5:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
wobei "Dinge" der benannte Bereich E5: E9 ist.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabetaste eingegeben werden.
Erläuterung
In diesem Beispiel haben wir eine Liste von Farben in einem benannten Bereich namens "Dinge" (E5: E9). Wir möchten den Text in Spalte B überprüfen, um festzustellen, ob er eine dieser Farben enthält. In diesem Fall möchten wir den Namen der ersten gefundenen Farbe zurückgeben.
Diese Formel arbeitet von innen nach außen und verwendet die ISNUMBER-Funktion und die SEARCH-Funktion, um den Text in B5 nach jeder Farbe zu durchsuchen, die in "Dingen" wie diesen aufgeführt ist:
ISNUMBER(SEARCH(things,B5)
Dieser Ausdruck basiert auf einer Formel (hier ausführlich erläutert), die eine Zelle auf einen einzelnen Teilstring überprüft. Wenn die Zelle den Teilstring enthält, gibt der Ausdruck TRUE zurück. Wenn nicht, gibt der Ausdruck FALSE zurück.
Wenn wir dieser SUCHE eine Liste von Dingen geben (anstelle von einer Sache), erhalten wir eine Reihe von Ergebnissen zurück. Jede gefundene Farbe erzeugt eine numerische Position, und nicht gefundene Farben erzeugen einen Fehler:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Die ISNUMBER-Funktion konvertiert dann die Ergebnisse in TRUE / FALSE-Werte. Jede Zahl wird WAHR und jeder Fehler (nicht gefunden) wird FALSCH. Das Ergebnis ist ein Array wie folgt:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Dieses Array wird als Array-Argument an die MATCH-Funktion zurückgegeben. Der Suchwert ist TRUE und der Übereinstimmungstyp wird auf Null gesetzt, um eine genaue Übereinstimmung zu erzwingen. Wenn eine passende Farbe vorhanden ist, gibt MATCH die Position zurück, die zuerst als WAHR gefunden wurde. Dieser Wert wird als Zeilennummer in die INDEX-Funktion eingegeben, wobei der benannte Bereich "things" als Array bereitgestellt wird. Wenn mindestens eine Übereinstimmung vorliegt, gibt INDEX die Farbe an dieser Position zurück. Wenn keine Übereinstimmung gefunden wird, gibt diese Formel den Fehler # N / A zurück.
Mit fest codierten Werten
Wenn Sie in diesem Beispiel keinen externen benannten Bereich wie "things" einrichten möchten, können Sie Werte wie folgt als "Array-Konstanten" in die Formel codieren:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Holen Sie sich das erste Match in der Zelle
Die Sprache hier ist ziemlich verwirrend, aber die obige Formel gibt die erste Übereinstimmung zurück, die in der Liste der zu suchenden Dinge gefunden wurde. Wenn Sie stattdessen die erste Übereinstimmung zurückgeben möchten, die in der zu testenden Zelle gefunden wurde, können Sie eine Formel wie die folgende ausprobieren:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
In dieser Version der Formel ist die MATCH-Funktion so eingerichtet, dass nach dem Ergebnis dieses Snippets gesucht wird:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
Hiermit wird die AGGREGATE-Funktion verwendet, um den Mindestwert für die von SEARCH zurückgegebenen Ergebnisse zu ermitteln. Wir brauchen hier AGGREGATE, da das eingehende Array wahrscheinlich Fehler enthält (von SEARCH zurückgegeben, wenn Dinge nicht gefunden werden), und wir brauchen eine Funktion, die diese Fehler ignoriert und uns trotzdem den minimalen numerischen Wert gibt.
Das Ergebnis von AGGREGATE wird zusammen mit demselben von SEARCH zurückgegebenen Array direkt als Suchwert an MATCH zurückgegeben. Das Endergebnis ist die erste Übereinstimmung in der Zelle, nicht die erste Übereinstimmung in der Liste der Dinge.
