Generische Formel
=LOOKUP(2,1/SEARCH(things,A1),things)
Zusammenfassung
Um eine Zelle auf eines von mehreren Dingen zu überprüfen und die letzte in der Liste gefundene Übereinstimmung zurückzugeben, können Sie eine Formel verwenden, die auf den Funktionen LOOKUP und SEARCH basiert. Bei mehreren gefundenen Übereinstimmungen gibt die Formel die letzte Übereinstimmung aus der Liste der "Dinge" zurück.
Im gezeigten Beispiel lautet die Formel in C5:
=LOOKUP(2,1/SEARCH(things,B5),things)
Erläuterung
Kontext: Sie haben eine Liste von Dingen im benannten Bereich "Dinge" (E5: E8) und möchten die Zellen in Spalte B überprüfen, um festzustellen, ob sie diese Dinge enthalten. Wenn ja, möchten Sie das letzte Element von "Dingen" zurückgeben, die gefunden wurden.
In dieser Formel wird die SEARCH-Funktion verwendet, um Zellen in Spalte B wie folgt zu durchsuchen:
SEARCH(things,B5)
Wenn SEARCH eine Übereinstimmung findet, wird die Position der Übereinstimmung in der durchsuchten Zelle zurückgegeben. Wenn die Suche keine Übereinstimmung finden kann, wird der Fehler #VALUE zurückgegeben. Da wir SEARH mehr als eine Sache geben, nach der gesucht werden muss, wird mehr als ein Ergebnis zurückgegeben. In dem gezeigten Beispiel gibt SEARCH ein Array von Ergebnissen wie folgt zurück:
(8;24;#VALUE!;#VALUE!)
Dieses Array wird dann als Teiler für die Zahl 1 verwendet. Das Ergebnis ist ein Array, das aus Fehlern und Dezimalwerten besteht. Die Fehler stellen Dinge dar, die nicht gefunden wurden, und die Dezimalwerte repräsentieren Dinge, die gefunden wurden. Im gezeigten Beispiel sieht das Array folgendermaßen aus:
(0.125;0.0416666666666667;#VALUE!;#VALUE!)
Dieses Array dient als "lookup_vector" für die LOOKUP-Funktion. Der Suchwert wird als Nummer 2 angegeben, und der Ergebnisvektor ist der benannte Bereich "Dinge". Das ist der kluge Teil.
Die Formel ist so aufgebaut, dass der Suchvektor niemals einen Wert größer als 1 enthält, während der Suchwert 2 ist. Dies bedeutet, dass der Suchwert niemals gefunden wird. In diesem Fall stimmt LOOKUP mit dem letzten im Array gefundenen numerischen Wert überein, der dem letzten von SEARCH gefundenen "Ding" entspricht.
Schließlich gibt LOOKUP unter Verwendung des angegebenen Bereichs "Dinge" als Ergebnisvektor das zuletzt gefundene Objekt zurück.
Mit fest codierten Werten
Die Verwendung eines Bereichs wie "Dinge" erleichtert das Ändern der Liste der Suchbegriffe (und das Hinzufügen weiterer Suchbegriffe), ist jedoch nicht erforderlich. Sie können Werte auch wie folgt direkt in die Formel fest codieren:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
