Fehlende Werte finden

Es gibt Situationen, in denen Sie zwei Listen vergleichen müssen, um zu prüfen, ob ein Wert aus Liste A in Liste B in Excel enthalten ist. Beispielsweise verfügen Sie über eine eigene Produktliste und möchten sicherstellen, dass Ihre Produkte auch in der vom Lieferanten bereitgestellten Liste enthalten sind. Dafür stellen wir Ihnen hier drei bewährte Methoden vor – wählen Sie diejenige, die am besten zu Ihnen passt.

Fehlende Werte mit MATCH, ISNV und WENN finden
Fehlende Werte mit VLOOKUP, ISNV und WENN finden
Fehlende Werte mit COUNTIF und WENN finden

Fehlende Werte mit MATCH, ISNV und WENN finden

Um herauszufinden, ob alle Produkte in Ihrer Liste auch in der Liste Ihres Lieferanten enthalten sind, wie im obigen Screenshot gezeigt, verwenden Sie zunächst die MATCH-Funktion, um die Position eines Produkts aus Ihrer Liste (Wert aus Liste A) in der Lieferantenliste (Liste B) zu ermitteln. MATCH gibt den Fehlerwert #NV zurück, wenn ein Produkt nicht gefunden wird. Anschließend übergeben Sie das Ergebnis an ISNV, um die #NV-Fehler in WAHR-Werte umzuwandeln – was bedeutet, dass diese Produkte fehlen. Die WENN-Funktion liefert dann das gewünschte Ergebnis.

Allgemeine Syntax

=IF(ISNA(MATCH()))„lookup_value",lookup_range,0)),"Missing",„Found")

✓ Hinweis: Sie können „Fehlt“ und „Gefunden“ durch beliebige Werte Ihrer Wahl ersetzen.

Um herauszufinden,ob alle Produkte in Ihrer Liste in der Liste Ihres Lieferanten enthalten sind,kopieren oder geben Sie die folgende Formel in die Zelle H6 ein und drücken Sie Enter, um das Ergebnis zu erhalten:

=WENN(ISTNV(VERGLEICH()))30002,$B$6:$B$10,0)),"Fehlt",„Gefunden")

Oder verwenden Sie Eine Zellreferenz, um die Formel dynamisch zu gestalten:

=WENN(ISTNV(VERGLEICH()))G6,$B$6:$B$10,0)),"Fehlt",„Gefunden")

√ Hinweis: Die Dollarzeichen ($) oben kennzeichnen absolute Bezüge, was bedeutet, dass sich der Suchbereichin der Formel nicht ändert, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Für den Suchwertwurden jedoch keine Dollarzeichen hinzugefügt, da dieser dynamisch sein soll. Ziehen Sie nach Eingabe der Formel am Ausfüllkästchen nach unten, um die Formel auf die darunterliegenden Zellen anzuwenden.

Erläuterung der Formel

Im Folgenden dient die unten stehende Formel als Beispiel:

=IF()ISNA()MATCH(G8,$B$6:$B$10,0)),"Missing",„Found")

• MATCH(G8;$B$6:$B$10,0): Der Übereinstimmungstyp 0 zwingt die MATCH-Funktion dazu, einen numerischen Wert zurückzugeben, der die Position der ersten exakten Übereinstimmung des Werts 3004 – also des Inhalts der Zelle G8 – im Array $B$6:$B$10 angibt. In diesem Fall konnte MATCH den gesuchten Wert im Array jedoch nicht finden und gibt daher den Fehler #NV zurück.
• ISNV()MATCH(G8;$B$6:$B$10,0))=ISNV()#NV): Die Funktion ISNV prüft, ob ein Wert dem Fehler „#NV“ entspricht. Wenn ja, gibt sie WAHR zurück; andernfalls FALSCH. Daher liefert diese ISNV-Formel WAHR.
• WENN()ISNV()MATCH(G8;$B$6:$B$10,0));"Fehlt";„Gefunden") = WENN(WAHR;"Fehlt";„Gefunden"): Die WENN-Funktion gibt „Fehlt“ zurück, wenn der von ISNV und MATCH durchgeführte Vergleich WAHR ergibt; andernfalls liefert sie „Gefunden“. Daher ergibt die Formel Fehlt.

Fehlende Werte mit SVERWEIS, ISTNV und WENN finden

Um herauszufinden, ob alle Produkte Ihrer Liste in der Lieferantenliste enthalten sind, ersetzen Sie einfach die VERGLEICH-Funktion oben durch SVERWEIS – denn auch SVERWEIS verhält sich identisch: Er gibt den Fehlerwert #NV zurück, sobald ein Wert in der anderen Liste fehlt.

Allgemeine Syntax

=IF(ISNA(VLOOKUP()))„lookup_value",lookup_range,1,FALSE)),"Missing",„Found")

✓ Hinweis: Sie können „Fehlt“ und „Gefunden“ durch beliebige Werte Ihrer Wahl ersetzen.

Um herauszufinden, ob alle Produkte Ihrer Liste in der Lieferantenliste enthalten sind, kopieren oder geben Sie bitte die folgende Formel in die Zelle H6 ein und drücken Sie Enter, um das Ergebnis zu erhalten:

