SVerweis: Automatische Wertesuche in Excel mit mehreren Kriterien

Die Funktion SVerweis eignet sich hervorragend, wenn es darum geht, bestimmte Inhalte einer Tabelle auszulesen. Leider hat die Suchfunktion eine zentrale Einschränkung: Mit SVerweis können nur Suchbegriffe in einer einzigen Spalte gesucht werden. Befinden sich die Suchbegriffe aber in 2 oder mehr Spalten, so muss die Suche über eine andere Funktionalität durchgeführt werden. Das kann beispielsweise über eine geschachtelte WENN-Abfrage gelöst werden oder auf elegantere Art in Kombination mit der Funktion WAHL.

25.05.2021 Von: Frédéric Jordan

Passende Arbeitshilfen

zip

Download-Paket Top-Tools für erfolgreiches Controlling

CHF 100.00

xls

Betriebs- und finanzwirtschaftliche Schwachstellenanalyse

CHF 90.00

xls

Rechner Plankostenrechnung

CHF 50.00

xls

Vorlage Planung und Kontrolle Projektkosten

CHF 40.00

xlsm

Muster Kennzahlensystem

CHF 40.00

Funktion =SVerweis()

Der SVerweis ist der Klassiker unter den Excel-Funktionen. Damit lassen sich Elemente in einer Tabelle oder einem Bereich für eine bestimmte Zelle suchen. Als Beispiel, sie suchen das Herkunftsland einer bestimmten Gemüsesorte anhand eines Landescodes.

Aufbau

=SVerweis(Suchkriterium; Matrix; Spalte; Übereinstimmung)

Beispiel

Das Grundgerüst, mit welchem wir arbeiten, ist eine Datenmatrix (vgl. Beispiel 1, Abbildung 1).
Wir möchten den Wert einer bestimmten Kennung finden. Dafür ergänzen wir die Datenmatrix um den gesuchten Wert und eine Zelle für die Ausgabe (vgl. Beispiel 1, Abbildung 2).
In der Zelle A11 tragen wir als Kennung den Buchstaben "E" für den gesuchten Wert ein (vgl. Beispiel 1, Abbildung 3).
Wir benötigen die nachfolgende Formel, welche in die Zelle B11 (vgl. Beispiel 1, Abbildung 4) einzutragen ist: =SVerweis(A11;A2;B7;2)
Damit erhalten wir das korrekte Resultat von 38303, welches der gesuchte Wert für die Kennung "E" ist.

Fehlerquellen

Obschon die Funktion an sich nicht schwer ist, kann es zu verschiedenen Fehlern kommen. Nachfolgend ein paar der häufigeren Probleme. Gerade für den Excel-Einsteiger sind manche Ausführungen nicht sofort zu verstehen. Dennoch kann es eine gute Hilfe darstellen, sobald man auf einer der Punkt stösst.

Problem	Ursache
Falscher Wert zurückgegeben	Wenn Bereich_Verweis als WAHR oder gar nicht angegeben ist, muss die erste Spalte alphabetisch oder numerisch sortiert sein. Ist die erste Spalte nicht sortiert, kann es sich beim Rückgabewert um einen unerwarteten Wert handeln. Sortieren Sie entweder die erste Spalte, oder verwenden Sie FALSCH für eine genaue Entsprechung.
#NV in der Zelle	Wenn Bereich_Verweis als WAHR angegeben ist und der Wert im Suchkriterium kleiner als der kleinste Wert in der ersten Spalte der Matrix ist, wird der Fehlerwert "#NV" zurückgegeben. Wenn Bereich_Verweis FALSCH ist, zeigt der Fehlerwert "#N/A" an, dass kein exakter Wert gefunden wurde.
#BEZUG! in der Zelle	Wenn Spaltenindex größer als die Anzahl der Spalten in Table-Arrayist, erhalten Sie die #Ref! zurück.
#WERT!- in der Zelle	Wenn die Matrix kleiner als 1 ist, erhalten Sie die #Value! zurück.
#NAME? in der Zelle	Der #NAME?- der Fehlerwert bedeutet normalerweise, dass die Formel keine Anführungszeichen enthält. Wenn Sie den Namen einer Person nachschlagen möchten, stellen Sie sicher, dass Sie den Namen in der Formel um Anführungszeichen verwenden.
#Spill! in der Zelle	Dieser besondere #Spill!-Fehler bedeutet normalerweise, dass Ihre Formel auf eine implizite Schnittmenge für den Nachschlagewert und die Verwendung einer ganzen Spalte als Referenz basiert. Beispiel: = SVerweis (A:A; A:C; 2; falsch). Sie können das Problem beheben, indem Sie den Nachschlage Verweis mit dem @-Operator wie folgt verankern: = SVerweis (@ A:A; A:C; 2; falsch). Alternativ können Sie die herkömmliche SVerweis-Methode verwenden und auf eine einzelne Zelle anstatt auf eine ganze Spalte verweisen: = SVerweis (a2; A:C; 2; falsch).

