Verwendung der VLOOKUP-Funktion und spezifische Beispiele

Die SVERWEIS-Funktion in Excel ist beim Nachschlagen von Daten äußerst nützlich. Hier erfahren Sie, was Sie über die Verwendung der SVERWEIS-Funktion in Excel wissen müssen .

Sie möchten einen bestimmten Wert in einer Excel-Tabelle überprüfen und Informationen dazu nachschlagen. Das Ausführen eines SVERWEISes erspart Ihnen in diesem Fall erheblichen Aufwand. Dies ist eine der besten Möglichkeiten, eine vertikale Abfrage in Microsoft Excel auszuführen. Der Artikel erläutert Ihnen die Bedeutung, Verwendung und anschauliche Beispiele der SVERWEIS-Funktion in Microsoft Excel.

• Dies sind die grundlegendsten Funktionen in Excel, die Sie verstehen müssen

So verwenden Sie die VLOOKUP-Funktion

• Was ist die SVERWEIS-Funktion?
• Formel der SVERWEIS-Funktion in Excel 2016, 2013, 2010, 2007, 2003
• Video-Tutorial zur Verwendung der SVERWEIS-Funktion
• Beispiel 1: Verwenden Sie die SVERWEIS-Funktion, um die Bewertungen von Studenten und Mitarbeitern auszuwerten:
• Beispiel 2: Verwenden Sie VLOOKUP zur absoluten Erkennung, um Daten abzurufen
• Beispiel 3: Verwenden Sie VLOOKUP, um Daten zu extrahieren
• Beispiel 4: Verwenden Sie die SVERWEIS-Funktion für zwei verschiedene Excel-Tabellen
• Wofür soll die VLOOKUP-Funktion verwendet werden?
• Was Sie bei der SVERWEIS-Funktion beachten sollten

Was ist die SVERWEIS-Funktion?

Die SVERWEIS-Funktion ist eine Datensuchfunktion in Excel. Ich gehe davon aus, dass Sie über Excel-Kenntnisse verfügen und grundlegende Funktionen wie SUMME, MITTELWERT und HEUTE verwenden können. Eine der häufigsten Funktionen von VLOOKUP ist eine Datenfunktion, das heißt, sie arbeitet mit Datenbanktabellen oder einfacher mit Listen von Elementen. Die Liste ist sehr abwechslungsreich. Sie können eine Tabelle über das Personal Ihres Unternehmens, Produktkategorien, Kundenlisten oder alles andere erstellen. Und unten ist eine Liste der Produkte, die Unternehmen A auf dem Markt verkauft:

Datenbanktabellen enthalten häufig Kennungen für jedes Produkt. In der Datentabelle oben ist die Spalte „Artikelcode“ das besondere Identifikationszeichen. Hinweis: Um die SVERWEIS-Funktion verwenden zu können, muss die Datentabelle eine Spalte mit identifizierenden Zeichen wie Code oder ID enthalten und wie in der Tabelle oben die erste Spalte sein.

SVERWEIS ist wahrscheinlich die bekannteste Funktion in Excel, allerdings aus guten und schlechten Gründen. Positiv zu vermerken ist, dass die VLOOKUP-Funktion einfach zu verwenden ist und eine sehr nützliche Funktion hat. Insbesondere für neue Benutzer ist es äußerst spannend zu beobachten, wie die SVERWEIS-Funktion die Tabelle scannt, eine Übereinstimmung findet und ein genaues Ergebnis zurückgibt. Der kompetente Umgang mit der VLOOKUP-Funktion ist eine wesentliche Fähigkeit, vom Anfänger bis zum erfahrenen Excel-Benutzer.