=WENN(ISTNV(SVERWEIS()))30002,$B$6:$B$10,1;FALSCH)),"Fehlt",„Gefunden")

Oder verwenden Sie Eine Zellreferenz, um die Formel dynamisch zu gestalten:

=WENN(ISTNV(SVERWEIS()))G6,$B$6:$B$10,1;FALSCH)),"Fehlt",„Gefunden")

√ Hinweis: Die Dollarzeichen ($) oben kennzeichnen absolute Bezüge, was bedeutet, dass sich der Suchbereich in der Formel nicht ändert, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Für den Suchwert wurden jedoch keine Dollarzeichen hinzugefügt, da dieser dynamisch sein soll. Ziehen Sie nach Eingabe der Formel am Ausfüllkästchen nach unten, um die Formel auf die darunterliegenden Zellen anzuwenden.

Erläuterung der Formel

Im Folgenden dient die unten stehende Formel als Beispiel:

=IF()ISNA()VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing",„Found")

• VLOOKUP(G8;$B$6:$B$10;1;FALSCH): Das Argument Bereich_Verweis auf FALSCH zwingt die VLOOKUP-Funktion dazu, exakt nach dem Wert zu suchen, der mit 3004 – dem Wert in Zelle G8 – übereinstimmt. Falls der Suchwert 3004 in der 1. Spalte des Arrays $B$6:$B$10 vorhanden ist, gibt VLOOKUP diesen Wert zurück; andernfalls liefert sie den Fehlerwert #NV. Da 3004 im Array nicht enthalten ist, lautet das Ergebnis #NV.
• ISNV()VLOOKUP(G8;$B$6:$B$10,1;1;FALSCH))=ISNV()#NV): Die Funktion ISNV prüft, ob ein Wert den Fehler „#NV“ darstellt. Wenn ja, gibt sie WAHR zurück; andernfalls – also wenn der Wert irgendetwas anderes als „#NV“ ist – liefert sie FALSCH. Daher ergibt diese ISNV-Formel WAHR.
• WENN()ISNV()VLOOKUP(G8;$B$6:$B$10,1;1;FALSCH));"Fehlt";„Gefunden") = WENN(WAHR;"Fehlt";„Gefunden"): Die WENN-Funktion gibt „Fehlt“ zurück, wenn der von ISNV und VLOOKUP durchgeführte Vergleich WAHR ergibt; andernfalls liefert sie „Gefunden“. Daher ergibt die Formel Fehlt.

Fehlende Werte mit ZÄHLENWENN und WENN finden

Um herauszufinden, ob alle Produkte Ihrer Liste in der Lieferantenliste enthalten sind, können Sie eine einfachere Formel mit den Funktionen ZÄHLENWENN und WENN verwenden. Diese Formel nutzt die Tatsache aus, dass Excel jede Zahl außer null (0) als WAHR interpretiert. Wenn ein Wert in einer anderen Liste vorhanden ist, gibt ZÄHLENWENN die Anzahl seiner Vorkommen in dieser Liste zurück, und WENN wertet diese Zahl als WAHR aus. Ist der Wert hingegen nicht in der Liste enthalten, gibt ZÄHLENWENN 0 zurück, und WENN wertet dies als FALSCH aus.

Allgemeine Syntax

=IF(COUNTIF())„lookup_range",lookup_value),"Found",„Missing")

√ Hinweis: Sie können „Gefunden“ und „Fehlt“ nach Bedarf durch beliebige Werte ersetzen.

Um herauszufinden, ob alle Produkte Ihrer Liste in der Lieferantenliste enthalten sind, kopieren oder geben Sie bitte die folgende Formel in die Zelle H6 ein und drücken Sie Enter, um das Ergebnis zu erhalten:

=WENN(ZÄHLENWENN())$B$6:$B$10,30002),"Gefunden",„Fehlt")

Oder verwenden Sie Eine Zellreferenz, um die Formel dynamisch zu gestalten:

=WENN(ZÄHLENWENN())$B$6:$B$10,G6),"Gefunden",„Fehlt")

√ Hinweis: Die Dollarzeichen ($) oben kennzeichnen absolute Bezüge, was bedeutet, dass der Suchbereichin der Formel nicht ändert, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Für den Suchwertwurden jedoch keine Dollarzeichen hinzugefügt, da dieser dynamisch sein soll. Ziehen Sie nach Eingabe der Formel am Ausfüllkästchen nach unten, um die Formel auf die darunterliegenden Zellen anzuwenden.

Erläuterung der Formel

Im Folgenden dient die unten stehende Formel als Beispiel:

=IF()COUNTIF($B$6:$B$10,G8),"Found",„Missing")

• COUNTIF($B$6:$B$10;G8): Die COUNTIF-Funktion zählt, wie oft 3004 – der Wert in Zelle G8 – im Bereich $B$6:$B$10 vorkommt. Da 3004 in diesem Bereich nicht enthalten ist, lautet das Ergebnis 0.
• WENN()COUNTIF($B$6:$B$10;G8);"Gefunden";„Fehlt") = WENN(0;"Gefunden";„Fehlt"): Die WENN-Funktion wertet 0 als FALSCH aus. Daher gibt die Formel Fehlt zurück – den Wert, der ausgegeben wird, wenn das erste Argument FALSCH ist.

Verwandte Funktionen

Verwandte Formeln