Start › Excel-Funktionen › SVERWEIS › Ungefähre Übereinstimmung
SVERWEIS mit ungefährer Übereinstimmung: so funktioniert WAHR
Meistens soll SVERWEIS eine genaue Übereinstimmung finden. Bei Rabatten, Versandkosten, Steuerstufen oder Noten liegt Ihr Wert aber oft zwischen zwei Zeilen der Tabelle. Dafür ist die ungefähre Übereinstimmung da: SVERWEIS findet die Stufe, zu der Ihr Wert gehört.
SVERWEIS · In dieser Anleitung
So funktioniert die ungefähre Übereinstimmung
Die ungefähre Übereinstimmung ist das vierte Argument von SVERWEIS auf WAHR gesetzt oder weggelassen:
=SVERWEIS(D2; A2:B6; 2; WAHR)SVERWEIS geht dann die erste Spalte durch und hält beim letzten Wert an, der nicht größer ist als Ihr Suchwert. Es gibt den Wert dieser Zeile zurück. Sehen Sie sich diese Rabatttabelle an:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Menge ab | Rabatt | Menge | Rabatt | |
| 2 | 1 | 0% | 36 | 10% | |
| 3 | 10 | 5% | |||
| 4 | 25 | 10% | |||
| 5 | 50 | 15% | |||
| 6 | 100 | 20% |
Bei einer Menge von 36 geht SVERWEIS an 1, 10 und 25 vorbei, sieht, dass 50 zu groß ist, und geht zur Zeile mit 25 zurück. Der Rabatt beträgt 10%. Alle Mengen von 25 bis 49 erhalten 10 %, ab 50 sind es 15 %.
Jede Zeile ist der Beginn einer Stufe. Tippen Sie den kleinsten Wert jeder Stufe in die erste Spalte: 1, 10, 25 und so weiter. Eine Spalte „bis“ brauchen Sie nicht: Die nächste Zeile markiert das Ende der Stufe.
Die erste Spalte muss sortiert sein
Die ungefähre Übereinstimmung funktioniert nur, wenn die erste Spalte aufsteigend sortiert ist. SVERWEIS prüft nicht alle Zeilen; es geht davon aus, dass die Reihenfolge stimmt, und hört früher auf. Bei einer unsortierten Tabelle gibt es einen falschen Wert zurück, ohne Warnung.
Prüfen Sie auch, ob die erste Zeile den kleinsten möglichen Wert abdeckt. Bestellt jemand 0 Stück und Ihre Tabelle beginnt bei 1, findet SVERWEIS nichts Kleineres und ergibt #NV. Lassen Sie die Tabelle bei 0 beginnen, um das zu vermeiden.
Übung: SVERWEIS mit ungefährer Übereinstimmung
Suchen Sie den Rabatt für eine Bestellung von 36 Stück. Die Tipps führen Sie durch jeden Teil der Formel.
Übung: Tippen Sie in Zelle E2 =SVERWEIS(D2;A2:B6;2;WAHR), um den Rabatt zu finden. Aktivieren Sie JavaScript, um das interaktiv zu tun.
Weitere Beispiele
Noten aus Punkten. Eine Tabelle mit 0, 50, 60, 70 und 85 in der ersten Spalte und den Noten 5, 4, 3, 2 und 1 daneben. =SVERWEIS(B2; $E$2:$F$6; 2; WAHR) macht aus 78 Punkten die Note 2.
Versandkosten nach Gewicht. Gewichtsstufen ab 0, 2, 5, 10 und 20 kg mit einem Preis pro Stufe. Ein Paket mit 7 kg erhält den Preis der 5-kg-Stufe.
Steuerstufen. Die Einkommensgrenzen in der ersten Spalte und der Satz daneben. Für eine progressive Steuer, bei der jede Stufe ihren eigenen Satz hat, braucht es mehr als einen Verweis, aber für einen festen Satz pro Stufe genügt ein SVERWEIS.
Genaue oder ungefähre Übereinstimmung?
| Genaue Übereinstimmung (FALSCH) | Ungefähre Übereinstimmung (WAHR) | |
|---|---|---|
| Verwenden für | Namen, Nummern, IDs, Produkte | Stufen: Mengen, Beträge, Punkte, Datumswerte |
| Sortierung nötig | Nein | Ja, aufsteigend |
| Wert nicht gefunden | #NV | Die Stufe darunter |
| Standard | Nein, Sie müssen FALSCH tippen | Ja, wenn Sie das Argument weglassen |
Das letzte Argument wegzulassen bedeutet WAHR. Suchen Sie Namen oder Nummern und vergessen das FALSCH, verwendet SVERWEIS die ungefähre Übereinstimmung und kann einen Wert aus der falschen Zeile zurückgeben. Tippen Sie immer FALSCH, es sei denn, Sie wollen wirklich Stufen.
In Excel 365 können Sie auch XVERWEIS mit dem Vergleichsmodus -1 („genaue Übereinstimmung oder nächstkleineres Element“) verwenden. Das tut dasselbe wie die ungefähre Übereinstimmung, funktioniert aber auch bei unsortierten Daten.
Mehr zu SVERWEIS
Häufige Fragen
Was bedeutet WAHR in SVERWEIS?
WAHR verlangt eine ungefähre Übereinstimmung: SVERWEIS gibt die Zeile mit dem größten Wert zurück, der Ihren Suchwert nicht übersteigt. FALSCH verlangt eine genaue Übereinstimmung.
Ist die ungefähre Übereinstimmung der Standard von SVERWEIS?
Ja. Lassen Sie das vierte Argument weg, verwendet SVERWEIS die ungefähre Übereinstimmung. Deshalb tippen Sie besser immer FALSCH, wenn Sie eine genaue Übereinstimmung wollen.
Warum gibt die ungefähre Übereinstimmung von SVERWEIS einen falschen Wert zurück?
Fast immer, weil die erste Spalte nicht aufsteigend sortiert ist. Sortieren Sie die Tabelle nach dieser Spalte, dann stimmen die Ergebnisse.
Kann man die ungefähre Übereinstimmung mit Text verwenden?
Sie funktioniert alphabetisch, ist aber selten nützlich: „Pflaume“ würde zur Zeile „Pfirsich“ passen, wenn „Pflaume“ selbst fehlt. Verwenden Sie für Text FALSCH.