Erste Übereinstimmung finden, die nicht enthält
Um die Position des ersten Elements zu finden, das eine bestimmte Zeichenfolge nicht enthält, verwenden Sie eine Formel, die auf den Funktionen VERGLEICH, ISTZAHL und SUCHEN basiert.
Wie findet man die erste Übereinstimmung, die die Zeichenfolge „TOOL“ nicht enthält?
Um die Position des ersten Werts zu finden, der die Zeichenfolge „TOOL“ nicht enthält, kombinieren Sie die Funktionen SUCHEN und ISTZAHL, um ein WAHR/FALSCH-Array zu erstellen: WAHR steht dabei für Werte, die „TOOL“ enthalten, und FALSCH für solche, die dies nicht tun. Anschließend sucht VERGLEICH im Array nach dem ersten FALSCH-Wert und gibt die Position des ersten Elements zurück, das die Zeichenfolge „TOOL“ nicht enthält.
Allgemeine Syntax
=MATCH(FALSE,ISNUMBER(SEARCH()))lookup_value,range)),0)
√ Hinweis: Dies ist eine Matrixformel, für deren Eingabe Sie Strg+Umschalttaste+Eingabetastedrücken müssen.
- Suchwert: Die Zeichenfolge, die Sie angeben, um den ersten Wert zu finden, der sie **nicht** enthält. Die Zeichenfolge muss in doppelte Anführungszeichen eingeschlossen werden – andernfalls liefert das Ergebnis einen Fehler. Alternativ können Sie eine Zellreferenz verwenden, um das manuelle Hinzufügen von doppelten Anführungszeichen zu vermeiden.
- Bereich: Die Liste, aus der der erste Wert zurückgegeben werden soll, der den Suchwert nicht enthält.
- 0: Der Vergleichstyp 0 zwingt die Funktion VERGLEICH, eine exakte Übereinstimmung durchzuführen.
Um die Position des ersten Werts zu finden, der die Zeichenfolge „TOOL“ nicht enthält, kopieren oder geben Sie die folgende Formel in die Zelle E5 ein und drücken Sie Strg+Umschalttaste+Eingabetaste, um das Ergebnis zu erhalten:
=VERGLEICH(FALSCH;ISTZAHL(SUCHEN()))„TOOL";B5:B11));0)
Oder verwenden Sie Eine Zellreferenz, um die Formel dynamisch zu gestalten:
=VERGLEICH(FALSCH;ISTZAHL(SUCHEN()))E4;B5:B11));0)
Erläuterung der Formel
=MATCH(FALSE,())ISNUMBER()SEARCH(E4,B5:B11)),0)
- SUCHEN(E4;B5:B11): Die Funktion SUCHEN sucht nach der Zeichenfolge TOOL(Wert in)E4) in jedem Wert des Bereichs B5:B11 und gibt die Positionen des Anfangs der Zeichenfolge innerhalb dieser Werte als Array zurück: {3;3;4;#WERT!;1;3;#WERT!}.
- ISTZAHL()SUCHEN(E4;B5:B11))=ISTZAHL(){3;3;4;#WERT!;1;3;#WERT!}): Die Funktion ISTZAHL wandelt alle Zahlen im Array in WAHR-Werte und alle übrigen Werte in FALSCH-Werte um. Das Ergebnis lautet daher:{WAHR;WAHR;WAHR;FALSCH;WAHR;WAHR;FALSCH}.
- VERGLEICH(FALSCH;())ISTZAHL()SUCHEN(E4;B5:B11)),0) = VERGLEICH(FALSCH;(){WAHR;WAHR;WAHR;FALSCH;WAHR;WAHR;FALSCH},0): Der Vergleichstyp 0 zwingt die Funktion VERGLEICH, eine exakte Übereinstimmung durchzuführen. Die Funktion gibt dann die Position des ersten exakten FALSCH-Werts im Array zurück. Daher liefert die Funktion 4, da der erste FALSCH-Wert an der 4. Position steht.
Wissenswertes
- Die Formel unterscheidet nicht zwischen Groß- und Kleinschreibung. Wenn Sie eine groß-/kleinschreibungsabhängige Übereinstimmung durchführen möchten, lesen Sie bitte hier das entsprechende Tutorial.
- Um den Wert an der von VERGLEICH ermittelten Position abzurufen, fügen Sie einfach die Funktion INDEX hinzu.
Verwandte Funktionen
Die Excel-Funktion VERGLEICH sucht in einem Zellbereich nach einem bestimmten Wert und gibt dessen relative Position zurück.
In Excel hilft Ihnen die SUCHEN-Funktion, die Position eines bestimmten Zeichens oder Teilstrings innerhalb einer vorgegebenen Textzeichenfolge zu ermitteln – wie im folgenden Screenshot veranschaulicht. In diesem Tutorial erfahren Sie, wie Sie die SUCHEN-Funktion in Excel effektiv nutzen.
Verwandte Formeln
Erste teilweise Übereinstimmung mit Platzhaltern finden
Um die Position der ersten teilweisen Übereinstimmung zu ermitteln, bei der eine bestimmte Textzeichenfolge in einem Excel-Bereich enthalten ist, können Sie eine Formel mit Platzhalterzeichen verwenden – dem Sternchen (*) und dem Fragezeichen (?).
Erste Übereinstimmung finden, die nicht mit beginnt
Um die Position der ersten Übereinstimmung zu ermitteln, die nicht mit einer bestimmten Zeichenfolge beginnt, unterstützen Sie die Funktionen VERGLEICH, LINKS und WENN.
Um die Position des ersten Fehlers in einer Spalte oder Zeile zu ermitteln, unterstützen Sie die Funktionen VERGLEICH und ISTFEHLER.
Maximalwert in einem Bereich finden
In bestimmten Fällen müssen Sie die Position des Maximalwerts in einer Liste, Tabelle oder Zeile in Excel ermitteln. Dieses Tutorial zeigt Ihnen, wie Sie diese Aufgabe mithilfe der Funktionen MAX und VERGLEICH elegant lösen.
Die besten Office-Produktivitätswerkzeuge
Kutools für Excel – Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen und stellt sicher, dass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab – Aktiviert tabbasiertes Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- Mit einem Klick zwischen Dutzenden geöffneter Dokumente wechseln!
- Reduziert täglich Hunderte Mausklicks für Sie – verabschieden Sie sich von der Maus-Hand.
- Steigert Ihre Produktivität um 50 %, wenn Sie mehrere Dokumente gleichzeitig anzeigen und bearbeiten.
- Bringt effiziente Tabs in Office (inklusive Excel) – genau wie bei Chrome, Edge und Firefox.