Passende Produkt-Empfehlungen

Planung & Unternehmenssteuerung

Das Controlling-Cockpit für die erfolgreiche Planung und Steuerung.

ab CHF 168.00

Newsletter Finanz- und Rechnungswesen

Aktuelles Praxiswissen aus dem Finanz- und Rechnungswesen mit Experten Know-How, Best Practice und Praxisbeispielen.

ab CHF 98.00

Finance Professional

Die Komplettlösung für Ihr Rechnungswesen und Controlling.

ab CHF 290.00

Controller Leitfaden

Das Standardwerk für wirksames Controlling und eine effektive Controller-Tätigkeit.

CHF 248.00

ab CHF 88.00

Bewährte Methoden

Aufgrund dessen, dass mit der Funktion SVerweis alles gesucht werden kann, auch mittels einer Formel anstelle eines absoluten Wertes gibt es manche Schwierigkeiten. Damit dies nicht geschieht, existieren einige Methoden, welche sich bewährt haben.

Aktion	Grund
Verwenden Sie absolute Bezüge für Bereich_Verweis.	Bei Verwendung absoluter Bezüge können Sie eine Formel nach unten ausfüllen, sodass immer auf genau den gleichen Nachschlagebereich verwiesen wird.
Speichern Sie keine Zahlen oder Datumswerten als Text.	Bei der Suche nach Zahlen oder Datumswerten müssen Sie sicherstellen, dass die Daten in der ersten Spalte der *Matrix* nicht als Textwerte gespeichert sind. Andernfalls gibt SVERWEIS möglicherweise einen falschen oder unerwarteten Wert zurück.
Sortieren Sie die erste Spalte.	Sortieren Sie die erste Spalte der Matrix, bevor Sie SVERWEIS verwenden, wenn Bereich_Verweis WAHR ist.
Verwenden Sie Platzhalterzeichen.	Wenn range_lookup ist und Kriterium Text ist, können Sie die Platzhalterzeichen – das Fragezeichen (?) und das Sternchen () – in Kriterium*verwenden. Ein Fragezeichen ersetzt ein einzelnes Zeichen. Ein Sternchen ersetzt eine beliebige Zeichenfolge. Wenn Sie nach einem tatsächlichen Fragezeichen oder Sternchen suchen möchten, geben Sie vor dem Zeichen eine Tilde (~) ein.
Stellen Sie sicher, dass Ihre Daten keine fehlerhaften Zeichen enthalten.	Stellen Sie beim Durchsuchen von Textwerten in der ersten Spalte sicher, dass die Daten in der ersten Spalte keine führenden Leerzeichen, nachgestellte Leerzeichen, inkonsistente Verwendung von geraden ("oder") und geschweiften ("oder") Anführungszeichen oder nicht druckbaren Zeichen enthalten. In diesen Fällen gibt SVerweis möglicherweise einen unerwarteten Wert zurück. Um genaue Ergebnisse zu erhalten, können Sie die Funktionen SÄUBERN und GLÄTTEN verwenden, um alle schließenden Leerzeichen nach Tabellenwerten in einer Zelle zu entfernen.

Die Suche mit mehreren Kriterien mit der Funktion WAHL

Komplizierter wird die Suche, wenn Sie in mehreren Spalten und nach mehreren Kriterien suchen möchten. Wie bereits erwähnt, können Sie das mit Hilfe verschachtelter WENN-Abfragen lösen. Oder aber Sie kombinieren die Funktion SVerweis mit der Funktion WAHL, um ein Suchargument über mehrere Spalten zu erstellen. In einem zweiten Beispiel sollen die Preise für kombinierte Artikel aus den Spalten 1 und 2 ermittelt werden.

Beispiel 2, Abbildung 1: Jetzt werden 2 Kriterien für die Auswahl herangezogen.

Die Funktion WAHL wird für das Argument der Matrix innerhalb der Funktion SVerweis wie folgt zusammengesetzt: WAHL({1.2};A4:A14&B4:B14;C4:C14)

Die geschweiften Klammern für den zusammengesetzten Spaltenindex 1.2 werden über die Tastatur eingegeben (nicht wie am Ende der Matrixfunktion).

Step by Step - Suche mit der Funktion SVerweis kombiniert mit WAHL

Geben Sie in der Zelle E4 und F4 die Artikelbezeichnungen für den Artikel ein, dessen Preis Sie suchen.
In Zelle G4 geben Sie dann die gesamte Formel wie folgt ein:
=SVerweis(E4&F4;WAHL({1.2};A4:A14&B4:B14;C4:C14);2;0)
Erstellen Sie am Ende die geschweiften Klammern für die Matrixfunktion mit der Tastenkombination Strg + Shift + Enter.
Sie bekommen den Einkaufspreis des Artikels angezeigt.

Beispiel 2, Abbildung 2: Die Kombination von SVerweis und WAHL findet den gewünschten Eintrag.

Sie können mit dieser Vorgehensweise die Anzahl der Spalten und Kriterien jederzeit noch erweitern.