Weiter zu WhatsApp?

Der Chat öffnet sich in WhatsApp, in der App oder in einem neuen Tab. Dabei verlassen Sie unsere Website und es werden Daten wie Ihre IP-Adresse an WhatsApp (Meta) übermittelt.

EXCEL & LISTEN

Excel-Listen abgleichen: zwei Tabellen über die Artikelnummer sicher verbinden

Die neue Preisliste des Großhändlers ist da, über 3.000 Zeilen, und die Einkaufspreise sollen in Ihre Artikelliste. Also suchen, kopieren, einfügen, Zeile für Zeile. Und am Ende bleibt das ungute Gefühl, dass irgendwo ein Preis in der falschen Zeile gelandet ist.

Hände tippen auf einem Laptop, auf dem Bildschirm eine Tabelle mit Spalten und Prüfwerten zum Abgleichen von Listen
Foto: Bluestonex auf Unsplash

Das kostet nicht nur den Nachmittag. Ein falsch übernommener Einkaufspreis fällt oft erst auf, wenn das Angebot beim Kunden liegt oder die Marge am Jahresende nicht stimmt.

Dieser Artikel zeigt, wie Sie Excel-Listen so abgleichen, dass jede Zeile über die Artikelnummer den richtigen Wert bekommt und jede Lücke sichtbar wird: mit XVERWEIS, mit SVERWEIS für ältere Versionen und mit Power Query für den Abgleich, der jede Woche wiederkommt.

Kurz erklärt: Wie gleiche ich zwei Excel-Listen ab?

Zwei Excel-Listen abgleichen heißt: Sie verbinden jede Zeile aus Liste A mit der passenden Zeile aus Liste B. Die Verbindung läuft über einen eindeutigen Schlüssel, meist die Artikelnummer. Dann holen Sie die gewünschten Werte herüber und markieren alle Zeilen, für die es keinen Treffer gibt.

  1. Schlüsselspalte festlegen

    Wählen Sie eine Spalte, die jede Zeile eindeutig bezeichnet und in beiden Listen vorkommt, zum Beispiel die Artikelnummer des Lieferanten.

  2. Schlüssel bereinigen

    Entfernen Sie Leerzeichen und bringen Sie beide Spalten auf dasselbe Format, also Text oder Zahl, damit 004711 und 4711 nicht aneinander vorbeilaufen.

  3. Werte holen

    Mit XVERWEIS, SVERWEIS oder Power Query übernehmen Sie Preis, Lieferzeit oder Bestand aus der zweiten Liste in die erste.

  4. Lücken sichtbar machen

    Lassen Sie Zeilen ohne Treffer als „nicht gefunden“ ausweisen, statt sie leer zu lassen. Leere Zellen fallen niemandem auf.

  5. Ergebnis prüfen

    Zählen Sie die Treffer, prüfen Sie zehn Zeilen per Stichprobe und übernehmen Sie das Ergebnis erst dann in Kalkulation oder Warenwirtschaft.

Welche Spalte eignet sich als Schlüssel für den Abgleich?

Die meisten Abgleiche scheitern am Schlüssel, selten an der Formel. Eine Schlüsselspalte ist die Spalte, über die Excel zwei Zeilen als zusammengehörig erkennt. Jede Formel und jede Abfrage ist nur so gut wie dieser Schlüssel. Die Artikelnummer eignet sich fast immer. Die Artikelbezeichnung eignet sich fast nie, denn „Kabel NYM-J 3x1,5“ und „NYM-J 3 x 1,5 mm² Kabel“ sind für Excel zwei verschiedene Dinge.

  • Gute Schlüssel: Artikelnummer des Lieferanten, EAN beziehungsweise GTIN, Kundennummer, Rechnungsnummer.
  • Schwache Schlüssel: Bezeichnungen, Firmennamen, Adressen. Hier weichen Schreibweisen ab, und ein Abgleich trifft nur einen Teil.
  • Eigene und fremde Nummern: Führen Sie in Ihrer Artikelliste eine eigene Spalte mit der Lieferanten-Artikelnummer. Ohne diese Brücke fehlt der Schlüssel.

Was tun, wenn eine Spalte allein nicht eindeutig ist?