Negativ zu vermerken ist, dass die SVERWEIS-Funktion eingeschränkt ist und gefährliche Standardwerte aufweist. Im Gegensatz zu INDEX und MATCH (oder XLOOKUP) benötigt die VLOOKUP-Funktion eine vollständige Tabelle mit Suchwerten in der ersten Spalte. Dies erschwert die Verwendung der SVERWEIS-Funktion mit mehreren Kriterien. Darüber hinaus macht es das Standard-Matching-Verhalten von VLOOKUP leicht, falsche Ergebnisse zu erhalten. Aber keine Sorge! Der Schlüssel zur erfolgreichen Verwendung der SVERWEIS-Funktion liegt in der Beherrschung der Grundlagen.

VLOOKUP hat 4 Argumente: lookup_value, table_array, Column_index_num und range_lookup. Lookup_value ist der Wert, nach dem gesucht werden soll, und table_array ist der vertikale Datenbereich, in dem gesucht werden soll. Die erste Spalte von table_array muss die zu durchsuchenden Suchwerte enthalten. Das Argument „column_index_num“ ist die Spaltennummer des abzurufenden Werts, wobei die erste Spalte von „table_array“ Spalte 1 ist.

Schließlich steuert range_lookup das Verhalten beim Finden einer Übereinstimmung. Wenn range_lookup TRUE ist, führt die VLOOKUP-Funktion eine ungefähre Übereinstimmung durch. Wenn „range_lookup“ FALSE ist, führt die VLOOKUP-Funktion eine exakte Übereinstimmung durch. Wichtig ist, dass „range_lookup“ optional ist und standardmäßig auf TRUE eingestellt ist, sodass VLOOKUP standardmäßig eine ungefähre Übereinstimmung durchführt.

Formel der SVERWEIS-Funktion in Excel

Die SVERWEIS-Funktion in Excel hat die folgende Formel:

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

Da drin:

• VLOOKUP: Ist der Funktionsname
• Fettgedruckte Parameter sind erforderlich.
• lookup_value: Für die Suche verwendeter Wert
• table_array: Tabelle mit dem zu durchsuchenden Wert, eingegeben als Absolutwert mit einem $-Zeichen davor, zum Beispiel: $A$3:$E$40.
• col_index_num: Reihenfolge der Spalte, die den Suchwert auf table_array enthält. Beispielsweise enthält in der Tabelle $A$3:$E$40 Spalte B den zu durchsuchenden Wert, dann ist col_index_num hier 2; Tabelle $C$3:$F$40, Spalte E enthält den Suchwert, dann ist col_index_num hier 3.
• range_lookup: Ist der Suchbereich, TRUE entspricht 1 (relative Suche), FALSE entspricht 0 (absolute Suche). Dieser Parameter ist in der Formel nicht immer erforderlich.

Die relative Suche kann nur angewendet werden, wenn die in table_array zu suchenden Werte in der richtigen Reihenfolge angeordnet wurden (aufsteigend oder absteigend oder alphabetisch). Bei solchen Tabellen können Sie eine relative Suche verwenden, die der Verwendung einer unendlichen IF-Funktion ähnelt . Für Werte, die nicht durchsucht werden können oder nicht sortiert sind, verwenden Sie die absolute Suche, um den genauen Wert zu finden.

Video-Tutorial zur Verwendung der SVERWEIS-Funktion

Beispiel 1: SVERWEIS-Funktion zur Auswertung der Bewertungen von Studierenden und Mitarbeitern

Angenommen, Sie haben ein Zeugnis wie folgt:

Und die Rangordnungstabelle lautet wie folgt:

Jetzt verwenden wir die SVERWEIS-Funktion, um Bewertungen für Schüler einzugeben. Sie werden feststellen, dass die Rangliste in der Reihenfolge von niedrig nach hoch (von schwach nach gut) geordnet ist. In diesem Fall können wir also die relative Suche verwenden.

In Excel werden diese beiden Tabellen wie folgt dargestellt:

Hier befindet sich der zu erkennende Wert in Spalte C, beginnend mit Zeile 6. Der Suchbereich ist $A$18:$B$21, die Reihenfolge der Spalte, die den erkannten Wert enthält, ist 2.

