Das Koordinieren riesiger Datenmengen in Microsoft Excel ist ein zeitraubendes Unterfangen. Zum Glück müssen Sie das nicht. Mit der Funktion VLOOKUP können Sie diese Aufgabe automatisieren und jede Menge Zeit sparen.
Was genau macht VLOOKUP? Hier ist die einfache Erklärung: Die VLOOKUP-Funktion sucht nach einem bestimmten Wert in Ihren Daten und kann, sobald sie diesen Wert identifiziert hat, andere Informationen finden und anzeigen, die mit diesem Wert verknüpft sind.
Die VLOOKUP-Funktion von Microsoft Excel ist einfacher zu verwenden, als Sie denken. Darüber hinaus ist sie unglaublich leistungsstark und definitiv etwas, das Sie in Ihrem Arsenal analytischer Waffen haben möchten.
Wie funktioniert VLOOKUP?
VLOOKUP steht für „vertikale Suche“. In Excel bedeutet dies, dass Daten vertikal in einer Tabelle gesucht werden , wobei die Spalten der Tabelle – und eine eindeutige Kennung in diesen Spalten – als Grundlage für die Suche verwendet werden. Wenn Sie Ihre Daten nachschlagen, müssen sie vertikal aufgelistet werden, wo auch immer sich diese Daten befinden.
VLOOKUP-Excel-Formel
Microsoft beschreibt die VLOOKUP-Formel bzw. -Funktion wie folgt:
=VLOOKUP(Nachschlagewert, Bereich, der den Nachschlagewert enthält, Spaltennummer im Bereich, der den Rückgabewert enthält, Ungefähre Übereinstimmung (WAHR) oder Genaue Übereinstimmung (FALSCH)).
Es ist hilfreich, Ihre Daten so zu organisieren, dass der Wert, den Sie nachschlagen möchten, links neben dem Rückgabewert steht, den Sie finden möchten.
Die Formel sucht immer nach rechts.
Wenn Sie in Excel eine VLOOKUP-Funktion ausführen, suchen Sie im Wesentlichen nach neuen Daten in einer anderen Tabelle, die mit alten Daten in Ihrer aktuellen Tabelle verknüpft sind. Wenn VLOOKUP diese Suche ausführt, sucht es immer rechts neben Ihren aktuellen Daten nach den neuen Daten.
Wenn beispielsweise eine Tabelle eine vertikale Liste von Namen enthält und eine andere Tabelle eine ungeordnete Liste dieser Namen und ihrer E-Mail-Adressen , können Sie VLOOKUP verwenden, um diese E-Mail-Adressen in der Reihenfolge abzurufen, in der sie in Ihrer ersten Tabelle stehen. Diese E-Mail-Adressen müssen in der Spalte rechts neben den Namen in der zweiten Tabelle aufgeführt sein, sonst kann Excel sie nicht finden. (Das muss man sich mal vorstellen …)
Die Formel benötigt eine eindeutige Kennung, um Daten abzurufen.
Das Geheimnis der Funktionsweise von VLOOKUP? Eindeutige Kennungen.
Eine eindeutige Kennung ist eine Information, die beide Datenquellen gemeinsam nutzen und die – wie der Name schon sagt – eindeutig ist (d. h. die Kennung ist nur einem Datensatz in Ihrer Datenbank zugeordnet). Zu eindeutigen Kennungen gehören Produktcodes, Lagereinheiten (SKUs) und Kundenkontakte.
Okay, genug der Erklärungen: Sehen wir uns ein weiteres Beispiel für die VLOOKUP-Funktion in Aktion an!
VLOOKUP Excel-Beispiel
Im folgenden Video zeigen wir ein Beispiel in Aktion, bei dem die Funktion VLOOKUP verwendet wird, um E-Mail-Adressen (aus einer zweiten Datenquelle) den entsprechenden Daten in einem separaten Blatt zuzuordnen.
Anmerkung des Autors: Es gibt viele verschiedene Versionen von Excel. Was Sie im obigen Video sehen, stimmt also möglicherweise nicht immer genau mit dem überein, was Sie in Ihrer Version sehen. Aus diesem Grund empfehlen wir Ihnen, die unten stehenden schriftlichen Anweisungen zu befolgen.
- Identifizieren Sie eine Zellenspalte, die Sie mit neuen Daten füllen möchten.
- Wählen Sie „Funktion“ (Fx) > VLOOKUP und fügen Sie diese Formel in Ihre markierte Zelle ein.
- Geben Sie den Nachschlagewert ein, für den Sie neue Daten abrufen möchten.
- Geben Sie das Tabellenarray der Kalkulationstabelle ein, in dem sich die gewünschten Daten befinden.
- Geben Sie die Spaltennummer der Daten ein, die Excel zurückgeben soll.
- Geben Sie Ihren Bereichssuchvorgang ein, um eine genaue oder ungefähre Übereinstimmung mit Ihrem Suchwert zu finden.
- Klicken Sie auf „Fertig“ (oder „Eingabe“) und füllen Sie Ihre neue Spalte aus.
Zu Ihrer Information: So sieht die Syntax einer VLOOKUP-Funktion aus:
VLOOKUP(Suchwert, Tabellenarray, Spaltenindexnummer, Bereichssuche)
In den folgenden Schritten weisen wir jeder dieser Komponenten den richtigen Wert zu und verwenden dabei Kundennamen als eindeutige Kennung, um den MRR jedes Kunden zu ermitteln.
1. Identifizieren Sie eine Zellenspalte, die Sie mit neuen Daten füllen möchten.
Denken Sie daran, dass Sie Daten aus einem anderen Blatt abrufen und in dieses einfügen möchten. Beschriften Sie daher eine Spalte neben den Zellen, zu denen Sie weitere Informationen benötigen, mit einem aussagekräftigen Titel in der obersten Zelle, z. B. „MRR“ für monatlich wiederkehrende Einnahmen. In diese neue Spalte werden die Daten eingefügt, die Sie abrufen.
2. Wählen Sie „Funktion“ (Fx) > VLOOKUP und fügen Sie diese Formel in Ihre markierte Zelle ein.
Links neben der Textleiste über Ihrer Tabelle sehen Sie ein kleines Funktionssymbol, das wie ein Skript aussieht: Fx . Klicken Sie auf die erste leere Zelle unter Ihrem Spaltentitel und dann auf dieses Funktionssymbol. Auf der rechten Seite Ihres Bildschirms wird ein Feld mit dem Titel Formel-Generator oder Funktion einfügen angezeigt (je nachdem, welche Excel-Version Sie haben).
Suchen Sie in der Liste der im Formel-Generator enthaltenen Optionen nach „VLOOKUP“ und wählen Sie es aus. Wählen Sie dann „ OK“ oder „Funktion einfügen“ , um mit dem Erstellen Ihres VLOOKUP zu beginnen. Die Zelle, die Sie derzeit in Ihrer Tabelle markiert haben, sollte nun folgendermaßen aussehen: „ =VLOOKUP() “
Sie können diese Formel auch manuell in einen Anruf eingeben, indem Sie den oben fettgedruckten Text exakt in die gewünschte Zelle eingeben.
Nachdem Sie den Text =VLOOKUP in Ihre erste Zelle eingegeben haben, ist es an der Zeit, die Formel mit vier verschiedenen Kriterien zu füllen. Diese Kriterien helfen Excel dabei, genau einzugrenzen, wo sich die gewünschten Daten befinden und wonach gesucht werden soll.
3. Geben Sie den Nachschlagewert ein, für den Sie neue Daten abrufen möchten.
Das erste Kriterium ist Ihr Nachschlagewert – das ist der Wert Ihrer Tabelle, der mit Daten verknüpft ist, die Excel für Sie finden und zurückgeben soll. Um ihn einzugeben, klicken Sie auf die Zelle, die einen Wert enthält, für den Sie eine Übereinstimmung suchen. In unserem oben gezeigten Beispiel befindet er sich in Zelle A2. Sie beginnen mit der Migration Ihrer neuen Daten in D2, da diese Zelle den MRR des in A2 aufgeführten Kundennamens darstellt.
Denken Sie daran, dass Ihr Suchwert alles Mögliche sein kann: Text, Zahlen, Website-Links, was auch immer. Solange der gesuchte Wert mit dem Wert in der verweisenden Tabelle übereinstimmt – darüber sprechen wir im nächsten Schritt – gibt diese Funktion die gewünschten Daten zurück.
4. Geben Sie das Tabellenarray der Kalkulationstabelle ein, in dem sich die gewünschten Daten befinden.
Geben Sie neben dem Feld „Tabellenarray“ den Zellbereich ein, den Sie durchsuchen möchten, und das Blatt, in dem sich diese Zellen befinden. Verwenden Sie dabei das im Screenshot oben gezeigte Format. Der obige Eintrag bedeutet, dass sich die gesuchten Daten in einem Tabellenblatt mit dem Titel „Seiten“ befinden und irgendwo zwischen Spalte B und Spalte K zu finden sind.
Das Blatt, in dem sich Ihre Daten befinden, muss sich in Ihrer aktuellen Excel-Datei befinden. Das bedeutet, dass sich Ihre Daten entweder in einer anderen Zellentabelle irgendwo in Ihrem aktuellen Arbeitsblatt oder in einem anderen Arbeitsblatt befinden können, das unten in Ihrer Arbeitsmappe verlinkt ist, wie unten gezeigt.
Wenn sich Ihre Daten beispielsweise in „Sheet2“ zwischen den Zellen C7 und L18 befinden, lautet Ihr Tabellenarrayeintrag „Sheet2!C7:L18“.
5. Geben Sie die Spaltennummer der Daten ein, die Excel zurückgeben soll.
Unter dem Tabellenarray-Feld geben Sie die „Spaltenindexnummer“ des Tabellenarrays ein, das Sie durchsuchen. Wenn Sie sich beispielsweise auf die Spalten B bis K konzentrieren (notiert als „B:K“, wenn Sie sie in das Feld „Tabellenarray“ eingeben), die gewünschten Werte sich jedoch in Spalte K befinden, geben Sie „10“ in das Feld „Spaltenindexnummer“ ein, da Spalte K die 10. Spalte von links ist.
6. Geben Sie Ihren Bereichssuchvorgang ein, um eine genaue oder ungefähre Übereinstimmung mit Ihrem Suchwert zu finden.
In Situationen wie unserer, in denen es um den monatlichen Umsatz geht, möchten Sie genaue Übereinstimmungen in der Tabelle finden, die Sie durchsuchen. Geben Sie dazu „FALSE“ in das Feld „Bereichssuche“ ein. Dadurch teilt Excel mit, dass Sie nur den genauen Umsatz finden möchten, der mit jedem Verkaufskontakt verknüpft ist.
Um Ihre brennende Frage zu beantworten: Ja, Sie können Excel veranlassen, nach einer ungefähren Übereinstimmung statt einer genauen Übereinstimmung zu suchen . Geben Sie dazu im vierten oben gezeigten Feld einfach TRUE statt FALSE ein.
Wenn VLOOKUP auf eine ungefähre Übereinstimmung eingestellt ist, sucht es nach Daten, die Ihrem Nachschlagewert am nächsten kommen, und nicht nach Daten, die mit diesem Wert identisch sind. Wenn Sie beispielsweise nach Daten suchen, die mit einer Liste von Website-Links verknüpft sind, und einige Ihrer Links am Anfang „https://“ haben, kann es für Sie sinnvoll sein, eine ungefähre Übereinstimmung zu finden, nur für den Fall, dass es Links gibt, die dieses „https://“-Tag nicht haben. Auf diese Weise kann der Rest des Links übereinstimmen, ohne dass dieser anfängliche Texttag dazu führt, dass Ihre VLOOKUP-Formel einen Fehler zurückgibt, wenn Excel ihn nicht finden kann.
7. Klicken Sie auf „Fertig“ (oder „Eingabe“) und füllen Sie Ihre neue Spalte aus.
Um die gewünschten Werte offiziell in Ihre neue Spalte aus Schritt 1 einzutragen, klicken Sie nach dem Ausfüllen des Felds „Bereichssuche“ auf „Fertig“ (oder „Eingabe“, je nach Ihrer Excel-Version). Dadurch wird Ihre erste Zelle ausgefüllt. Sie können diese Gelegenheit nutzen, um in der anderen Tabelle nachzuschauen, um sicherzustellen, dass dies der richtige Wert ist.
Wenn ja, füllen Sie den Rest der neuen Spalte mit jedem nachfolgenden Wert aus, indem Sie auf die erste ausgefüllte Zelle und dann auf das kleine Quadrat klicken, das in der unteren rechten Ecke dieser Zelle angezeigt wird. Fertig! Alle Ihre Werte sollten angezeigt werden.
VLOOKUP funktioniert nicht?
VLOOKUP-Anleitung
Sie kommen nicht weiter, nachdem Sie versucht haben, mit den oben genannten Schritten Ihre eigene VLOOKUP-Funktion auszuführen? Sehen Sie sich dieses praktische Tutorial von Microsoft an, das Sie durch die korrekte Verwendung der Funktion führt.
Wenn Sie die oben genannten Schritte ausgeführt haben und Ihre VLOOKUP-Funktion immer noch nicht funktioniert, liegt das Problem möglicherweise an:
- Syntax (also wie Sie die Formel strukturiert haben)
- Werte (also ob die gesuchten Daten gut und richtig formatiert sind)
Fehlerbehebung bei der VLOOKUP-Syntax
Sehen Sie sich zunächst die VLOOKUP-Formel an, die Sie in die angegebene Zelle geschrieben haben.
- Bezieht es sich auf den richtigen Nachschlagewert für seinen Schlüsselbezeichner?
- Gibt es den richtigen Tabellenarraybereich für die abzurufenden Werte an?
- Ist das richtige Blatt für den Bereich angegeben?
- Ist das Blatt richtig geschrieben?
- Wird die richtige Syntax zum Verweisen auf das Blatt verwendet? (z. B. Seiten!B:K oder „Blatt 1“!B:K)
- Wurde die richtige Spaltennummer angegeben? (z. B. A ist 1, B ist 2 usw.)
- Ist „Richtig“ oder „Falsch“ die richtige Vorgehensweise für die Einrichtung Ihres Blattes?
Read more: Fitbit Versa 4 Testbericht: Eine Fitness-Smartwatch, die wenig Sinn macht
Fehlerbehebung bei VLOOKUP-Werten
Wenn die Syntax nicht das Problem ist, liegt das Problem möglicherweise bei den Werten selbst, die Sie abrufen möchten. Dies äußert sich häufig als #N/A-Fehler , bei dem VLOOKUP einen referenzierten Wert nicht finden kann.
- Sind die Werte vertikal und von rechts nach links formatiert?
- Stimmen die Werte mit der Art und Weise überein, wie Sie sich auf sie beziehen?
Wenn Sie beispielsweise nach URL-Daten suchen, muss jede URL eine Zeile mit den entsprechenden Daten links davon in derselben Zeile sein. Wenn Sie die URLs als Spaltenüberschriften haben und die Daten vertikal verschoben sind, funktioniert die VLOOKUP-Funktion nicht.
Um bei diesem Beispiel zu bleiben: Die URLs müssen in beiden Blättern im Format übereinstimmen. Wenn ein Blatt „https://“ im Wert enthält, während das andere Blatt „https://“ weglässt, kann VLOOKUP die Werte nicht zuordnen.
VLOOKUP als leistungsstarkes Marketinginstrument
Marketingfachleute müssen Daten aus einer Vielzahl von Quellen analysieren, um ein vollständiges Bild der Lead-Generierung (und mehr) zu erhalten. Microsoft Excel ist das perfekte Tool, um dies präzise und in großem Umfang zu tun, insbesondere mit der VLOOKUP-Funktion.
Anmerkung des Herausgebers: Dieser Beitrag wurde ursprünglich im März 2019 veröffentlicht und der Vollständigkeit halber aktualisiert.