Manchmal ist nur die Kombination eindeutig, etwa Lieferant und Artikelnummer, wenn zwei Lieferanten dieselben Nummern vergeben. Dann bauen Sie in beiden Listen eine Hilfsspalte mit einem zusammengesetzten Schlüssel: =[@Lieferant]&"|"&[@Artikelnummer]. Das Trennzeichen ist wichtig. Ohne es ergeben „12“ und „345“ dasselbe wie „123“ und „45“.

Zwei Excel-Listen mit Formeln abgleichen: XVERWEIS, SVERWEIS oder INDEX

XVERWEIS in Microsoft 365, Excel 2021 und Excel 2024

XVERWEIS sucht einen Wert in einer Spalte und gibt den Wert aus derselben Zeile einer anderen Spalte zurück. Die Funktion gibt es in Microsoft 365, Excel 2021 und Excel 2024. In Excel 2016 und 2019 fehlt sie.

Wandeln Sie beide Listen zuerst mit Strg+T in eine Tabelle um und benennen Sie die Lieferantentabelle „Lieferant“. Dann lautet die Formel in Ihrer Artikelliste: =XVERWEIS([@Artikelnummer];Lieferant[Artikelnummer];Lieferant[Einkaufspreis];"nicht gefunden")

  • Genauer Treffer ist Standard. XVERWEIS sucht ohne weitere Angabe nur exakte Übereinstimmungen.
  • Der vierte Wert ist Ihr Warnsignal. Statt des Fehlers #NV steht „nicht gefunden“ in der Zelle. Danach können Sie filtern.
  • Die Spaltenreihenfolge ist egal. Die Artikelnummer muss nicht links vom Preis stehen.
  • Für jede weitere Spalte eine eigene Formel. Lieferzeit und Bestand holen Sie mit derselben Formel, nur die Rückgabespalte ändert sich.

SVERWEIS und INDEX mit VERGLEICH für ältere Versionen

Arbeiten Sie mit Excel 2016 oder 2019, nutzen Sie SVERWEIS. Liegt die Lieferantenliste auf einem Blatt namens Lieferant, lautet die Formel: =WENNNV(SVERWEIS(A2;Lieferant!$A:$D;3;FALSCH);"nicht gefunden"). Die 3 steht für die dritte Spalte des Bereichs, hier den Preis in Spalte C.

Das FALSCH am Ende ist der wichtigste Teil. Lassen Sie es weg, sucht SVERWEIS laut Microsoft eine ungefähre Übereinstimmung. Bei unsortierten Artikelnummern kann die Formel dann einen Preis aus einer fremden Zeile liefern, ohne Fehlermeldung. Zwei weitere Schwächen: Die Artikelnummer muss in der ersten Spalte des Bereichs stehen. Und fügt jemand eine Spalte ein, zeigt die feste 3 plötzlich auf die falsche Spalte.

Robuster ist die Kombination aus INDEX und VERGLEICH: =INDEX(Lieferant!$C:$C;VERGLEICH(A2;Lieferant!$A:$A;0)). VERGLEICH findet die Zeile, INDEX holt den Wert. Die 0 erzwingt einen genauen Treffer, und eingefügte Spalten stören nicht.

Excel-Listen mit Power Query zusammenführen, wenn es jede Woche wiederkommt

