Steht ein Kunde schon in der Kundenliste? Ist eine Artikelnummer gültig? Wurde ein Name bereits erfasst? Solche Fragen beantwortet Excel mit einer kurzen Formel, die WAHR oder FALSCH liefert oder einen eigenen Text wie „vorhanden“ ausgibt.
Die Kurzlösung
Steht die Liste in A2:A100 und der gesuchte Wert in D2:
=ZÄHLENWENN(A2:A100;D2)>0
Die Formel zählt, wie oft der Wert vorkommt, und ergibt WAHR, sobald es mindestens einmal ist. Mit eigenem Text:
=WENN(ZÄHLENWENN(A2:A100;D2);"vorhanden";"fehlt")
Beispiel
| A | D | Formel | Ergebnis | |
|---|---|---|---|---|
| 2 | Hans | Max | =ZÄHLENWENN(A2:A6;D2)>0 | WAHR |
| 3 | Max | max | =ZÄHLENWENN(A2:A6;D3)>0 | WAHR |
| 4 | Christine | max | =ODER(IDENTISCH(D4;A2:A6)) | FALSCH |
| 5 | Werner | Max | =ODER(IDENTISCH(D5;A2:A6)) | WAHR |
| 6 | Wigbert |
ZÄHLENWENN unterscheidet nicht zwischen Groß- und Kleinschreibung, „max“ wird also gefunden. Soll die Schreibweise genau stimmen, nehmen Sie IDENTISCH.
Genaue Schreibweise mit IDENTISCH
IDENTISCH vergleicht zwei Texte Zeichen für Zeichen, einschließlich Groß- und Kleinschreibung. Zusammen mit ODER prüft die Formel die ganze Liste:
=ODER(IDENTISCH(D2;A2:A100))
In Excel für Microsoft 365 und Excel 2021 genügt die normale Eingabe. In älteren Versionen müssen Sie die Formel mit Strg+Umschalt+Eingabe als Matrixformel abschließen, sonst prüft sie nur eine Zelle.
Varianten und Vergleich
| Formel | Groß/klein | Platzhalter * ? | Wann geeignet |
|---|---|---|---|
=ZÄHLENWENN(A2:A100;D2)>0 | egal | ja | Standard, alle Versionen |
=ISTZAHL(VERGLEICH(D2;A2:A100;0)) | egal | ja | wenn Sie zusätzlich die Position brauchen |
=ISTZAHL(XVERGLEICH(D2;A2:A100)) | egal | nein | Excel 2021 und Microsoft 365, Suchwerte mit Sternchen |
=ODER(IDENTISCH(D2;A2:A100)) | genau | nein | Codes, bei denen „AB1“ und „ab1“ verschieden sind |
Platzhalter bedeutet: Steht im Suchwert ein Sternchen, etwa W*, zählt ZÄHLENWENN alle Einträge, die mit W beginnen, und VERGLEICH liefert die Position des ersten davon. Im Beispiel liefert =ZÄHLENWENN(A2:A6;"W*") den Wert 2 (Werner und Wigbert). XVERGLEICH sucht ohne weitere Angabe nach dem genauen Text W* und findet nichts.
Treffer farbig markieren
Um gefundene Einträge in der Liste hervorzuheben, markieren Sie A2:A100 und legen unter Start, Bedingte Formatierung, Neue Regel eine Regel mit Formel an:
=A2=$D$2
Umgekehrt markiert =ZÄHLENWENN($A$2:$A$100;D2)=0 in einer zweiten Liste alle Werte, die in der ersten Liste fehlen.
Viele Werte auf einmal prüfen (Microsoft 365)
Häufig ist nicht ein einzelner Wert zu prüfen, sondern eine ganze Liste: Welche Artikel aus der Bestellung fehlen im Artikelstamm? Welche Teilnehmer der Anmeldung stehen schon in der Kundenliste? In Microsoft 365 beantwortet das jeweils eine Formel, die das Ergebnis untereinander ausgibt. Ein Beispiel mit der Liste aus A2:A6 (Hans, Max, Christine, Werner, Wigbert) und den zu prüfenden Namen in D2:D6:
| D | Prüfung je Zeile |
|---|---|
| Max | WAHR |
| Anna | FALSCH |
| Werner | WAHR |
| Paul | FALSCH |
| Hans | WAHR |
Die Spalte „Prüfung je Zeile“ entsteht mit einer einzigen Formel in E2:
=ZÄHLENWENN(A2:A6;D2:D6)>0
ZÄHLENWENN bekommt als Suchkriterium den ganzen Bereich D2:D6 und prüft jeden Namen einzeln. Interessanter ist oft nur das, was fehlt. Diese Formel listet alle Namen aus D, die nicht in der Liste stehen:
=FILTER(D2:D6;ZÄHLENWENN(A2:A6;D2:D6)=0;"alle vorhanden")
Ergebnis: Anna und Paul. Ist kein Name übrig, erscheint „alle vorhanden“. Mit =0 durch >0 ersetzt, erhalten Sie die Namen, die in beiden Listen stehen: Max, Werner und Hans. Wie viele Namen gefunden wurden, zählt =ANZAHL(XVERGLEICH(D2:D6;A2:A6)), hier 3.
Für Listen, die wachsen, verwenden Sie gekürzte Bezüge mit Punkt, dann reichen die Bereiche immer bis zur letzten gefüllten Zeile:
=FILTER(D2:.D1000;ZÄHLENWENN(A2:.A1000;D2:.D1000)=0;"alle vorhanden")
Zwei ganze Spalten Zeile für Zeile gegenüberzustellen, mit farbiger Markierung der Unterschiede, beschreibt der Beitrag Zwei Spalten in Excel vergleichen und Unterschiede markieren.
Prüfen, ob ein Wert gültig aufgebaut ist
Nicht immer gibt es eine Liste aller gültigen Werte. Bei Artikelnummern, Kostenstellen oder Rechnungsnummern ist oft nur das Format festgelegt, etwa „zwei Großbuchstaben, Bindestrich, vier Ziffern“. Aktuelle Versionen von Microsoft 365 prüfen solche Muster mit REGEXTESTEN:
=REGEXTESTEN(D2;"^[A-Z]{2}-\d{4}$")
Die Formel ergibt WAHR für „AB-1234“ und FALSCH für „AB-123“ oder „ab-1234“, weil REGEXTESTEN Groß- und Kleinschreibung unterscheidet. ^ und $ stehen für Anfang und Ende, [A-Z]{2} für zwei Großbuchstaben, \d{4} für vier Ziffern. Weitere Zeichen erklärt der Beitrag SUMMEWENN mit Platzhalter: Summen für Textteile bilden.
Damit falsche Werte gar nicht erst in die Liste gelangen, lässt sich die Eingabe über Daten, Datenüberprüfung auf eine Auswahlliste beschränken. Wie das auch mit mehreren Einträgen je Zelle geht, zeigt der Beitrag Dropdown mit Mehrfachauswahl in Excel per VBA.
Typische Fehler
- Leerzeichen am Ende: „Max “ mit Leerzeichen ist für Excel ein anderer Text als „Max“. ZÄHLENWENN findet ihn nicht. Bereinigen Sie den Suchwert mit GLÄTTEN:
=ZÄHLENWENN(A2:A100;GLÄTTEN(D2))>0. Geschützte Leerzeichen aus Webseiten entferntWECHSELN(D2;ZEICHEN(160);""). - Zahl als Text: Steht die Artikelnummer 4711 in der Liste als Text, liefert VERGLEICH für die Zahl 4711 keinen Treffer, ZÄHLENWENN dagegen schon. Wenn VERGLEICH unerwartet nichts findet, prüfen Sie die Zellen mit
=ISTTEXT(A2). - Sternchen oder Fragezeichen im Suchwert: ZÄHLENWENN und VERGLEICH werten sie als Platzhalter. Nehmen Sie dann XVERGLEICH oder setzen Sie eine Tilde davor (
~*). - Bezüge beim Kopieren: Kopieren Sie die Formel nach unten, muss die Liste fest bleiben:
$A$2:$A$100. Die Formeln für Microsoft 365 mit ganzen Bereichen brauchen Sie dagegen nicht zu kopieren.
Mit KI schneller zum Ziel
Eine Formel für den Abgleich zweier Listen schreiben ChatGPT, Claude oder Copilot in Sekunden. Vorsicht ist bei den Daten geboten: Kunden- oder Mitarbeiterlisten gehören nicht in einen öffentlichen KI-Dienst. Für die Formel braucht die KI die echten Namen auch gar nicht. Beschreiben Sie den Aufbau, also welche Spalte was enthält, oder geben Sie ein paar erfundene Beispielzeilen an. Die fertige Formel wenden Sie dann in Ihrer Datei an. Welche Dienste für Unternehmensdaten geeignet sind und welche Regeln gelten, steht im Beitrag KI im Unternehmen: Dienste wählen, Regeln, EU-KI-Verordnung.
Auch bei KI-Formeln gilt: Leerzeichen am Ende und Zahlen als Text bemerkt die KI nicht, wenn sie Ihre Daten nicht sieht. Prüfen Sie das Ergebnis mit den typischen Fehlern oben. Wie wir KI in Projekten verantwortungsvoll einsetzen, um Kosten zu senken, zeigt die Seite KI und Automatisierung.
Aus der Praxis: stille Fehler in Abgleichen
Ein Abgleich, der „nicht gefunden“ meldet, obwohl der Wert da ist, fällt meist erst auf, wenn jemand nachrechnet. Ursache sind fast immer die Fälle aus diesem Beitrag: ein Leerzeichen am Ende, eine Zahl als Text, ein Sternchen im Suchwert. In gewachsenen Arbeitsmappen mit vielen Verweisen sind solche stillen Fehler schwer zu finden, weil keine Fehlermeldung erscheint. Bei einer Prüfung von Arbeitsmappen gehen wir genau solchen Verweisen nach und zeigen, wo Ergebnisse unbemerkt danebenliegen.
Passende Beiträge
Lernen oder machen lassen?
Excel-Formeln, die im Alltag sitzen.
In einer Schulung arbeiten wir mit Ihren eigenen Dateien, damit Sie das Gelernte am nächsten Tag nutzen. Oder wir bauen die Lösung direkt für Sie.
