XVERWEIS mit WENN-Anweisung – Excel & Google Sheets
Die Beispielarbeitsmappe herunterladen
In diesem Tutorial zeigen wir Ihnen, wie Sie die Funktionen XVERWEIS und WENN in Excel kombinieren können. Wenn Ihre Excel-Version XVERWEIS nicht unterstützt, lesen Sie, wie Sie stattdessen SVERWEIS verwenden können.
XVERWEIS – Mehrere Nachschlagekriterien
Es gibt viele Möglichkeiten, die WENN-Funktion zusammen mit der XVERWEIS-Funktion zu verwenden, aber sehen wir uns zunächst ein Beispiel an, bei dem das Kernelement der WENN-Funktion (die logischen Kriterien) verwendet wird.
Ein häufiges Beispiel ist die Durchführung einer Suche mit mehreren Kriterien, und die gängigste Lösung hierfür ist die Verkettung der Suchkriterien (z. B. F3&G3) und der entsprechenden Spalten in den Nachschlagedaten (z. B. B3:B7&C3:C7).
=XVERWEIS(F3&G3;B3:B7&C3:C7;D3:D7)
Die obige Methode funktioniert in den meisten Fällen gut, kann aber bei Bedingungen, die Zahlen beinhalten, zu falschen Ergebnissen führen.
Eine sicherere Methode ist die Erstellung eines Arrays von booleschen Werten aus den logischen Kriterien.
=XVERWEIS(1;(B3:B7=F3)*(C3:C7=G3);D3:D7)
Lassen Sie uns diese Formel durchgehen:
Logische Kriterien
Zunächst wenden wir die entsprechende Bedingung auf die entsprechenden Spalten an, indem wir die logischen Operatoren (z. B. =, <, >) verwenden.
Beginnen wir mit dem ersten Kriterium (z. B. Studenten-ID).
=B3=$C$2
Wiederholen Sie den Schritt für die anderen Kriterien (z. B. Fach).
=D3=$E$2
Das UND-Array
Als Nächstes führen wir das Array-Äquivalent der UND-Funktion aus, indem wir die booleschen Arrays multiplizieren, wobei WAHR gleich 1 und FALSCH gleich 0 ist.
=C3*E3
Hinweis: Die UND-Funktion ist eine Aggregatfunktion (viele Eingänge für einen Ausgang). Daher wird sie in unserem Array-Szenario nicht funktionieren.
Die XVERWEIS-Funktion
Als Nächstes verwenden wir das Ergebnis des UND-Arrays als neues Nachschlage-Array, in dem wir nach 1 anstelle des ursprünglichen Suchwertes suchen werden.
=XVERWEIS(1;F3:F7;G3:G7)
Die Kombination aller obigen Formeln führt zu unserer ursprünglichen Formel:
=XVERWEIS(1;(B3:B7=F3)*(C3:C7=G3);D3:D7)XVERWEIS – Fehlerbehandlung mit WENN
Manchmal muss geprüft werden, ob das Ergebnis einer XVERWEIS-Funktion zu einem Fehler führt. Eine gute Möglichkeit, dies zu tun, ist die Verwendung der WENN-Funktion, die uns auch am besten über die Ursache des Fehlers informiert.
XVERWEIS-WENN mit ISTNV
Prüfen wir zunächst, ob XVERWEIS eine Übereinstimmung gefunden hat, indem wir die Formel WENN mit ISTNV verwenden.
=WENN(ISTNV(XVERWEIS(E3;B3:B7;C3:C7));"Produkt nicht gefunden!";F3/XVERWEIS(E3;B3:B7;C3:C7))
Gehen wir die obige Formel durch:
Die ISTNV-Funktion
Prüfen wir zunächst mit der ISTNV-Funktion, ob ein #NV-Fehler vorliegt, was im Grunde bedeutet, dass keine Übereinstimmung gefunden wurde.
=ISTNV(XVERWEIS(E3;B3:B7;C3:C7))
Die WENN-Funktion
Als Nächstes verwenden wir die WENN-Funktion, um das Ergebnis der ISTNV-Funktion zu prüfen und eine Meldung zurückzugeben (z. B. „Produkt nicht gefunden!“), wenn das Ergebnis WAHR ist. Andernfalls, wenn das Ergebnis FALSCH ist, fahren wir mit der Berechnung fort.
=WENN(G3;"Produkt nicht gefunden!";F3/XVERWEIS(E3;B3:B7;C3:C7))
Die Kombination aller Formeln führt zu unserer ursprünglichen Formel:
=WENN(ISTNV(XVERWEIS(E3;B3:B7;C3:C7));"Produkt nicht gefunden!";F3/XVERWEIS(E3;B3:B7;C3:C7))XVERWEIS-WENN mit ISTLEER
Ein weiterer Punkt, den es zu prüfen gilt, ist, ob das Ergebnis von XVERWEIS leer ist. Es gibt Fälle, in denen ein Leerzeichen bedeutet, dass es noch keine Eingaben gibt, und deshalb müssen wir es von Null unterscheiden.
Wir ersetzen einfach ISTNV durch ISTLEER, um auf Leerzeichen zu prüfen.
=WENN(ISTLEER(XVERWEIS(E3;B3:B7;C3:C7));"Keine Daten!";F3/XVERWEIS(E3;B3:B7;C3:C7))
Gehen wir die obige Formel einmal durch:
Die ISTLEER-Funktion
Zunächst prüfen wir mit der ISTLEER-Funktion, ob ein Leerzeichen vorliegt.
=ISTLEER(XVERWEIS(E3;B3:B7;C3:C7))
Die WENN-Funktion
Wie im vorherigen Szenario geben wir dann das Ergebnis der ISTLEER-Funktion in die WENN-Funktion ein und geben eine Meldung zurück (z. B. „Keine Daten!“), wenn das Ergebnis WAHR ist, oder fahren mit der Berechnung fort, wenn es FALSCH ist.
=WENN(G3;"Keine Daten!";F3/XVERWEIS(E3;B3:B7;C3:C7))
Die Kombination aller Formeln führt zu unserer ursprünglichen Formel:
=WENN(ISTLEER(XVERWEIS(E3;B3:B7;C3:C7));"Keine Daten!";F3/XVERWEIS(E3;B3:B7;C3:C7))
XVERWEIS-WENN mit ISTTEXT
Eine weitere Sache, die man bei Berechnungen vermeiden sollte, ist die versehentliche Eingabe von Text. In diesem Fall verwenden wir die Formel WENN with ISTTEXT, um auf einen Textwert zu prüfen.
=WENN(ISTTEXT(XVERWEIS(E3;B3:B7;C3:C7));"Ungültige Eingabe!";F3/XVERWEIS(E3;B3:B7;C3:C7))
Die ISTTEXT-Funktion
Zunächst wird geprüft, ob die Ausgabe der XVERWEIS-Funktion ein Text ist.
=ISTTEXT(XVERWEIS(E3;B3:B7;C3:C7))
Die WENN-Funktion
Als Nächstes prüfen wir das Ergebnis mit der WENN-Funktion und geben die entsprechende Meldung zurück (z. B. „Ungültige Eingabe!“), wenn das Ergebnis WAHR ist, oder fahren mit der Berechnung fort, wenn es FALSCH ist.
=WENN(G3;"Ungültige Eingabe!";F3/XVERWEIS(E3;B3:B7;C3:C7))
XVERWEIS mit WENNS
Die endgültige Fehlerbehandlungsformel wäre die Kombination der vorherigen WENN-Formeln und wir können dies durch Verschachtelung erreichen.
=WENN(ISTNV(XVERWEIS(E3;B3:B7;C3:C7));"Produkt nicht gefunden!";
WENN(ISTLEER(XVERWEIS(E3;B3:B7;C3:C7));"Keine Daten!";
WENN(ISTTEXT(XVERWEIS(E3;B3:B7;C3:C7));"Ungültige Eingabe!";F3/XVERWEIS(E3;B3:B7;C3:C7))
)
)
Wie wir oben gesehen haben, wird die verschachtelte WENN-Formel immer komplizierter, je mehr Bedingungen wir hinzufügen. Ein besserer Weg, dies zu tun, ist die Verwendung der WENNS-Funktion.
=WENNS(ISTNV(XVERWEIS(E3;B3:B7;C3:C7));"Produkt nicht gefunden!";
ISTLEER(XVERWEIS(E3;B3:B7;C3:C7));"Keine Daten!";
ISTTEXT(XVERWEIS(E3;B3:B7;C3:C7));"Ungültige Eingabe!";
WAHR;F3/XVERWEIS(E3;B3:B7;C3:C7)
)
Hinweis: Die WENNS-Funktion kann mehrere Sätze von logischen Kriterien auswerten. Sie beginnt mit der ersten Bedingung und fährt mit der nächsten fort, bis sie die erste WAHR-Bedingung findet und den entsprechenden Rückgabewert zurückgibt.
Lassen Sie uns die obige Formel durchgehen:
ISTNV
Wir beginnen mit unserer ersten Bedingung, der ISTNV-Funktion. Wenn ISTNV WAHR zurückgibt, geben wir den entsprechenden Wert zurück (z. B. „Produkt nicht gefunden!“). Andernfalls fahren wir mit der Prüfung der nächsten Bedingung fort.
=ISTNV(XVERWEIS(E3;B3:B7;C3:C7))
ISTLEER und ISTTEXT
Da die erste Bedingung in diesem Szenario FALSCH ist, prüfen wir die nachfolgenden Bedingungen, bis wir das erste WAHR finden, und geben den entsprechenden Wert für die WAHR-Bedingung zurück.
=ISTLEER(XVERWEIS(E3;B3:B7;C3:C7))
=ISTTEXT(XVERWEIS(E3;B3:B7;C3:C7))
Standardwert
Wir können einen Standardwert festlegen, indem wir die letzte Bedingung auf WAHR setzen, falls alle Bedingungen FALSCH sind. In unserem Szenario zur Fehlerbehandlung bedeutet dies, dass wir jetzt mit der Berechnung ohne Fehler fortfahren können.
Die Kombination aller obigen Formeln führt zu unserer ursprünglichen Formel:
=WENNS(ISTNV(XVERWEIS(E3;B3:B7;C3:C7));"Produkt nicht gefunden!";
ISTLEER(XVERWEIS(E3;B3:B7;C3:C7));"Keine Daten!";
ISTTEXT(XVERWEIS(E3;B3:B7;C3:C7));"Ungültige Eingabe!";
WAHR;F3/XVERWEIS(E3;B3:B7;C3:C7)
)