Power Query ist das Werkzeug in Excel zum Abrufen und Umformen von Daten, zu finden auf der Registerkarte Daten in der Gruppe Daten abrufen und transformieren. In Excel für Windows ist es ab Excel 2016 enthalten, in Excel für Mac nur mit einem Microsoft 365-Abonnement. Prüfen Sie am Mac, ob alle Schritte unten verfügbar sind. Der Vorteil: Sie richten den Abgleich einmal ein und aktualisieren ihn danach per Klick, egal wer im Team ihn startet.

  1. Beide Listen als Abfrage laden

    Klicken Sie in die jeweilige Tabelle und wählen Sie Daten > Aus Tabelle/Bereich. Stellen Sie im Editor den Datentyp der Artikelnummer in beiden Abfragen auf Text. Laut Microsoft müssen die verbundenen Spalten denselben Datentyp haben. Wandelt Power Query die Spalte automatisch in eine Zahl um, gehen führende Nullen verloren.

  2. Zusammenführen öffnen

    Speichern Sie beide Abfragen mit Schließen & laden in > Nur Verbindung erstellen. Dann wählen Sie Daten > Daten abrufen > Abfragen kombinieren > Zusammenführen. Im Power Query-Editor geht es auch über Start > Abfragen zusammenführen. Oben wählen Sie Ihre Artikelliste, darunter die Lieferantenliste.

  3. Schlüssel markieren

    Klicken Sie in beiden Tabellen die Artikelnummer an. Bei einem zusammengesetzten Schlüssel markieren Sie mit gedrückter Strg-Taste mehrere Spalten, in beiden Tabellen in gleicher Reihenfolge.

  4. Join-Art wählen

    Wählen Sie die linke äußere Verknüpfung: alle Zeilen aus der ersten Tabelle, passende aus der zweiten. So bleiben alle Ihre Artikel erhalten, und passende Werte des Lieferanten kommen dazu. Unten im Dialog zeigt Excel an, wie viele Zeilen übereinstimmen. Diese Zahl ist Ihre erste Kontrolle.

  5. Spalten erweitern und laden

    Klicken Sie auf das Erweitern-Symbol (Doppelpfeil) der neuen Spalte, wählen Sie Preis und Lieferzeit und laden Sie das Ergebnis mit Schließen & laden in ein neues Blatt.

Nächste Woche ersetzen Sie nur die Daten in der Lieferantentabelle und klicken auf Daten > Alle aktualisieren. Anders als die Formeln unterscheidet Power Query Groß- und Kleinschreibung. Und ein doppelter Schlüssel in der Lieferantenliste verdoppelt nach dem Erweitern die betroffene Zeile, statt still den ersten Treffer zu nehmen. Die Join-Art „Linker Antijoin“ liefert übrigens genau die Artikel ohne Gegenstück. Mehr dazu im Artikel fehlende Datensätze finden.

Die drei Wege im Vergleich
KriteriumXVERWEISSVERWEISPower Query
Excel-VersionMicrosoft 365, 2021, 2024alle VersionenWindows ab 2016, Mac nur Microsoft 365
Genauer TrefferStandardnur mit FALSCHStandard
Suchrichtungbeliebignur nach rechtsbeliebig
Groß- und Kleinschreibungegalegalwird unterschieden
Doppelter Schlüsselerster Treffererster TrefferZeile wird verdoppelt
Wiederholung nächste WocheFormeln nachziehenFormeln nachziehenein Klick auf Aktualisieren
Mehrere Schlüsselspaltenüber Hilfsspalteüber Hilfsspaltedirekt möglich
Geeignet fürschnellen einmaligen Abgleichältere Excel-Versionenwiederkehrende Abgleiche
Mitarbeiterin im Büro prüft eine ausgedruckte Auswertung am Klemmbrett neben ihrem Laptop
Foto: Vitaly Gariev auf Unsplash

Ein Beispiel aus dem Einkauf: Preise aus der Lieferantenliste übernehmen

Die folgende Tabelle zeigt, woran einzelne Zeilen in diesem Beispiel scheitern. Mit solchen Fällen sollten Sie bei jeder Datei aus einem fremden Programm rechnen.

Typische Gründe für fehlende Treffer (XVERWEIS)
Ihre ListeLieferantenlisteErgebnisUrsache
10-471110-471112,40 €passt
004711 (Text)4711 (Zahl)nicht gefundenführende Nullen verloren, Text trifft nicht auf Zahl
AB-120 mit Leerzeichen am EndeAB-120nicht gefundenunsichtbares Leerzeichen
K-200k-2008,90 €Groß- und Kleinschreibung spielt bei XVERWEIS keine Rolle
5521055210, zweimal vorhanden3,10 €nur der erste Treffer wird geholt, der zweite Preis geht verloren

