Excel SVERWEIS: Formel Schritt für Schritt mit Beispiel

Du hast eine Artikelnummer und brauchst den Preis dazu. Der Preis steht in einer anderen Liste, vielleicht auf einem anderen Blatt. Von Hand hieße das: Nummer suchen, in der Zeile nach rechts gehen, Preis abschreiben. Genau diese drei Handgriffe erledigt der Excel SVERWEIS, nur für tausend Zeilen auf einmal. Die Funktion gilt als schwierig, weil sie vier Argumente hat und bei Fehlern wenig erklärt. Tatsächlich macht sie immer dasselbe, und fast jeder Fehler hat eine von fünf Ursachen. Diese Anleitung baut die Formel Argument für Argument an einer Preisliste mit sechs Artikeln auf, zeigt die ungefähre Suche für Rabattstaffeln und nennt die Alternativen für die Fälle, in denen der SVERWEIS nicht reicht.

Die Preise stammen aus den Produktkarten in diesem Beitrag und koennen sich inzwischen geaendert haben. Die Links sind Werbelinks.

Was macht der SVERWEIS?

Das S steht für senkrecht. Die Funktion läuft die erste Spalte eines Bereichs von oben nach unten ab, bis sie den Suchwert findet. Dann geht sie in dieser Zeile nach rechts und gibt den Inhalt der Spalte zurück, die du ihr nennst. Es gibt auch einen WVERWEIS für waagerecht angeordnete Listen. Den brauchst du selten.

Die Namen der Argumente stammen aus der Formelhilfe von Excel und klingen sperriger als nötig. Merke dir die vier Fragen dahinter: Was suche ich? Wo suche ich? Die wievielte Spalte will ich? Muss es genau passen?

Die Beispieldaten

Auf dem Blatt Preise steht die Preisliste in A1 bis C7.

Auf dem Blatt Bestellung stehen in Zeile 1 die Überschriften Artikelnr, Bezeichnung, Menge, Preis und Gesamt. In A2 steht die Artikelnummer 1004, in C2 die Menge 3. Bezeichnung und Preis soll Excel selbst holen.

SVERWEIS Schritt für Schritt schreiben

