SVerweis: Automatische Wertesuche in Excel mit mehreren Kriterien
Frédéric Jordan
Frédéric Jordan ist dipl. Experte in Organisationsmanagement und als Organisations- und Managementberater bei Jordan Consulting tätig.
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
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 Kriteriumverwenden. 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. |
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.