Warum findet SVERWEIS einen Wert nicht? Typische Fehler und wie Sie sie vermeiden

  • Ungefährer Treffer: SVERWEIS ohne FALSCH kann falsche Preise ohne Fehlermeldung liefern. Prüfen Sie bestehende Dateien gezielt auf dieses vierte Argument.
  • Zahl gegen Text: Eine Artikelnummer als Zahl findet dieselbe Nummer als Text nicht. Vereinheitlichen Sie beide Spalten, etwa mit =TEXT(A2;"000000") für sechsstellige Nummern mit führenden Nullen.
  • Leerzeichen: GLÄTTEN entfernt überzählige Leerzeichen, auch am Anfang und Ende. Das geschützte Leerzeichen (Zeichen 160) aus Webexporten bleibt laut Microsoft stehen. Dafür hilft =GLÄTTEN(WECHSELN(A2;ZEICHEN(160);" ")).
  • Doppelte Schlüssel: Die Formel nimmt still den ersten Treffer. Prüfen Sie den Schlüssel vorher mit ZÄHLENWENN.
  • Feste Bereiche: Ein Bereich wie $A$2:$D$2500 wächst nicht mit. Neue Artikel ab Zeile 2501 werden nie gefunden. Tabellen mit Strg+T wachsen automatisch.
  • Formeln als Endergebnis: Löscht jemand die Lieferantenliste, zeigen alle Formeln Fehler. Fügen Sie das geprüfte Ergebnis als Werte ein, bevor Sie es weitergeben oder importieren.

Ab wann lohnt sich der Abgleich von Hand nicht mehr?

Für einen einmaligen Abgleich reicht eine Formel. Teuer wird die Wiederholung. Eine vorsichtige Rechnung für einen einzigen Lieferanten: Datei öffnen, Spalten anpassen, Formeln nachziehen, Lücken klären. Das dauert schnell 45 Minuten pro Woche. Bei 46 Arbeitswochen sind das 34,5 Stunden im Jahr. Mit angenommenen 38 € internen Kosten pro Stunde, also Lohn plus Nebenkosten, kostet die Routine etwa 1.310 € im Jahr. Fehler sind da noch nicht eingerechnet, etwa ein veralteter Einkaufspreis in einem Angebot.

Ihre eigenen Zahlen rechnen Sie mit dem Büroarbeit-Kostenrechner in zwei Minuten durch. Die Rechnung kippt meist, wenn einer dieser Punkte zutrifft:

  • Mehrere Lieferanten schicken Listen in unterschiedlichen Formaten und mit anderen Spaltennamen.
  • Das Ergebnis muss danach in die Warenwirtschaft oder das ERP importiert werden.
  • Nur eine Person im Betrieb weiß, wie die Datei funktioniert. Ist sie im Urlaub, bleibt der Abgleich liegen.
  • Die Listen kommen als PDF oder im E-Mail-Text, nicht als Excel-Datei.
  • Preisänderungen müssen nicht nur übernommen, sondern auch geprüft und freigegeben werden. Dafür gibt es eigene Prüfregeln, siehe Lieferantenpreislisten vergleichen.

Der nächste Schritt: Listen ohne Formel vergleichen oder den Abgleich abgeben

Kommt die Liste vom Lieferanten, ist unser kostenloser Preislisten-Vergleich der einfachste Einstieg. Sie ziehen alte und neue Liste hinein, als Excel, CSV oder PDF. Das Tool verbindet beide über die Artikelnummer, auch wenn die Spalten anders heißen oder anders sortiert sind, und gleicht führende Nullen aus, die Excel gelöscht hat. Sie sehen jede Preisänderung in Prozent, neue und entfallene Artikel, und laden die Änderungen als Liste herunter. Doppelte Artikelnummern rät es nicht, es zeigt sie unter „Bitte prüfen“. Für andere Listen, etwa Kunden oder Lagerbestände, gibt es den Excel-Vergleich. Er zeigt fehlende, neue und geänderte Zeilen mit altem und neuem Wert. Werte in Ihre eigene Liste einzutragen bleibt Aufgabe der Formel oder von Power Query. In beiden Tools bleiben die Dateien in Ihrem Browser.

Kommt der Abgleich jede Woche und mit mehreren Lieferanten, übernimmt ein Arbeitshelfer die ganze Kette: Liste aus dem Postfach holen, Schlüssel bereinigen, abgleichen, Importdatei für Ihre Warenwirtschaft erstellen. Unklare Zeilen landen bei einer Person, die freigibt. Ihre Software bleibt, wie sie ist, und der Ablauf hängt nicht mehr an einer einzigen Kollegin. Was das konkret bedeutet, zeigt die Seite Excel-Arbeit automatisieren.

