Das Excel SVERWEIS-Funktion ist ein leistungsstarkes Tool, das Ihnen hilft, nach einem bestimmten Wert zu suchen, indem es die erste Spalte einer Tabelle oder eines Bereichs vertikal abgleicht und dann einen entsprechenden Wert aus einer anderen Spalte in derselben Zeile zurückgibt. Obwohl VLOOKUP unglaublich nützlich ist, kann es für Anfänger manchmal schwierig sein, es zu verstehen. Dieses Tutorial soll Ihnen dabei helfen, VLOOKUP zu meistern, indem es Folgendes bereitstellt Schritt-für-Schritt-Erklärung der Argumente, nützliche Beispiele und Lösungen für häufige Fehler Dies kann bei Verwendung der SVERWEIS-Funktion auftreten.

• Schritt-für-Schritt-Erklärung der Argumente
• Syntax und Argumente
• SVERWEIS-Beispiele
  Exakte Übereinstimmung vs. ungefähre Übereinstimmung   |   Vlookup mit mehreren Bedingungen
• VLOOKUP Häufige Fehler und Lösungen
  #Nicht zutreffend Fehler   |   #VALUE-Fehler   |   #REF-Fehler   |   Falscher Wert

SVERWEIS-Beispiele .xlsx VLOOKUP-Fehler .xlsx

Ähnliche Videos

Schritt-für-Schritt-Erklärung der Argumente

Wie im obigen Screenshot gezeigt, wird die SVERWEIS-Funktion verwendet, um eine E-Mail basierend auf einer bestimmten ID-Nummer zu finden. Ich werde nun die Verwendung von VLOOKUP in diesem Beispiel ausführlich erläutern, indem ich jedes Argument Schritt für Schritt aufschlüssele.

Schritt 1: Starten Sie die SVERWEIS-Funktion

Wählen Sie eine Zelle (in diesem Fall H6) aus, um das Ergebnis auszugeben, und starten Sie dann die SVERWEIS-Funktion, indem Sie den folgenden Inhalt eingeben Formel-Bar.

