SVERWEIS mit WAHR: Werte aus einer Staffel holen
Mit WAHR am Ende sucht SVERWEIS nicht den genauen Wert, sondern die passende Stufe einer Tabelle. So findest du zum Beispiel den Provisionssatz für 37.500 € Umsatz, obwohl diese Zahl in der Staffel gar nicht vorkommt.
Kurz gesagt: =SVERWEIS(Suchwert;Staffel;Spalte;WAHR). Excel nimmt die letzte Zeile, deren Wert in der ersten Spalte kleiner oder gleich dem Suchwert ist. Dafür muss die erste Spalte aufsteigend sortiert sein und die Untergrenze jeder Stufe enthalten.
Probier es gleich aus
Links steht eine Provisionsstaffel. Jede Zeile gilt ab dem Umsatz in Spalte A. Gesucht ist der Provisionssatz für den Umsatz in F2. Klick in die gelbe Zelle und schreib die Formel.
Aufgabe Links steht die Provisionsstaffel: ab 0 € Umsatz gibt es 1 %, ab 10.000 € 2 % und so weiter. Hol in G2 mit SVERWEIS den Provisionssatz, der zum Umsatz in F2 passt.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | ab Umsatz € | Provisionssatz | Verkäuferin | Umsatz € | Provisionssatz | ||
| 2 | 0 | 0,01 | Lena Hoffmann | 37500 | |||
| 3 | 10000 | 0,02 | |||||
| 4 | 25000 | 0,03 | |||||
| 5 | 50000 | 0,04 | |||||
| 6 | 100000 | 0,05 |
So funktioniert die ungefähre Übereinstimmung
Bei der genauen Suche mit FALSCH muss der Suchwert exakt in der ersten Spalte stehen, sonst kommt #NV. Bei Staffeln ist das nie der Fall: Niemand pflegt eine Zeile für jeden möglichen Umsatz. Stattdessen steht in der Tabelle nur, ab welchem Wert eine Stufe beginnt.
| Argument | Bedeutung | Im Beispiel |
|---|---|---|
| Suchkriterium | Der Wert, für den du die Stufe suchst | F2 (37.500 € Umsatz) |
| Matrix | Die Staffel. Erste Spalte: Untergrenzen, aufsteigend sortiert | A2:B6 |
| Spaltenindex | Aus welcher Spalte der Staffel das Ergebnis kommt | 2 (Provisionssatz) |
| Bereich_Verweis | WAHR oder weglassen für die ungefähre Suche | WAHR |
Excel geht gedanklich die erste Spalte von oben nach unten durch und bleibt bei der letzten Stufe stehen, die den Suchwert nicht übersteigt. Für 37.500 sind das 0, 10.000 und 25.000. Die nächste Stufe, 50.000, ist zu groß. Also gilt die Zeile mit 25.000 und die Formel liefert 3 %.
Liegt ein Wert genau auf einer Grenze, gehört er zur höheren Stufe. 25.000 € Umsatz ergeben also schon 3 %, nicht 2 %. Die Zahl in Spalte A bedeutet immer „ab“.
Warum die Staffel sortiert sein muss
Excel liest die Tabelle bei der ungefähren Suche nicht Zeile für Zeile. Es prüft zuerst einen Wert in der Mitte, entscheidet, ob es oben oder unten weitersucht, und halbiert den Bereich immer wieder. Diese Binärsuche ist schnell, funktioniert aber nur, wenn die Werte von klein nach groß sortiert sind.
Ist die Staffel durcheinander, springt Excel an die falsche Stelle und gibt eine falsche Stufe zurück. Das Gemeine daran: Es erscheint keine Fehlermeldung. Microsoft schreibt in der Hilfe zu SVERWEIS ausdrücklich, dass die erste Spalte bei WAHR aufsteigend sortiert sein muss, weil das Ergebnis sonst falsch sein kann. Prüf deine Staffel deshalb immer, bevor du ihr vertraust.
Noch eine Übung: Rabatt für jede Bestellung
Hier holst du den Rabatt für eine ganze Liste von Bestellungen. Die Formel wird nach unten ausgefüllt, also muss die Staffel mit Dollarzeichen fixiert werden. Wie das geht, steht ausführlich bei den absoluten Bezügen.
Aufgabe Rechts steht die Rabattstaffel. Hol in C2 mit SVERWEIS den Rabatt für den Bestellwert in B2. Die Formel wird danach bis C7 ausgefüllt und muss in jeder Zeile stimmen.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Bestellung | Bestellwert € | Rabatt | ab Bestellwert € | Rabatt | |
| 2 | B-2041 | 180 | 0 | 0 | ||
| 3 | B-2042 | 1240 | 250 | 0,02 | ||
| 4 | B-2043 | 499,9 | 500 | 0,05 | ||
| 5 | B-2044 | 2750 | 1000 | 0,08 | ||
| 6 | B-2045 | 500 | 2500 | 0,1 | ||
| 7 | B-2046 | 86 |
Typische Staffeln aus dem Büroalltag
- Provision: Umsatzgrenzen in Spalte A, Provisionssätze in Spalte B. Die Provision in Euro ist dann
=F2*SVERWEIS(F2;A2:B6;2;WAHR). - Mengenrabatt: Bestellwert oder Stückzahl ab der ein Rabatt gilt.
- Versandkosten: Gewichtsklassen ab 0 kg, 2 kg, 5 kg und so weiter.
- Kundenklassen: Jahresumsatz ab dem ein Kunde Bronze, Silber oder Gold ist. Das Ergebnis darf auch Text sein.
- Noten und Bewertungen: Punktzahl ab der eine Note oder Stufe erreicht ist.
Eine Staffeltabelle hat einen großen Vorteil gegenüber verschachtelten WENN-Formeln: Ändert sich eine Grenze, passt du eine Zelle an und alle Formeln rechnen sofort richtig weiter.
Dritte Übung: Versandkosten nach Gewicht
Diesmal hat die Tabelle drei Spalten und der Preis steht in der dritten. Probier, das vierte Argument wegzulassen. Ohne Angabe sucht SVERWEIS automatisch ungefähr.
Aufgabe Die Versandkosten hängen vom Gewicht ab. Hol in F2 mit SVERWEIS den Preis für das Paket, dessen Gewicht in E2 steht. Der Preis steht in der dritten Spalte der Tabelle.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ab kg | Paketklasse | Preis € | Gewicht kg | Versand € | |
| 2 | 0 | S | 4,9 | 7,4 | ||
| 3 | 2 | M | 6,9 | |||
| 4 | 5 | L | 8,9 | |||
| 5 | 10 | XL | 12,9 | |||
| 6 | 20 | XXL | 24,9 |
Typische Fehler bei der Staffelsuche
Die Staffel ist nicht aufsteigend sortiert
Der häufigste Fehler, und er fällt oft lange nicht auf. Sortiere die Tabelle über Daten > Sortieren nach der ersten Spalte von klein nach groß.
#NV bei kleinen Werten
Ist der Suchwert kleiner als die erste Grenze, findet SVERWEIS keine passende Stufe und meldet #NV. Beginn die Staffel deshalb mit 0, auch wenn es in dieser Stufe 0 % Rabatt gibt.
Obergrenzen statt Untergrenzen
Viele Staffeln sind als „bis 10.000 €“ formuliert. SVERWEIS braucht aber die Untergrenze jeder Stufe. Schreib die Tabelle um: Aus „bis 10.000: 1 %, bis 25.000: 2 %“ wird „ab 0: 1 %, ab 10.000: 2 %“. Achte dabei darauf, ob genau 10.000 noch zur unteren oder schon zur oberen Stufe gehören soll.
Zahlen als Text gespeichert
Stehen die Grenzen als Text in den Zellen, etwa nach einem Import, vergleicht Excel nicht mehr Zahl mit Zahl. Erkennbar ist das oft an linksbündigen Zahlen. Wandle sie in echte Zahlen um.
WAHR bei Artikelnummern
Die ungefähre Suche ist nur für Stufen gedacht. Für Artikelnummern, Namen und IDs brauchst du die genaue Suche mit FALSCH, wie im Grundlagenartikel zum SVERWEIS erklärt.
Alternativen zu SVERWEIS mit WAHR
XVERWEIS hat einen eigenen Vergleichsmodus für Staffeln: =XVERWEIS(F2;A2:A6;B2:B6;;-1). Die -1 bedeutet „genau oder nächstkleinerer Wert“. Laut Microsoft braucht dieser Modus keine sortierte Liste, nur die Binärsuche von XVERWEIS setzt eine Sortierung voraus. Mit INDEX und VERGLEICH geht es ebenfalls: =INDEX(B2:B6;VERGLEICH(F2;A2:A6;1)). Hier gilt wieder: Die 1 bei VERGLEICH verlangt aufsteigend sortierte Werte.
SVERWEIS mit WAHR auf Englisch
Im englischen Excel heißt die Funktion VLOOKUP, WAHR heißt TRUE, und die Argumente werden mit Komma getrennt:
| Deutsch | Englisch |
|---|---|
=SVERWEIS(F2;A2:B6;2;WAHR) | =VLOOKUP(F2,A2:B6,2,TRUE) |
| ungefähre Übereinstimmung | approximate match |
| XVERWEIS, INDEX, VERGLEICH | XLOOKUP, INDEX, MATCH |
In den Übungen kannst du beide Schreibweisen verwenden.
In welchen Excel-Versionen funktioniert das?
SVERWEIS mit ungefährer Übereinstimmung gibt es in allen Excel-Versionen, auch in Excel 2016 und 2019, in Excel für Mac und in Excel für das Web. Die Alternative mit XVERWEIS steht nur in Microsoft 365, Excel 2021 und Excel 2024 zur Verfügung. INDEX mit VERGLEICH läuft wieder überall.
Häufige Fragen
Was bedeutet WAHR bei SVERWEIS?
WAHR schaltet die ungefähre Übereinstimmung ein. SVERWEIS liefert dann die Zeile mit dem größten Wert, der kleiner oder gleich dem Suchwert ist. FALSCH steht für die genaue Suche.
Muss ich WAHR hinschreiben?
Nein. Lässt du das vierte Argument weg, sucht SVERWEIS ebenfalls ungefähr. Mit WAHR ist die Formel aber für andere leichter zu lesen, und niemand hält das fehlende Argument für einen Fehler.
Warum liefert SVERWEIS mit WAHR ein falsches Ergebnis?
Fast immer ist die erste Spalte nicht aufsteigend sortiert, oder die Tabelle enthält Obergrenzen statt Untergrenzen. Sortiere die Staffel und prüf, ob jede Zeile den Startwert ihrer Stufe enthält.
Zu welcher Stufe gehört ein Wert genau auf der Grenze?
Zur höheren. Steht in der Staffel 10.000, gilt diese Zeile ab genau 10.000.
Geht das nicht auch mit WENN?
Ja, mit verschachtelten WENN- oder WENNS-Formeln. Bei mehr als zwei, drei Stufen wird das aber unübersichtlich, und jede Änderung einer Grenze heißt, alle Formeln anzufassen. Die Staffeltabelle mit SVERWEIS bleibt pflegeleicht.