Schicken Ihnen mehrere Lieferanten jede Woche neue Listen, lohnt ein Blick auf den ganzen Ablauf. Beschreiben Sie im kostenlosen Ablauf-Check, welche Listen kommen und wohin das Ergebnis muss. Innerhalb von 1–2 Werktagen erhalten Sie eine persönliche, kostenlose Einschätzung, ob Formel, Power Query oder ein Arbeitshelfer am besten passt. Ein Arbeitshelfer kostet ab 1.990 € exkl. USt. zum Festpreis und ist bei kleinen Projekten 3–7 Tage nach Abstimmung des Anwendungsfalls einsatzbereit.

Quellen und weiterführende Links

HÄUFIGE FRAGEN

Kurz beantwortet.

Wie kann ich zwei Excel-Listen abgleichen und Werte übernehmen?

Sie verbinden beide Listen über eine eindeutige Schlüsselspalte, meist die Artikelnummer. Holen Sie dann mit XVERWEIS, SVERWEIS mit FALSCH oder Power Query die gewünschten Werte aus der zweiten Liste. Lassen Sie Zeilen ohne Treffer als „nicht gefunden“ ausweisen, zählen Sie diese und prüfen Sie sie einzeln, bevor Sie das Ergebnis verwenden.

Wie gleiche ich Excel-Listen ohne SVERWEIS ab?

In Microsoft 365, Excel 2021 und Excel 2024 nutzen Sie XVERWEIS, in älteren Versionen INDEX mit VERGLEICH. Für wiederkehrende Abgleiche eignet sich Power Query mit der Funktion Abfragen zusammenführen. Ganz ohne Formel geht es mit einem Vergleichstool, das beide Listen über eine Schlüsselspalte wie die Artikelnummer zuordnet.

Warum findet SVERWEIS einen Wert nicht, obwohl er in der Liste steht?

Meist unterscheiden sich die Werte unsichtbar: Leerzeichen am Ende, ein geschütztes Leerzeichen oder eine Nummer, die in einer Liste als Zahl und in der anderen als Text gespeichert ist. Bereinigen Sie beide Spalten mit GLÄTTEN und WECHSELN und bringen Sie sie auf dasselbe Format. Prüfen Sie außerdem, ob das letzte Argument FALSCH lautet.

Kann ich zwei Excel-Tabellen über mehrere Spalten abgleichen?

Ja. Mit Formeln bauen Sie in beiden Tabellen eine Hilfsspalte, die die Spalten mit einem Trennzeichen verbindet, etwa Lieferant und Artikelnummer. In Power Query markieren Sie beim Zusammenführen mehrere Spalten direkt mit gedrückter Strg-Taste, in beiden Tabellen in derselben Reihenfolge.

Kann ich Excel-Listen online vergleichen, ohne die Daten hochzuladen?

Ja, wenn das Tool die Dateien direkt im Browser verarbeitet. Unser kostenloser Excel-Vergleich arbeitet so: Die Dateien werden nicht hochgeladen, und Sie sehen neue, fehlende und geänderte Zeilen. Unterstützt werden .xlsx und .csv bis 5 MB und 20.000 Zeilen je Datei. Doppelte Schlüssel markiert das Tool als unklar, damit Sie sie prüfen.

SPRECHEN WIR ÜBER IHREN ABLAUF

Schreiben Sie uns. Wir melden uns persönlich.

Eine Frage, eine Idee oder eine Aufgabe, die jede Woche Zeit kostet? Ein paar Sätze reichen. Sie bekommen eine ehrliche Antwort von den Menschen, die Ihren Arbeitshelfer auch bauen.

Ivan, Lukas & JanAntwort in 1–2 Werktagen

  • Persönliche Antwort
  • Unverbindlich
  • Aus Wien, DSGVO-bewusst
Foto von Ivan
Lieber direkt sprechen?Ivan, Ihr Ansprechpartner+43 676 59 40 027office@pebblebyte.comMo bis Fr 08:00–18:00
Konkrete Aufgabe? Zum kostenlosen Ablauf-Check

Ihre Nachricht

Bitte keine Zugangsdaten oder vertraulichen Kundendaten eingeben. Wir verwenden Ihre Angaben nur zur Bearbeitung Ihrer Anfrage. Kein Newsletter, keine Weitergabe zu Werbezwecken. Details im Datenschutzhinweis.