Geben Sie in Zelle D6 die Formel ein: =SVERWEIS($C6,$A$18:$B$21,2,1). Dies ist eine relative Suchformel. Sie können eine absolute Suche durchführen, wenn Sie möchten (oder weil die Rangfolge nicht sortiert ist), indem Sie wie folgt 0 zur Formel hinzufügen: =VLOOKUP($C6 ,$A$18:$B$21,2, 0). Drücken Sie Enter.

Klicken Sie auf Zelle D6. In der unteren rechten Ecke erscheint ein kleines Quadrat. Klicken Sie darauf und ziehen Sie die Tabelle nach unten, um die Benotungsformel für die verbleibenden Schüler zu kopieren.

Zu diesem Zeitpunkt liegen uns die Ergebnisse der Verwendung der SVERWEIS-Funktion zur Klassifizierung der akademischen Leistungen der Schüler wie folgt vor:

Fragen Sie sich, warum $ vor C6 verwendet werden muss? $ behält Spalte C bei und ändert die Zeilen nur, wenn Sie die Formel in der Tabelle nach unten ziehen. Außerdem gibt es $A$18:$B$21, um die Rangfolgeregeltabelle zu korrigieren, damit sie sich beim Ziehen der Formel nicht ändert.

Beispiel 2: Die VLOOKUP-Funktion sucht absolut nach Daten

Angenommen, Sie verfügen über eine Mitarbeiterdatentabelle, in der der Mitarbeitercode, der vollständige Name und die Position gespeichert sind. In einer weiteren Tabelle werden der Mitarbeitercode, der Heimatort und das Bildungsniveau gespeichert. Nun möchten Sie für jeden Mitarbeiter Angaben zum Heimatort und zum Bildungsniveau machen. Was sollten Sie tun?

Angenommen, Sie haben eine Tabelle mit Mitarbeitern und deren Heimatorten wie folgt:

Jetzt möchten Sie die Informationen zum Heimatort der Mitarbeiter eingeben. Geben Sie in Zelle D4 die absolute Suchformel wie folgt ein: =Vlookup($A4,$A$16:$C$25,2,0)

Drücken Sie dann die Eingabetaste. Klicken Sie auf das kleine Quadrat, das in der Ecke von Zelle D4 erscheint, und ziehen Sie es über die Tabelle nach unten, um die Formel auf andere Mitarbeiter zu kopieren.

Um Qualifikationsinformationen für Mitarbeiter einzugeben, geben Sie in Zelle E4 die absolute Suchformel ein: =VLOOKUP($A4,$A$16:$C$25,3,0)

Drücken Sie weiterhin die Eingabetaste und scrollen Sie nach unten, um die Formel auf die verbleibenden Mitarbeiter zu kopieren. Wir erhalten das folgende Ergebnis:

Beispiel 3: Verwenden Sie VLOOKUP, um Daten zu extrahieren

Wenn wir mit dem Datensatz von Beispiel 2 fortfahren, finden wir nun die Heimatorte von drei Mitarbeitern: Nguyen Hoang Anh, Tran Van Anh und Nguyen Quang Vinh. Ich habe es in eine neue Tabelle F15:G18 extrahiert.

Ich werde diese Suche in Tabelle A3:E13 durchführen, nachdem ich meinen Heimatort und meine Qualifikationen eingegeben habe. Geben Sie nun die folgende absolute Suchformel in Zelle G16 ein: =VLOOKUP($F16,$B$3:$E$13,3,0) > drücken Sie die Eingabetaste.

Wenn wir die Formel für die verbleibenden 2 Mitarbeiter kopieren, erhalten wir folgendes Ergebnis:

Beachten Sie, dass sich in diesem Beispiel der Erkennungswert in Spalte B befindet, sodass der Erkennungsbereich aus Spalte B und nicht aus Spalte A berechnet wird.

Beispiel 4: Verwenden Sie die SVERWEIS-Funktion für zwei verschiedene Excel-Tabellen

Zurück zum Datensatz in Beispiel 2: Nachdem die Qualifikationen und der Heimatort des Mitarbeiters ausgefüllt wurden, nennen wir das Blatt QTM.

