So verwenden Sie SVERWEIS für einen Wertebereich

Die Funktion SVERWEIS in Excel ist weithin bekannt und wird oft genutzt, um exakte Übereinstimmungen zu finden, beispielsweise Produkt- oder Kunden-IDs. Dieser Artikel zeigt jedoch, wie SVERWEIS auch für die Suche in Wertebereichen eingesetzt werden kann.

Anwendungsbeispiel 1: Notenvergabe mit SVERWEIS

Nehmen wir an, wir haben eine Liste von Prüfungsergebnissen und möchten diese in Noten umwandeln. In unserer Tabelle repräsentiert Spalte A die tatsächlichen Prüfungsergebnisse, während Spalte B die daraus resultierenden Noten aufnehmen soll. Zusätzlich haben wir eine Hilfstabelle (Spalten D und E) erstellt, die die erforderliche Punktzahl für jede Note definiert.

Mithilfe von SVERWEIS können wir die in Spalte D definierten Wertebereiche nutzen, um den Prüfungsergebnissen in Spalte A die entsprechenden Noten aus Spalte E zuzuordnen.

Die SVERWEIS-Formel im Detail

Bevor wir die Formel in unserem konkreten Beispiel anwenden, wiederholen wir kurz die SVERWEIS-Syntax:

=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)

Die einzelnen Parameter haben folgende Bedeutung:

  • Suchkriterium: Der Wert, nach dem gesucht wird. In unserem Fall ist dies die Punktzahl in Spalte A, beginnend mit Zelle A2.
  • Matrix: Die Tabelle, in der die Daten gesucht werden. In unserem Fall die Tabelle mit den Notengrenzen und den zugehörigen Noten (Bereich D2:E7).
  • Spaltenindex: Die Nummer der Spalte in der Matrix, die den Rückgabewert enthält. In unserem Beispiel ist dies die zweite Spalte (E), also 2.
  • Bereich_Verweis: Ein logischer Wert (WAHR oder FALSCH). Gibt an, ob eine ungefähre Übereinstimmung (WAHR) oder eine exakte Übereinstimmung (FALSCH) gesucht wird. Hier wählen wir WAHR für eine Bereichssuche.

Die vollständige Formel für unser Beispiel lautet:

=SVERWEIS(A2;$D$2:$E$7;2;WAHR)

Beachten Sie, dass die Matrix (D2:E7) mit absoluten Bezügen versehen wurde ($), sodass sie sich beim Kopieren der Formel in die Zellen der Spalte B nicht ändert.

Wichtige Hinweise zur Bereichssuche mit SVERWEIS

Bei der Bereichssuche mit SVERWEIS ist es entscheidend, dass die erste Spalte der Suchmatrix (in diesem Fall Spalte D) aufsteigend sortiert ist. Die Formel ist auf diese Sortierung angewiesen, um den Suchwert dem korrekten Bereich zuzuordnen.

Das folgende Bild demonstriert die Ergebnisse, die wir erhalten würden, wenn die Sortierung in der Matrix nach Notenbuchstaben anstatt nach Punktzahlen erfolgt:

Es ist wichtig zu verstehen, dass die Sortierung nur bei Bereichssuchen relevant ist. Wenn Sie am Ende einer SVERWEIS-Funktion FALSCH verwenden, ist die Sortierung nicht wichtig.

Anwendungsbeispiel 2: Rabattgewährung basierend auf dem Einkaufswert

In diesem Beispiel haben wir Verkaufsdaten. Wir möchten einen Rabatt auf den Verkaufsbetrag gewähren, wobei der Rabattsatz vom ausgegebenen Betrag abhängt.

Eine Hilfstabelle (Spalten D und E) enthält die Rabattsätze für die verschiedenen Ausgabegruppen.

Die folgende SVERWEIS-Formel kann verwendet werden, um den korrekten Rabatt aus der Tabelle zu ermitteln:

=SVERWEIS(A2;$D$2:$E$7;2;WAHR)

Dieses Beispiel ist besonders interessant, da wir die Funktion direkt in einer Formel verwenden können, um den Rabatt zu berechnen.

Oftmals nutzen Excel-Anwender komplizierte Formeln, um derartige bedingte Logiken umzusetzen, aber dieser SVERWEIS bietet eine einfache Möglichkeit, dies zu erreichen.

Im Folgenden wird der SVERWEIS in einer Formel verwendet, um den ermittelten Rabatt vom Verkaufsbetrag in Spalte A abzuziehen:

=A2-A2*SVERWEIS(A2;$D$2:$E$7;2;WAHR)

SVERWEIS ist nicht nur dann nützlich, wenn Sie nach bestimmten Datensätzen wie Mitarbeitern oder Produkten suchen. Die Funktion ist viel vielseitiger, als viele Anwender annehmen. Die Anwendung auf Bereiche ist ein gutes Beispiel dafür. Sie können SVERWEIS auch als Alternative zu komplizierten Formeln einsetzen.

Weitere Informationen zu SVERWEIS.