=VLOOKUP(

Schritt 2: Geben Sie den Suchwert an

Geben Sie zunächst den Suchwert (den gesuchten Wert) in der VLOOKUP-Funktion an. Hier verweise ich auf die Zelle G6, die eine bestimmte ID-Nummer 1005 enthält.

=VLOOKUP(G6

Schritt 3: Geben Sie das Tabellenarray an

Geben Sie als Nächstes einen Zellbereich an, der sowohl den gesuchten Wert als auch den Wert enthält, den Sie zurückgeben möchten. In diesem Fall wähle ich den Bereich B6:E12. Die Formel sieht nun wie folgt aus:

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

Schritt 4: Geben Sie die Spalte an, aus der Sie einen Wert zurückgeben möchten

Geben Sie dann die Spalte an, aus der Sie einen Wert zurückgeben möchten.

Da ich in diesem Beispiel die E-Mail basierend auf einer ID-Nummer zurücksenden muss, gebe ich hier eine Zahl 4 ein, um SVERWEIS anzuweisen, einen Wert aus der vierten Spalte des Datenbereichs zurückzugeben.

=VLOOKUP(G6,B6:E12,4

Schritt 5: Finden Sie eine ungefähre oder genaue Übereinstimmung

Bestimmen Sie abschließend, ob Sie eine ungefähre oder eine genaue Übereinstimmung suchen.

• Um einen zu finden genaue Übereinstimmung, müssen Sie verwenden FALSCH als letztes Argument.
• Um einen zu finden ungefähre Übereinstimmung, benutzen Sie TRUE als letztes Argument ein, oder lassen Sie es einfach leer.

In diesem Beispiel verwende ich FALSE für eine genaue Übereinstimmung. Die Formel sieht nun so aus:

=VLOOKUP(G6,B6:E12,4,FALSE

Drücken Sie die Eingabetaste, um das Ergebnis zu erhalten

Indem im obigen Beispiel jedes Argument einzeln erklärt wird, sind die Syntax und die Argumente der VLOOKUP-Funktion jetzt viel einfacher zu verstehen.

Syntax und Argumente

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

• Lookup-Wert (erforderlich): Der Wert (ein realer Wert oder eine Zellreferenz), nach dem Sie suchen. Denken Sie daran, dass dieser Wert in der ersten Spalte des table_array stehen muss.
• Table_array (erforderlich): Ein Zellbereich enthält sowohl die Spalte des Suchwerts als auch die Spalte des Rückgabewerts.
• Spalte_index (erforderlich): Eine Ganzzahl stellt die Spaltennummer dar, die den Rückgabewert enthält. Es beginnt mit der Nummer 1 für die Spalte ganz links im table_array.
• Range_lookup (optional): Ein logischer Wert, der bestimmt, ob SVERWEIS eine ungefähre oder eine genaue Übereinstimmung finden soll.
  • Ungefähre Übereinstimmung – Setzen Sie dieses Argument auf TRUE, 1 oder lass es leer.
    Wichtig: Um eine ungefähre Übereinstimmung zu finden, müssen die Werte in der ersten Spalte des table_array in aufsteigender Reihenfolge sortiert werden, falls VLOOKUP das falsche Ergebnis zurückgibt.
  • Exakte Übereinstimmung – Setzen Sie dieses Argument auf FALSCH or 0.

Beispiele

In diesem Abschnitt werden einige Beispiele gezeigt, die Ihnen helfen sollen, ein umfassenderes Verständnis der SVERWEIS-Funktion zu erlangen.

Beispiel 1: Exakte Übereinstimmung vs. ungefähre Übereinstimmung in SVERWEIS

Wenn Sie sich bei der Verwendung von SVERWEIS hinsichtlich der genauen Übereinstimmung und der ungefähren Übereinstimmung nicht sicher sind, kann Ihnen dieser Abschnitt dabei helfen, diese Verwirrung zu beseitigen.

Genaue Übereinstimmung im SVERWEIS

In diesem Beispiel werde ich die entsprechenden Namen basierend auf den im Bereich E6:E8 aufgeführten Bewertungen finden, also gebe ich die folgende Formel in Zelle F6 ein und ziehe den AutoFill-Griff nach unten auf F8. In dieser Formel wird das letzte Argument als angegeben FALSCH um eine exakte Übereinstimmungssuche durchzuführen.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

Da die Punktzahl 98 jedoch in der ersten Spalte des Datenbereichs nicht vorhanden ist, gibt VLOOKUP das Fehlerergebnis #N/A zurück.

Ungefähre Übereinstimmung in SVERWEIS

Verwenden Sie immer noch das obige Beispiel, wenn Sie das letzte Argument in ändern TRUE, VLOOKUP führt eine ungefähre Übereinstimmungssuche durch. Wenn keine Übereinstimmung gefunden wird, wird der nächstgrößere Wert ermittelt, der kleiner als der Suchwert ist, und das entsprechende Ergebnis zurückgegeben.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Da die Punktzahl 98 nicht existiert, findet SVERWEIS den nächstgrößten Wert, der kleiner als 98 ist, also 95, und gibt den Namen der Punktzahl 95 als nächstliegendes Ergebnis zurück.

Beispiel 2: Verwenden Sie SVERWEIS mit mehreren Kriterien

In diesem Abschnitt wird gezeigt, wie Sie SVERWEIS mit mehreren Bedingungen in Excel verwenden. Wenn Sie, wie im Screenshot unten gezeigt, versuchen, ein Gehalt anhand eines angegebenen Namens (in Zelle H5) und einer Abteilung (in Zelle H6) zu ermitteln, führen Sie die folgenden Schritte aus, um dies zu erreichen.

Schritt 1: Fügen Sie eine Hilfsspalte hinzu, um die Werte aus den Nachschlagespalten zu verketten

In diesem Fall müssen wir eine Hilfsspalte erstellen, um die Werte aus zu verketten Name und Vorname Spalte und die Abteilung Spalte.

1. Fügen Sie links neben Ihrem Datenbereich eine Hilfsspalte hinzu und geben Sie dieser Spalte eine Überschrift. Siehe Screenshot:
   
   
2. Wählen Sie in dieser Hilfsspalte die erste Zelle unter der Überschrift aus und geben Sie die folgende Formel ein Formelleiste, und drücke Enter.
   

   =C6&" "&D6

   
   Notizen: In dieser Formel verwenden wir ein kaufmännisches Und-Zeichen (&), um den Text in zwei Spalten zu einem einzigen Textstück zu verbinden.

   • C6 ist der Vorname des Name und Vorname Spalte zum Beitreten, D6 ist die erste Abteilung der Abteilung Spalte zum Beitreten.
   • Die Werte dieser beiden Zellen werden mit einem Leerzeichen dazwischen verkettet.
3. Wählen Sie diese Ergebniszelle aus und ziehen Sie sie AutoFill-Griff nach unten, um diese Formel auf andere Zellen in derselben Spalte anzuwenden.
   
   

Schritt 2: Wenden Sie die SVERWEIS-Funktion mit den angegebenen Kriterien an

Wählen Sie eine Zelle aus, in der Sie das Ergebnis ausgeben möchten (hier wähle ich I7), und geben Sie die folgende Formel ein Formelleiste, und drücken Sie dann Enter.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

Ergebnis

Häufige SVERWEIS-Fehler und Lösungen

In diesem Abschnitt werden die häufigsten Fehler aufgelistet, die bei der Verwendung von SVERWEIS auftreten können, und es werden Lösungen zu deren Behebung bereitgestellt.

#K.A. Fehler wird zurückgegeben

Der häufigste Fehler bei VLOOKUP ist der #N/A-Fehler, was bedeutet, dass Excel den gesuchten Wert nicht finden konnte. Hier sind einige Gründe, warum VLOOKUP möglicherweise den Fehler #N/A zurückgibt.

Grund 1: Der Suchwert befindet sich nicht in der ersten Spalte des table_array

Eine der Einschränkungen von Excel VLOOKUP besteht darin, dass Sie nur von links nach rechts schauen können. Die Suchwerte müssen sich also in der ersten Spalte des table_array befinden.

Wie im Screenshot unten gezeigt, möchte ich einen Namen basierend auf der angegebenen Berufsbezeichnung zurückgeben. Hier der Suchwert (Verkaufsleiter) befindet sich in der zweiten Spalte des table_array und der Rückgabewert befindet sich links von der Suchspalte, daher gibt VLOOKUP den Fehler #N/A zurück.

Lösungen

Sie können eine der folgenden Lösungen anwenden, um diesen Fehler zu beheben.

• Ordnen Sie die Spalten neu an
  
  Sie können die Spalten neu anordnen, um die Suchspalte in der ersten Spalte des table_array zu platzieren.
• Verwenden Sie die Funktionen INDEX und MATCH zusammen
  
  Hier verwenden wir die Funktionen INDEX und MATCH zusammen als Alternative zu VLOOKUP, um dieses Problem zu lösen.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• Verwenden Sie die XLOOKUP-Funktion (verfügbar in Excel 365, Excel 2021 und späteren Versionen).
  

  =XLOOKUP(F6,C6:C12,B6:B12)

Grund 2: Der Suchwert wurde in der Suchspalte nicht gefunden (genaue Übereinstimmung)

Einer der häufigsten Gründe, warum VLOOKUP den Fehler #N/A zurückgibt, ist, dass der gesuchte Wert nicht gefunden wurde.

Wie im Beispiel unten gezeigt, werden wir den Namen basierend auf der angegebenen Punktzahl von 98 in E6 finden. Dieser Wert ist jedoch in der ersten Spalte des Datenbereichs nicht vorhanden, sodass VLOOKUP das Fehlerergebnis #N/A zurückgibt.

Lösungen

Um diesen Fehler zu beheben, können Sie eine der folgenden Lösungen ausprobieren.

• Wenn Sie möchten, dass VLOOKUP nach dem nächstgrößten Wert sucht, der kleiner als der Suchwert ist, ändern Sie das letzte Argument FALSCH (genaue Übereinstimmung) zu TRUE (ungefähre Übereinstimmung). Weitere Informationen finden Sie unter Beispiel 1: Genaue Übereinstimmung vs. ungefähre Übereinstimmung mit SVERWEIS.
• Um eine Änderung des letzten Arguments zu vermeiden und eine Erinnerung zu erhalten, falls der Suchwert nicht gefunden wird, können Sie die VLOOKUP-Funktion in die IFERROR-Funktion einschließen:
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Grund 3: Der Nachschlagewert ist kleiner als der kleinste Wert in der Nachschlagespalte (ungefähre Übereinstimmung)

Wie im Screenshot unten gezeigt, führen Sie eine ungefähre Übereinstimmungssuche durch. Der gesuchte Wert (in diesem Fall die ID-Nummer 1001) ist kleiner als der kleinste Wert 1002 in der Suchspalte, daher gibt VLOOKUP den Fehler #N/A zurück.

Lösungen

Hier sind zwei Lösungen für Sie.

• Stellen Sie sicher, dass der Nachschlagewert größer oder gleich dem kleinsten Wert in der Nachschlagespalte ist.
• Wenn Sie möchten, dass Excel Sie daran erinnert, dass der Suchwert nicht gefunden wurde, verschachteln Sie einfach die VLOOKUP-Funktion wie folgt in der IFERROR-Funktion:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Grund 4: Zahlen werden als Text formatiert

Wie Sie im folgenden Screenshot sehen können, ist das Ergebnis des #N/A-Fehlers in diesem Beispiel auf eine Nichtübereinstimmung des Datentyps zwischen der Nachschlagezelle (G6) und der Nachschlagespalte (B6:B12) der Originaltabelle zurückzuführen. Hier ist der Wert in G6 eine Zahl und die Werte im Bereich B6:B12 sind als Text formatierte Zahlen.

Lösungen

Um dieses Problem zu lösen, müssen Sie den Suchwert wieder in eine Zahl umwandeln. Hier sind zwei Methoden für Sie.

• Wenden Sie die Funktion „In Zahl umwandeln“ an
  
  Klicken Sie auf die Zelle, in der Sie den Text in eine Zahl umwandeln möchten, und wählen Sie diese Schaltfläche aus  neben der Zelle und wählen Sie dann aus In Nummer konvertieren.
  
• Wenden Sie ein praktisches Tool zur Stapelkonvertierung zwischen Text und Zahl an
  
  Das Konvertieren Sie zwischen Text und Zahl Merkmal von Kutools for Excel hilft Ihnen, eine Reihe von Zellen ganz einfach von Text in Zahlen umzuwandeln und umgekehrt. Holen Sie sich jetzt eine 30-tägige kostenlose Testversion mit vollem Funktionsumfang!
  

Grund 5: Das table_array ist nicht konstant, wenn die SVERWEIS-Formel in andere Zellen gezogen wird

Wie im Screenshot unten gezeigt, gibt es in E6 und E7 zwei Suchwerte. Nachdem Sie das erste Ergebnis in F6 erhalten haben, ziehen Sie die VLOOKUP-Formel von Zelle F6 nach F7. Es wird ein #N/A-Fehlerergebnis zurückgegeben. Das liegt daran, dass die Zellbezüge (B6:C12) standardmäßig relativ sind und angepasst werden, wenn Sie sich durch die Zeilen nach unten bewegen. Das Tabellenarray wurde nach B7:C13 verschoben, das den Lookup-Score 73 nicht mehr enthält.

Lösung

Sie müssen das Tabellenarray sperren, um es konstant zu halten, indem Sie ein hinzufügen $ Vorzeichen vor den Zeilen und Spalten in den Zellreferenzen. Um mehr über absolute Referenzen in Excel zu erfahren, schauen Sie sich dieses Tutorial an: Absolute Excel-Referenz (wie man sie erstellt und verwendet).

#VALUE-Fehler wird zurückgegeben

Die folgenden Bedingungen können dazu führen, dass SVERWEIS das Fehlerergebnis #WERT zurückgibt.

Grund 1: Der Suchwert überschreitet 255 Zeichen

Wie im Screenshot unten gezeigt, überschreitet der Suchwert in Zelle H4 255 Zeichen, sodass VLOOKUP ein #VALUE-Fehlerergebnis zurückgibt.

Lösungen

Um diese Einschränkung zu umgehen, können Sie eine andere Suchfunktion anwenden, die längere Zeichenfolgen verarbeiten kann. Probieren Sie eine der folgenden Formeln aus.

• INDEX und MATCH:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• XLOOKUP-Funktion (verfügbar in Excel 365, Excel 2021 und späteren Versionen):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

Grund 2: Das Argument col_index ist kleiner als 1

Der Spaltenindex gibt die Spaltennummer im Tabellenarray an, die den Wert enthält, den Sie zurückgeben möchten. Dieses Argument muss eine positive Zahl sein, die einer gültigen Spalte im Tabellenarray entspricht.

Wenn Sie einen Spaltenindex eingeben, der kleiner als 1 ist (also null oder negativ), kann VLOOKUP die Spalte im Tabellenarray nicht finden.

Lösung

Um dieses Problem zu beheben, stellen Sie sicher, dass das Spaltenindexargument in Ihrer SVERWEIS-Formel eine positive Zahl ist, die einer gültigen Spalte im Tabellenarray entspricht.

#REF-Fehler wird zurückgegeben

In diesem Abschnitt wird ein Grund aufgeführt, warum VLOOKUP den #REF-Fehler zurückgibt, und Lösungen für dieses Problem bereitgestellt.

Grund: Das Argument col_index ist größer als die Anzahl der Spalten

Wie Sie im Screenshot unten sehen können, besteht das Tabellenarray nur aus 4 Spalten. Der Spaltenindex, den Sie in der VLOOKUP-Formel angegeben haben, ist jedoch 5, was größer ist als die Anzahl der Spalten im Tabellenarray. Infolgedessen kann VLOOKUP die Spalte nicht finden und gibt letztendlich einen #REF-Fehler zurück.

Lösungen

• Geben Sie eine korrekte Spaltennummer an
  Stellen Sie sicher, dass das Spaltenindexargument in Ihrer VLOOKUP-Formel eine Zahl ist, die einer gültigen Spalte im Tabellenarray entspricht.
• Rufen Sie automatisch die Spaltennummer basierend auf der angegebenen Spaltenüberschrift ab
  Wenn die Tabelle viele Spalten enthält, kann es schwierig sein, die richtige Spaltenindexnummer zu ermitteln. Hier können Sie die MATCH-Funktion in der VLOOKUP-Funktion verschachteln, um die Position der Spalte basierend auf einer bestimmten Spaltenüberschrift zu ermitteln.

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  Note: In der obigen Formel ist die MATCH("E-Mail",B5:E5, 0) Die Funktion wird verwendet, um die Spaltennummer von „ abzurufen.E-Mail" Spalte im Datumsbereich B6:E12. Hier ist das Ergebnis 4, das als col_index in der VLOOKUP-Funktion verwendet wird.

Es wird ein falscher Wert zurückgegeben

Wenn Sie feststellen, dass VLOOKUP nicht das richtige Ergebnis zurückgibt, kann dies folgende Ursachen haben

Grund 1: Die Nachschlagespalte ist nicht in aufsteigender Reihenfolge sortiert

Wenn Sie das letzte Argument auf gesetzt haben TRUE (oder ließ es leer) für eine ungefähre Übereinstimmung und die Nachschlagespalte nicht in aufsteigender Reihenfolge sortiert ist, ist der resultierende Wert möglicherweise falsch.

Lösung

Das Sortieren der Nachschlagespalte in aufsteigender Reihenfolge kann Ihnen bei der Lösung dieses Problems helfen. Befolgen Sie dazu bitte die folgenden Schritte:

1. Wählen Sie die Datenzellen in der Nachschlagespalte aus und gehen Sie zu Datum Tab, klicken Sie auf Sortieren Sie vom kleinsten zum größten der Sortieren & Filtern Gruppe.
2. Im Warnung sortieren Wählen Sie im Dialogfeld Erweitern Sie die Auswahl Option und klicken Sie auf OK.

Grund 2: Eine Spalte wird eingefügt oder entfernt

Wie im Screenshot unten gezeigt, befindet sich der Wert, den ich ursprünglich zurückgeben wollte, in der vierten Spalte des Tabellenarrays, daher gebe ich die col_index-Nummer als 4 an. Wenn eine neue Spalte eingefügt wird, wird die Ergebnisspalte zur fünften Spalte der Tabelle Array, was dazu führt, dass VLOOKUP das Ergebnis aus einer falschen Spalte zurückgibt.

Lösungen

Hier sind zwei Lösungen für Sie.

• Sie können die Spaltenindexnummer manuell ändern, um sie an die Position der Rückgabespalte anzupassen. Die Formel hier sollte geändert werden in:

  =VLOOKUP(H6,B6:F12,5,FALSE)

• Wenn Sie das Ergebnis immer aus einer bestimmten Spalte zurückgeben möchten, wie z. B. der E-Mail-Spalte in diesem Beispiel. Die folgende Formel kann dabei helfen, den Spaltenindex basierend auf der angegebenen Spaltenüberschrift automatisch anzupassen, unabhängig davon, ob Spalten in das Tabellenarray eingefügt oder daraus entfernt werden.

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Weitere Funktionshinweise

• SVERWEIS sucht nur von links nach rechts nach Werten.
  Der Nachschlagewert befindet sich in der Spalte ganz links und der Ergebniswert sollte in einer beliebigen Spalte rechts von der Nachschlagespalte stehen.
• Wenn Sie das letzte Argument leer lassen, verwendet VLOOKUP standardmäßig eine ungefähre Übereinstimmung.
• VLOOKUP führt eine Suche ohne Berücksichtigung der Groß-/Kleinschreibung durch.
• Bei mehreren Übereinstimmungen gibt VLOOKUP nur die erste Übereinstimmung zurück, die es im Tabellenarray findet, basierend auf der Reihenfolge der Zeilen im Tabellenarray.

Ähnliche Artikel