In einem anderen Blatt der Tabelle mit dem Namen QTM1 müssen Sie Informationen über die Qualifikationen und die Position des Mitarbeiters abrufen, wobei die Anordnungsreihenfolge des Mitarbeiters geändert wurde. Hier erkennen Sie die wahre Leistungsfähigkeit der SVERWEIS-Funktion.

Um Daten zu den „Qualifikationen“ des Mitarbeiters zu finden, geben Sie die Formel =SVERWEIS($B4,QTM!$B$3:$E$13,4,0) in Zelle C4 des Arbeitsblatts QTM1 ein.

Da drin:

• B4 ist die Spalte, die den zur Erkennung verwendeten Wert enthält.
• QTM! ist der Name des Blattes, das die Tabelle mit dem zu erkennenden Wert enthält. Fügen Sie nach dem Blattnamen ein ! hinzu.
• $B$3:$E$13 ist die Tabelle mit den Suchwerten und das Blatt mit der Tabelle (QTM).
• 4 ist die laufende Nummer der Spalte „Qualifikationen“, berechnet aus der Spalte „Vollständiger Name“ im QTM-Blatt.
• 0 ist absolute Erkennung.

Drücken Sie die Eingabetaste und kopieren Sie dann die Formel für alle verbleibenden Mitarbeiter in die Tabelle. Wir erhalten das folgende Ergebnis:

Um die „Position“-Daten des Mitarbeiters zu finden, geben Sie in Zelle D4 des Arbeitsblatts QTM1 die Formel ein: =VLOOKUP($B4,QTM!$B$3:$E$13,2,0) und drücken Sie die Eingabetaste.

Wenn wir die Formel für die verbleibenden Mitarbeiter kopieren, erhalten wir Folgendes:

Wofür soll die VLOOKUP-Funktion verwendet werden?

Zuerst müssen wir die genaue Antwort auf die Frage „Wofür verwenden wir die VLOOKUP-Funktion?“ finden.

Die VLOOKUP-Funktion wird verwendet, um das Nachschlagen von Informationen in einem Datenfeld oder einer Liste basierend auf verfügbaren Bezeichnern zu unterstützen.

Wenn Sie beispielsweise die SVERWEIS-Funktion mit einem Produktcode in eine andere Tabelle einfügen, werden die diesem Code entsprechenden Produktinformationen angezeigt. Diese Informationen können je nach Inhalt des von Ihnen verfassten Rezepts eine Beschreibung, ein Preis oder eine Lagermenge sein.

Je kleiner die Menge an Informationen ist, die Sie finden müssen, desto schwieriger wird es, die VLOOKUP-Funktion zu schreiben. Normalerweise verwenden Sie diese Funktion in einem wiederverwendbaren Arbeitsblatt als Vorlage. Bei jeder Eingabe des entsprechenden Produktcodes ruft das System alle notwendigen Informationen zum entsprechenden Produkt ab.

Was Sie bei der SVERWEIS-Funktion beachten sollten

Hier ist eine Liste wichtiger Dinge, die Sie bei der SVERWEIS-Funktion in Excel beachten sollten:

• Wenn „range_lookup“ weggelassen wird, lässt VLOOKUP nicht exakte Übereinstimmungen zu, verwendet aber dennoch exakte Übereinstimmungen, sofern verfügbar.
• Die größte Einschränkung der Funktion besteht darin, dass sie immer den Wert auf der rechten Seite annimmt. Die Funktion ruft Daten aus den Spalten rechts von der ersten Spalte in der Tabelle ab.
• Wenn die Nachschlagespalte doppelte Werte enthält, stimmt die VLOOKUP-Funktion nur mit dem ersten Wert überein.
• Die Funktion unterscheidet nicht zwischen Groß- und Kleinschreibung.
• Angenommen, in einem Arbeitsblatt ist eine VLOOKUP-Formel vorhanden. In diesem Fall können die Formeln kaputt gehen, wenn Sie eine Spalte in die Tabelle einfügen, da die Spaltenindexwerte fest codiert sind und sich beim Einfügen oder Löschen von Spalten nicht automatisch ändern.
• SVERWEIS erlaubt die Verwendung von Platzhalterzeichen, zum Beispiel Sternchen (*) oder Fragezeichen (?).
• Angenommen, Sie arbeiten in einer Tabelle mit einer Funktion, die als Text eingegebene Zahlen enthält. Wenn Sie Zahlen nur als Text aus einer Spalte in einer Tabelle abrufen, spielt das keine Rolle. Wenn die erste Spalte der Tabelle jedoch als Text eingegebene Zahlen enthält, wird das #N/A! wird angezeigt, wenn der Suchwert ebenfalls kein Text ist.
• Fehler #N/A! Tritt auf, wenn die VLOOKUP-Funktion keine Übereinstimmung für den bereitgestellten Lookup-Wert findet.
• Fehler #REF! passiert, wenn:
  • Das Argument col_index_num ist größer als die Anzahl der Spalten im bereitgestellten table_array
  • Die Formel hat versucht, auf nicht vorhandene Zellen zu verweisen.
• Fehler #VALUE! passiert, wenn:
  • Das Argument col_index_num ist kleiner als 1 oder wird nicht als numerischer Wert erkannt
  • Das Argument range_lookup wird nicht als einer der logischen Werte TRUE oder FALSE erkannt.

Hauptunterschiede zwischen SVERWEIS- und XLVERWEIS-Funktionen in Excel

Y – Ja

N – Nein

Mehr sehen:

• So verwenden Sie die IF-Funktion in Excel
• So verwenden Sie die AVERAGEIF-Funktion in Excel

Nachdem Sie wissen, wie man VLookup verwendet, müssen Sie auch häufige Fehler kennen und wissen, wie Sie Probleme beheben können.

Häufige Fehler bei der Verwendung der SVERWEIS-Funktion in Excel

Der Suchwert befindet sich in der falschen Spalte

Einer der häufigsten Fehler bei der Verwendung einer SVERWEIS-Formel ist, dass die Funktion nur den Wert #N/A zurückgibt. Dieser Fehler tritt auf, wenn der von VLOOKUP gesuchte Suchwert nicht gefunden werden kann.

In einigen Fällen können Sie die XLOOKUP-Funktion anstelle von VLOOKUP verwenden. Dieser Fehler kann jedoch nur in der VLOOKUP-Funktion einfach behoben werden.

So beheben Sie das Problem:

Der Grund dafür, dass SVERWEIS den gewünschten Wert nicht gefunden hat, liegt möglicherweise darin, dass der Wert nicht in der Tabelle enthalten ist. Wenn jedoch alle #N/A-Werte zurückgegeben werden und der Suchwert Text ist, liegt das höchstwahrscheinlich daran, dass nur von links nach rechts gelesen werden kann.

Stellen Sie beim Erstellen einer Tabelle in Excel für VLOOKUP sicher, dass der Suchwert links vom Wert platziert wird, den Sie zurückgeben möchten. Der einfachste Weg, dies zu tun, besteht darin, es in die erste Spalte der Tabelle einzufügen.

Jetzt gibt die Formel einen Wert zurück:

Insgesamt ist VLOOKUP eine großartige Möglichkeit, Daten in Microsoft Excel schneller abzufragen. Obwohl VLOOKUP-Abfragen vertikal auf Spalten ausgeführt werden und einigen anderen Einschränkungen unterliegen, aktualisiert Microsoft die Suchfunktionen von Excel kontinuierlich, um ihre Anwendbarkeit zu erweitern. Mit HLOOKUP, XLOOKUP usw. können Sie beispielsweise Daten in vielen verschiedenen Richtungen nachschlagen. LuckyTemplates.com wird Sie in den folgenden Artikeln weiterhin über die Nutzung dieser Funktion informieren.