Textsuche mit XVERWEIS – Excel und Google Sheets
Die Beispielarbeitsmappe herunterladen
In diesem Tutorial zeigt wir Ihnen, wie Sie die XVERWEIS-Funktion mit Text in Excel verwenden können.
XVERWEIS mit Text
Um eine Textzeichenfolge nachzuschlagen, können Sie sie in die XVERWEIS-Funktion in Anführungszeichen eingeben.
=XVERWEIS("Abo 2";B3:B7;C3:C7)
XVERWEIS mit Text in Zellen
Oder Sie können auf eine Zelle verweisen, die Text enthält.
=XVERWEIS(E3;B3:B7;C3:C7)
XVERWEIS mit mehreren Texten
Wenn Sie eine Reihe von Texten nachschlagen möchten, geben Sie den Bereichsbezug in die Formel ein, und es wird eine dynamische Array-Formel (Engl. Dynamic Array Spill Formula) erstellt.
=XVERWEIS(E3:E4;B3:B7;C3:C7)
Die Formel läuft durch, um die Berechnungen für das gesamte Werte-Array auszuführen. Alternativ können Sie auch auf eine einzelne Zelle verweisen, die Zellenbezüge sperren und die Formel nach unten kopieren.
=XVERWEIS(E3;$B$3:$B$7;$C$3:$C$7)
Hinweis: Stellen Sie sicher, dass das 2. und 3. Argument mit absoluten Bezügen belegt werden, indem Sie Dollarzeichen vor dem Spaltenbuchstaben und der Zeilennummer einfügen (oder Sie setzen den Cursor in den Bezug, während Sie sich in der Formel befinden, und drücken F4). Dadurch werden die Verweise beim Ziehen oder Kopieren der Formel fixiert.
XVERWEIS – den letzten Text finden
Standardmäßig sucht die XVERWEIS-Funktion eine exakte Übereinstimmung vom Anfang der Liste nach unten (d. h. Top-Down-Ansatz). Wir können dies umkehren, indem wir dem letzten Argument Suchmodus den Wert -1 zuweisen. Wir können das letzte Vorkommen eines Textes in einer Liste finden, indem wir -1 auf das letzte Argument der XVERWEIS-Funktion anwenden.
=XVERWEIS(E3;B3:B7;C3:C7;;;-1)
XVERWEIS mit der Funktion IDENTISCH
Es ist aus den vorherigen Beispielen nicht ersichtlich, aber der Abgleich der XVERWEIS-Funktion unterscheidet nicht zwischen Groß- und Kleinschreibung. Das bedeutet, dass der Text unabhängig von der Groß- oder Kleinschreibung übereinstimmt. Wir können die Funktion IDENTISCH verwenden, um einen Abgleich unter Berücksichtigung der Groß- und Kleinschreibung durchzuführen.
=XVERWEIS(WAHR;IDENTISCH(E3;B3:B7);C3:C7)
Sehen wir uns an, wie die obige Formel funktioniert:
Die IDENTISCH-Funktion vergleicht zwei Werte unter Berücksichtigung der Groß- und Kleinschreibung. In unserem Beispiel wird ein Array erstellt, das den Nachschlagewert (siehe Spalte C) und jeden der Werte aus dem Nachschlage-Array (siehe Spalte B) vergleicht. Die Funktion IDENTISCH gibt WAHR zurück, wenn die verglichenen Texte gleich sind, und FALSCH, wenn sie unterschiedlich sind (siehe Spalte D).
In unserer XVERWEIS-Formel sieht das so aus:
Hinweis: Dies funktioniert nur, wenn es eine Array-Eingabe gibt (B3:B7 in der XVERWEIS-Formel). Die Funktion IDENTISCH gibt auch eine Array-Ausgabe zurück, wie in Spalte D zu sehen ist. Die Array-Ausgabe der Funktion IDENTISCH wird dann als Nachschlage-Array für die XVERWEIS-Funktion verwendet. Wir suchen nach dem ersten Vorkommen des Wertes WAHR, was eine exakte Übereinstimmung bedeutet, und geben den entsprechenden Status zurück.
XVERWEIS mit teilweiser Übereinstimmung
Der Abgleich in den vorangegangenen Beispielen erfolgt durch den Vergleich ganzer Texte oder Phrasen, aber wir können auch einen Teilabgleich in XVERWEIS durchführen.
XVERWEIS mit Platzhaltern
Die einfachste Methode, einen Teilabgleich durchzuführen, ist die Verwendung von Platzhaltern. Um dies zu ermöglichen, müssen wir das 5. Argument (Vergleichsmodus) auf 2 setzen.
=XVERWEIS("*"&E3&"*";B3:B7;C3:C7;;2)
Hinweis: Der Platzhalter Sternchen steht für eine beliebige Anzahl von Zeichen. Durch Hinzufügen des Sternchens am Anfang und am Ende wird nach jedem Text gesucht, der „RS“ enthält.
XVERWEIS mit der Funktion SUCHEN
Eine andere Möglichkeit, eine teilweise Übereinstimmung durchzuführen, ist die Verschachtelung der Funktion SUCHEN innerhalb von XVERWEIS. Damit dies funktioniert, müssen wir die Funktion SUCHEN in die Funktion ISTZAHL einwickeln.
=XVERWEIS(WAHR;ISTZAHL(SUCHEN(E3;B3:B7));C3:C7;;2)
Sehen wir uns an, wie die Formel funktioniert: 
Die Funktion SUCHEN sucht einen Text in einem anderen. Sie gibt eine Positionsnummer zurück, wenn sie den gesuchten Text gefunden hat. Andernfalls gibt sie einen Fehler zurück. Da wir eine Array-Eingabe haben (B3:B7), führt sie die Suche für jeden der Werte innerhalb des Eingabe-Arrays durch und gibt eine Array-Ausgabe zurück (Spalte C unten).
So sieht es in unserer Formel aus:
Die Array-Ausgabe der Funktion SUCHEN wird dann an die Funktion ISTZAHL weitergeleitet, die prüft, ob die von der Funktion SUCHEN zurückgegebenen Werte Zahlen sind. Wenn ein Wert einer Zahl entspricht, gibt sie WAHR zurück. Andernfalls FALSCH. (Siehe Spalte D unten) 
So sieht das Ergebnis der Funktion ISTZAHL in der Formel aus:
Wir haben jetzt ein Array mit WAHR- und FALSCH-Werten, die als Nachschlage-Array für die XVERWEIS-Funktion verwendet werden. Wir suchen nach WAHR als Teilübereinstimmung.
XVERWEIS mit der Funktion FINDEN
Die Funktionen SUCHEN und FINDEN sind fast identisch. Ein wichtiger Unterschied besteht darin, dass bei der Funktion SUCHEN die Groß- und Kleinschreibung nicht beachtet wird, während sie bei der Funktion FINDEN berücksichtigt wird. Um eine teilweise Übereinstimmung unter Berücksichtigung der Groß- und Kleinschreibung zu erzielen, verwenden wir die Kombination aus den Funktionen FINDEN und ISTZAHL.
=XVERWEIS(WAHR;ISTZAHL(FINDEN(E3;B3:B7));C3:C7;;2)
XVERWEIS – Teilweise Übereinstimmung Problem
Die in den vorangegangenen Abschnitten besprochenen partiellen Abgleichsmethoden haben einen großen Fehler. Sie können keine exakte Textübereinstimmung innerhalb einer gegebenen Phrase erkennen. Der nachstehende teilweise Abgleich kann den Begriff EPA nicht von dem Wort „epa“ und dem Wort „Separate“ unterscheiden
XVERWEIS – Exakte Teilübereinstimmung
Wenn die Wörter durch Leerzeichen getrennt sind, können wir Leerzeichen hinzufügen, um eine exakte Teilübereinstimmung zu erzielen.
=XVERWEIS("*"&E3&" *";""&B3:B7&" ";C3:C7;;2)
Hinweis: Wir fügen Leerzeichen vor und nach E3 ein, und wir tun dasselbe für das Nachschlage-Array in B3:B7. Auf diese Weise werden die Wörter mit Leerzeichen am Anfang und am Ende gebunden, um eine genaue Übereinstimmung der Begriffe innerhalb der Phrasen zu gewährleisten.
XVERWEIS – Text-Problem
Eines der häufigsten Probleme beim Textabgleich sind unerwünschte Leerzeichen. Leerzeichen werden als Teil des Textes behandelt. Wenn also zusätzliche Leerzeichen vorhanden sind, die schwer zu erkennen sind, gibt die XVERWEIS-Funktion einen Fehler zurück. Stellen Sie sicher, dass keine unerwünschten Leerzeichen im Suchwert und im Suchfeld vorhanden sind. Der Nachschlagewert (E3) im folgenden Beispiel hat ein zusätzliches Leerzeichen zwischen „Abo“ und „2“ 
XVERWEIS mit der Funktion GLÄTTEN
Wir können die Funktion GLÄTTEN verwenden, um die zusätzlichen Leerzeichen zu entfernen. Wir können sie sowohl auf den Suchwert als auch auf das gesamte Suchfeld anwenden.
=XVERWEIS(GLÄTTEN(E3);GLÄTTEN(B3:B7);C3:C7)
Hinweis: Die Funktion GLÄTTEN entfernt zusätzliche Leerzeichen zwischen den Wörtern, bis sie durch ein Leerzeichen voneinander getrennt sind. Zusätzlich werden alle Leerzeichen vor dem ersten Wort und nach dem letzten Wort eines Textes oder einer Phrase entfernt.