1. Formel beginnen. Klicke auf dem Blatt Bestellung in D2 und tippe =SVERWEIS(.
2. Suchwert angeben. Klicke auf A2 und tippe ein Semikolon.
3. Bereich markieren. Wechsle auf das Blatt Preise und markiere A2 bis C7. In der Formel steht jetzt Preise!A2:C7.
4. Bereich festsetzen. Drücke sofort F4. Aus dem Bezug wird Preise!$A$2:$C$7. Tippe ein Semikolon.
5. Spalte nennen. Der Preis steht in der dritten Spalte des markierten Bereichs. Tippe 3 und ein Semikolon.
6. Genaue Suche wählen. Tippe FALSCH, schließe die Klammer und drücke Enter.

In D2 steht jetzt:

=SVERWEIS(A2;Preise!$A$2:$C$7;3;FALSCH)

Das Ergebnis ist 24,90. Für die Bezeichnung in B2 nimmst du dieselbe Formel mit einer 2 statt der 3 und erhältst Lampe. In E2 rechnest du =C2*D2 und bekommst 74,70 €. Ziehe alle drei Formeln so weit herunter, wie die Bestellung Zeilen hat.

Der Spaltenindex zählt innerhalb des Bereichs, nicht auf dem Blatt. Beginnt dein Bereich in Spalte D, ist D die 1, E die 2 und F die 3. Das ist die zweithäufigste Fehlerquelle nach dem vergessenen FALSCH.

gratis für dich

Neue Ideen, einmal pro Woche

Kein Spam, jederzeit abbestellbar.

E-Mail-Adresse Ideen holen → Ja, ich möchte den Newsletter. Es gelten die Datenschutzhinweise. Double-Opt-In, jederzeit abbestellbar.

Warum du FALSCH nie weglassen solltest

Lässt du das vierte Argument weg, nimmt Excel WAHR an. Dann sucht die Funktion nicht nach dem genauen Wert, sondern nach dem größten Wert, der kleiner oder gleich dem Suchwert ist. Das setzt eine aufsteigend sortierte Liste voraus. Ist die Liste nicht sortiert, liefert die Formel irgendein Ergebnis. Ohne Fehlermeldung.

Ein Beispiel: Du suchst die Artikelnummer 1007, die es nicht gibt. Mit FALSCH bekommst du #NV und weißt Bescheid. Ohne FALSCH bekommst du 15,50, den Preis der Vase, weil 1006 die größte Nummer unter 1007 ist. Auf der Rechnung steht dann ein Artikel, den niemand bestellt hat. Statt FALSCH darfst du auch eine 0 schreiben, das spart Tipparbeit: =SVERWEIS(A2;Preise!$A$2:$C$7;3;0).

Wofür ist die ungefähre Suche gut?

Es gibt einen Fall, in dem WAHR genau richtig ist: Staffeln. Rabatt nach Menge, Versandkosten nach Gewicht, Noten nach Punkten. Hier suchst du keinen genauen Wert, sondern die Stufe, in die er fällt.

Die Staffel steht in H2 bis I5. Mit der Menge in C2 lautet die Formel =SVERWEIS(C2;$H$2:$I$5;2;WAHR). Bei 40 Stück findet sie die 10 als größte Grenze unter 40 und liefert 5 Prozent. Bei genau 50 Stück liefert sie 10 Prozent, bei 120 Stück 15 Prozent.

Rechne ein Beispiel durch: 40 Ordner zu 2,50 € kosten 100,00 €. Abzüglich 5 Prozent bleiben 95,00 €. Als Formel in einer Zeile: =C2*D2*(1-SVERWEIS(C2;$H$2:$I$5;2;WAHR)).

Zwei Bedingungen müssen stimmen. Die erste Spalte ist aufsteigend sortiert, und die erste Stufe beginnt bei 0 oder beim kleinsten möglichen Wert. Fehlt die Null, liefert die Formel für 5 Stück den Fehler #NV, weil es keine Grenze unter 5 gibt.

Warum zeigt der SVERWEIS #NV?

#NV heißt nicht vorhanden. Die Funktion hat den Suchwert in der ersten Spalte nicht gefunden. In neun von zehn Fällen liegt es an einer dieser Ursachen.

Die Sache mit Zahl und Text ist die tückischste. Artikelnummern aus einem Warenwirtschaftsprogramm kommen oft als Text an. Die 1004 sieht aus wie eine Zahl, ist aber für Excel eine Zeichenkette. Tippst du in der Bestellung 1004 als Zahl ein, findet der SVERWEIS sie nicht. Multiplizierst du den Suchwert mit 1, wird aus Text eine Zahl. Hängst du mit dem kaufmännischen Und zwei Anführungszeichen an, wird aus einer Zahl Text. Welche Richtung du brauchst, sagt dir die Ausrichtung in der Preisliste.

Fehler abfangen, aber richtig

Ist die Zelle mit der Artikelnummer noch leer, zeigt die Formel #NV. Das sieht unschön aus und zerstört jede Summe darunter. Zwei Funktionen fangen das ab:

=WENNNV(SVERWEIS(A2;Preise!$A$2:$C$7;3;FALSCH);"")

WENNNV gibt es ab Excel 2013. Die Funktion ersetzt nur den Fehler #NV. Das bekanntere WENNFEHLER schluckt dagegen jeden Fehler, auch einen falsch geschriebenen Funktionsnamen oder einen gelöschten Bezug. Nimm deshalb WENNNV, wenn deine Version es kennt. Und baue die Formel immer erst ohne, bis sie stimmt.

Liefert die Formel #BEZUG!, ist der Spaltenindex größer als die Zahl der Spalten im Bereich. Bei einem Bereich von A bis C gibt es keine Spalte 4.

SVERWEIS mit Tabelle statt Zellbereich

Kommt ein siebter Artikel dazu, endet der Bereich weiter bei Zeile 7, und der neue Artikel wird nicht gefunden. Es gibt zwei Wege, das zu vermeiden. Der schnelle: ganze Spalten angeben, also =SVERWEIS(A2;Preise!A:C;3;FALSCH). Der saubere: die Preisliste mit Strg + T in eine echte Tabelle umwandeln und ihr den Namen Preise geben. Dann lautet die Formel =SVERWEIS(A2;Preise;3;FALSCH). Der Name steht für alle Datenzeilen und wächst mit. Wie das geht, zeigt der Beitrag Tabelle in Excel erstellen.

Die Spaltennummer automatisch bestimmen

Die feste 3 in der Formel ist eine Schwachstelle. Fügt jemand in der Preisliste zwischen Bezeichnung und Preis eine Spalte ein, liefert die Formel ab sofort deren Inhalt. Auch hier ohne Fehlermeldung. Robuster ist es, die Spalte über ihre Überschrift zu suchen:

=SVERWEIS($A2;Preise!$A$1:$C$7;VERGLEICH(D$1;Preise!$A$1:$C$1;0);FALSCH)

VERGLEICH sucht die Überschrift aus D1 der Bestellung, also das Wort Preis, in der Kopfzeile der Preisliste und liefert ihre Position: 3. Voraussetzung ist, dass die Überschriften auf beiden Blättern gleich lauten. Der Bereich beginnt jetzt in Zeile 1, damit die Kopfzeile dazugehört. Durch die gemischten Bezüge kannst du die Formel nach rechts und nach unten ziehen.

Nach links suchen: INDEX, VERGLEICH und XVERWEIS

Der SVERWEIS schaut nur nach rechts. Willst du zur Bezeichnung Lampe die Artikelnummer finden, steht das Ergebnis links von der Suchspalte, und die Funktion ist am Ende. Die klassische Lösung kombiniert zwei Funktionen:

=INDEX(Preise!$A$2:$A$7;VERGLEICH("Lampe";Preise!$B$2:$B$7;0))

VERGLEICH findet Lampe an Position 4 der Spalte B. INDEX holt den vierten Eintrag aus Spalte A: 1004. Die 0 am Ende von VERGLEICH entspricht dem FALSCH im SVERWEIS und steht für die genaue Suche.

In Excel 2021 und Microsoft 365 erledigt das der XVERWEIS in einem Stück: =XVERWEIS("Lampe";Preise!$B$2:$B$7;Preise!$A$2:$A$7;"nicht gefunden"). Suchspalte und Ergebnisspalte gibst du getrennt an, die Richtung ist egal, die genaue Suche ist voreingestellt, und der Text für den Fehlerfall ist gleich eingebaut.

Meine Empfehlung: Gibst du Dateien an Leute mit älteren Versionen weiter, bleib beim SVERWEIS oder bei INDEX mit VERGLEICH. Arbeitest du nur für dich mit einer aktuellen Version, nimm den XVERWEIS. Er ist kürzer und verzeiht mehr.

Suchen mit zwei Kriterien

Manchmal reicht ein Suchwert nicht. Ein Kissen gibt es in zwei Größen mit zwei Preisen. Der SVERWEIS kann nur ein Kriterium. Die einfachste Lösung ist eine Hilfsspalte, die beide Merkmale verbindet.

1. Füge in der Preisliste ganz links eine neue Spalte A ein.
2. Schreibe in A2 die Formel =B2&"|"&C2, wobei B den Artikel und C die Größe enthält. Das Ergebnis lautet etwa Kissen|40×40.
3. Ziehe die Formel bis ans Ende der Liste.
4. Suche in der Bestellung mit dem verbundenen Wert: =SVERWEIS(B2&"|"&C2;Preise!$A$2:$E$20;5;FALSCH).

Der senkrechte Strich als Trenner verhindert Verwechslungen. Ohne ihn wären Kissen4 plus 0x40 und Kissen plus 40×40 derselbe Text.

4 Dinge, die oft überraschen

1. Nur der erste Treffer zählt. Steht eine Artikelnummer zweimal in der Liste, liefert der SVERWEIS immer die obere Zeile. Prüfe Dubletten vorher mit =ZÄHLENWENN(Preise!$A$2:$A$7;A2). Steht dort mehr als 1, gibt es den Wert mehrfach.
2. Groß- und Kleinschreibung ist egal. lampe findet Lampe.
3. Platzhalter funktionieren. Mit genauer Suche findet =SVERWEIS("Lam*";Preise!$B$2:$C$7;2;FALSCH) den ersten Eintrag, der mit Lam beginnt, und liefert 24,90.
4. Leere Ergebniszellen werden zu 0. Ist die Preiszelle in der Liste leer, zeigt die Formel 0 und nicht nichts. Für Preise ist das meist in Ordnung, bei Texten irritiert es.

Hilfsmittel für die Arbeit mit Verweisen

Der SVERWEIS braucht nur Excel. Wer oft zwischen zwei Listen hin und her schaut oder Nummern eintippt, arbeitet mit diesen Dingen entspannter.

ASUS VY249HGR, 24 Zoll Full HD IPS Monitor

Bei Verweisen schaust du ständig auf zwei Listen. Auf einem zweiten Bildschirm liegen Preisliste und Bestellung nebeneinander. Ein 24-Zoll-Monitor mit IPS-Panel, 1920 x 1080 Pixeln, HDMI und VESA-Aufnahme 100 x 100 laut Hersteller.

Der Haken: Full HD auf 24 Zoll zeigt nicht mehr Spalten als ein Full-HD-Notebook, nur größer. Für mehr Fläche brauchst du eine höhere Auflösung.

LogiLink ID0184 Ziffernblock mit USB-Kabel

Artikelnummern sind reine Ziffernfolgen. Mit einem Ziffernblock tippst du sie deutlich schneller als über die obere Tastenreihe. Eine numerische Zusatztastatur mit USB-Anschluss und LED-Anzeige laut Anbieter. Einstecken genügt.

Der Haken: Das Kabel belegt einen USB-Anschluss und liegt auf dem Schreibtisch. An Notebooks mit nur zwei Buchsen wird es schnell eng.

Excel: Formeln und Funktionen (Ausgabe 2, zu Excel 2024)

SVERWEIS, INDEX, VERGLEICH und XVERWEIS mit Beispielen zum Nacharbeiten. Der Titel verspricht verständliche Anleitungen und Beispiele für schnelle Lösungen, aktuell zu Excel 2024, älteren Versionen und Microsoft 365.

Der Haken: Es ist ein reines Formelbuch. Diagramme, Druck und Layout kommen darin kaum vor.

Microsoft 365 Excel Formeln und Funktionen für Dummies

Für Einsteiger, denen die Formelhilfe von Excel zu knapp ist. Ein Einsteigerbuch nur zu Formeln und Funktionen in Excel für Microsoft 365.

Der Haken: Der lockere Ton der Reihe ist Geschmackssache, und wer Excel 2016 oder 2019 nutzt, findet nicht jede beschriebene Funktion.

Was passt dazu?

Am häufigsten arbeitet der SVERWEIS zusammen mit einer Auswahlliste: Du wählst den Artikel per Klick, und der Preis erscheint. Den ersten Teil erklärt Dropdown in Excel erstellen. Ein Beispiel aus dem Alltag ist das Haushaltsbuch in Excel, in dem der Verweis zu jeder Kategorie die Art nachschlägt. Alle verwandten Funktionen stehen in der Übersicht der Excel Formeln.

Häufige Fragen

Dieser Beitrag wurde mit KI-Unterstützung erstellt und redaktionell geprüft.

*Werbung · Als Amazon-Partner verdiene ich an qualifizierten Verkäufen — für dich ohne Mehrkosten.