20+ VLOOKUP-Beispiele für Excel-Anfänger und fortgeschrittene Benutzer

Die VLOOKUP-Funktion zählt zu den beliebtesten Funktionen in Excel. In diesem Tutorial erfahren Sie Schritt für Schritt, wie Sie VLOOKUP in Excel nutzen – anhand von Dutzenden grundlegender und fortgeschrittener Beispiele.

Einführung in die VLOOKUP-Funktion – Syntax und Argumente

In Excel ist die VLOOKUP-Funktion eine leistungsstarke Funktion für die meisten Anwender: Sie sucht in der äußersten linken Spalte eines Datenbereichs nach einem bestimmten Wert und gibt den passenden Wert aus derselben Zeile einer von Ihnen festgelegten Spalte zurück – wie im folgenden Screenshot veranschaulicht.

Syntax der VLOOKUP-Funktion:

Argumente:

Hinweise zur Funktion:

Grundlegende VLOOKUP-Beispiele

In diesem Abschnitt werden einige häufig genutzte VLOOKUP-Formeln vorgestellt.

2,1 Exakte und ungefähre Übereinstimmung bei VLOOKUP

2,1.1 Durchführung einer exakten Übereinstimmung mit VLOOKUP

Normalerweise müssen Sie bei der Suche nach einer exakten Übereinstimmung mit der VLOOKUP-Funktion als letztes Argument FALSE angeben.

Um beispielsweise die entsprechenden Mathematiknoten anhand bestimmter ID-Nummern zu erhalten, gehen Sie wie folgt vor:

Kopieren Sie die folgende Formel in eine leere Zelle (hier wähle ich G2 aus) und drücken Sie die „Enter“-Taste, um das Ergebnis zu erhalten:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Wie funktioniert die VLOOKUP-Formel?

Zunächst sucht sie in der äußersten linken Spalte der Tabelle nach der ID C1005, durchläuft diese von oben nach unten und findet den Wert in Zelle A6.

Sobald der Wert gefunden wird, springt die Funktion in die dritte Spalte rechts und extrahiert den dort enthaltenen Wert.

Sie erhalten somit das Ergebnis wie im folgenden Screenshot gezeigt:

2,1.2 Führen Sie eine ungefähre Übereinstimmung mit VLOOKUP durch

Die ungefähre Übereinstimmung ist nützlich für die Suche nach Werten innerhalb eines Bereichs. Wird keine exakte Übereinstimmung gefunden, gibt VLOOKUP bei einer ungefähren Suche den größten Wert zurück, der kleiner als der Suchwert ist.

Angenommen, Sie haben den folgenden Datenbereich vorliegen und die genannten Bestellmengen befinden sich nicht in der Spalte „Bestellungen“ – wie ermitteln Sie dann den nächstgelegenen Rabatt aus Spalte B?

Schritt 1: Wenden Sie die VLOOKUP-Formel an und füllen Sie sie in andere Zellen aus

Kopieren Sie die folgende Formel in die Zelle, in der das Ergebnis angezeigt werden soll, und ziehen Sie anschließend am Ausfüllkästchen nach unten, um die Formel auf weitere Zellen zu übertragen.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Ergebnis:

Nun erhalten Sie die ungefähren Übereinstimmungen basierend auf den angegebenen Werten; siehe Screenshot:

2,2 Führen Sie eine Groß-/Kleinschreibung beachten VLOOKUP-Suche in Excel durch

Standardmäßig führt die VLOOKUP-Funktion eine groß-/kleinschreibungsunabhängige Suche durch – sie behandelt Groß- und Kleinbuchstaben also als identisch. Manchmal benötigen Sie jedoch eine groß-/kleinschreibungsabhängige Suche in Excel, was mit der herkömmlichen VLOOKUP-Funktion nicht möglich ist. In solchen Fällen können Sie alternative Kombinationen wie INDEX und VERGLEICH (MATCH) zusammen mit der EXACT-Funktion oder LOOKUP mit EXACT nutzen.

Angenommen, Sie haben einen Datenbereich, dessen ID-Spalte Textzeichenfolgen enthält, die mit „Alles groß“ oder „Nach Kleinbuchstaben filtern“ gefiltert wurden. Nun möchten Sie die zugehörige Mathematiknote zur angegebenen ID-Nummer abrufen.

Schritt 1: Wenden Sie eine der Formeln an und füllen Sie sie in andere Zellen aus

Kopieren Sie eine der folgenden Formeln in eine leere Zelle, in der das Ergebnis erscheinen soll. Wählen Sie anschließend die Formelzelle aus und ziehen Sie am Ausfüllkästchen nach unten, um die Formel auf die gewünschten Zellen zu übertragen.

Formel 1: Drücken Sie nach dem Einfügen der Formel gleichzeitig „Strg“ + „Umschalttaste“ + „Eingabe“.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Formel 2: Drücken Sie nach dem Einfügen der Formel die Eingabetaste.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Ergebnis:

Dann erhalten Sie die gewünschten, korrekten Ergebnisse. Siehe Screenshot:

2,3 VLOOKUP-Werte von rechts nach links in Excel

Die VLOOKUP-Funktion sucht stets einen Wert in der äußersten linken Spalte eines Datenbereichs und gibt den entsprechenden Wert aus einer Spalte rechts davon zurück. Wenn Sie eine umgekehrte VLOOKUP-Suche durchführen möchten – also einen bestimmten Wert in der rechten Spalte suchen und den zugehörigen Wert aus der linken Spalte zurückgeben möchten – wie im folgenden Screenshot gezeigt:

Klicken Sie hier, um die detaillierten Schritte für diese Aufgabe zu erfahren…

2,4 VLOOKUP des zweiten, n-ten oder letzten übereinstimmenden Werts in Excel

Normalerweise gibt die VLOOKUP-Funktion nur den ersten übereinstimmenden Datensatz zurück, wenn mehrere Übereinstimmungen gefunden werden. In diesem Abschnitt erfahren Sie, wie Sie stattdessen den zweiten, n-ten oder letzten übereinstimmenden Wert aus einem Datenbereich abrufen können.

2,4.1 VLOOKUP und Rückgabe des 2. oder n-ten übereinstimmenden Werts

Angenommen, in Spalte A steht eine Liste mit Namen und in Spalte B der jeweils gebuchte Schulungskurs. Jetzt möchten Sie den zweiten oder n-ten Kurs ermitteln, den ein bestimmter Kunde gebucht hat. Siehe Screenshot:

Die VLOOKUP-Funktion eignet sich hierfür nicht direkt – als Alternative empfiehlt sich die INDEX-Funktion.

Schritt 1: Wenden Sie die Formel an und füllen Sie sie in andere Zellen aus

Um beispielsweise den zweiten übereinstimmenden Wert basierend auf den angegebenen Kriterien zu erhalten, geben Sie die folgende Formel in eine leere Zelle ein und drücken Sie gleichzeitig „Strg“ + „Umschalttaste“ + „Eingabe“, um das erste Ergebnis zu erzielen. Wählen Sie anschließend die Formelzelle aus und ziehen Sie am Ausfüllkästchen nach unten, um die Formel in die gewünschten Zellen zu übertragen.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Ergebnis:

Nun werden alle zweiten übereinstimmenden Werte basierend auf den angegebenen Namen gleichzeitig angezeigt.

2,4.2 VLOOKUP und Rückgabe des letzten übereinstimmenden Werts

Wenn Sie wie im folgenden Screenshot gezeigt einen VLOOKUP durchführen und den letzten übereinstimmenden Wert zurückgeben möchten, hilft Ihnen dieses VLOOKUP und Rückgabe des letzten übereinstimmenden Werts-Tutorial detailliert weiter.

2,5 VLOOKUP übereinstimmender Werte zwischen zwei angegebenen Werten oder Datumsangaben

Gelegentlich möchten Sie möglicherweise nach Werten innerhalb eines Bereichs zwischen zwei Zahlen oder Datumsangaben suchen und die entsprechenden Ergebnisse – wie im folgenden Screenshot gezeigt – abrufen. In einem solchen Fall können Sie die LOOKUP-Funktion statt der VLOOKUP-Funktion mit einer sortierten Tabelle verwenden.

2,5.1 VLOOKUP übereinstimmender Werte zwischen zwei angegebenen Werten oder Datumsangaben mithilfe einer Formel

Schritt 1: Ordnen Sie die Daten an und wenden Sie die folgende Formel an

Ihre ursprüngliche Tabelle sollte einen sortierten Datenbereich enthalten. Kopieren oder geben Sie anschließend die folgende Formel in eine leere Zelle ein und ziehen Sie am Ausfüllkästchen, um sie auf die gewünschten weiteren Zellen zu übertragen.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Ergebnis:

Nun erhalten Sie alle übereinstimmenden Datensätze basierend auf dem angegebenen Wert; siehe Screenshot:

2,5.2 VLOOKUP übereinstimmender Werte zwischen zwei angegebenen Werten oder Datumsangaben mit einer praktischen Funktion

Falls Sie Schwierigkeiten haben, sich die obige Formel zu merken oder zu verstehen, stellen wir Ihnen hier ein einfaches Werkzeug vor – „Kutools für Excel“. Mit der Funktion „Daten zwischen zwei Werten finden“ geben Sie problemlos das entsprechende Element basierend auf einem bestimmten Wert oder Datum zwischen zwei Werten bzw. Datumsangaben zurück.

1. Klicken Sie auf „Kutools“ > „Erweiterte SVERWEIS“ > „Daten zwischen zwei Werten finden“, um diese Funktion zu nutzen.
2. Geben Sie anschließend im Dialogfeld die gewünschten Operationen entsprechend Ihren Daten an.

2,6 Verwendung von Platzhaltern für teilweise Übereinstimmungen in der VLOOKUP-Funktion

In Excel können Sie Platzhalter in der VLOOKUP-Funktion nutzen, um eine teilweise Übereinstimmung mit einem Suchwert zu erzielen. So lassen sich beispielsweise mithilfe von VLOOKUP passende Werte aus einer Tabelle abrufen, die nur einen Teil des Suchwerts enthalten.

Angenommen, Sie haben einen Datenbereich wie im folgenden Screenshot dargestellt. Nun möchten Sie die Punktzahl anhand des Vornamens (nicht des vollständigen Namens) extrahieren. Wie lässt sich diese Aufgabe in Excel lösen?

Schritt 1: Wenden Sie die Formel an und füllen Sie sie in andere Zellen aus

Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein und ziehen Sie anschließend am Ausfüllkästchen, um diese Formel in die gewünschten weiteren Zellen zu übertragen:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Ergebnis:

Alle übereinstimmenden Punktzahlen wurden wie im folgenden Screenshot gezeigt zurückgegeben:

2,7 VLOOKUP-Werte aus einem anderen Arbeitsblatt

In der Regel arbeiten Sie häufig mit mehreren Arbeitsblättern. Die VLOOKUP-Funktion eignet sich nicht nur zum Abrufen von Daten innerhalb eines Arbeitsblatts, sondern auch zum Zugriff auf Daten aus einem anderen Arbeitsblatt.

Angenommen, Sie haben zwei Arbeitsblätter wie im folgenden Screenshot dargestellt. Um die entsprechenden Daten aus dem von Ihnen angegebenen Arbeitsblatt abzurufen, gehen Sie wie folgt vor:

Schritt 1: Wenden Sie die Formel an und füllen Sie sie in andere Zellen aus

Geben Sie die folgende Formel in eine leere Zelle ein, in der die übereinstimmenden Elemente angezeigt werden sollen, und ziehen Sie anschließend am Ausfüllkästchen nach unten, um die Formel auf die gewünschten Zellen anzuwenden.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Ergebnis:

Sie erhalten die gewünschten entsprechenden Ergebnisse; siehe Screenshot:

2,8 VLOOKUP-Werte aus einer anderen Arbeitsmappe

In diesem Abschnitt erfahren Sie, wie Sie mit der VLOOKUP-Funktion übereinstimmende Werte aus einer anderen Arbeitsmappe abrufen und zurückgeben können.

Angenommen, Sie haben zwei Arbeitsmappen: Die erste enthält eine Liste von Produkten samt deren jeweiligen Kosten. In der zweiten möchten Sie – wie im folgenden Screenshot dargestellt – die zugehörigen Kosten für jedes Produkt ermitteln.

Schritt 1: Wenden Sie die Formel an

Öffnen Sie beide Arbeitsmappen, die Sie verwenden möchten, und wenden Sie anschließend die folgende Formel in einer Zelle der zweiten Arbeitsmappe an, in der Sie das Ergebnis anzeigen möchten. Ziehen und kopieren Sie diese Formel danach in die gewünschten weiteren Zellen.

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Ergebnis:

2,9 Geben Sie stattdessen eine leere Zelle oder einen bestimmten Text anstelle von 0 oder dem Fehler #NV zurück

Normalerweise gibt die VLOOKUP-Funktion einen entsprechenden Wert zurück. Ist die übereinstimmende Zelle jedoch leer, erhalten Sie eine 0 – und wenn der Suchwert nicht gefunden wird, erscheint der Fehlerwert #NV, wie im folgenden Screenshot gezeigt. Möchten Sie stattdessen eine leere Zelle oder einen bestimmten Wert anstelle von 0 oder #NV anzeigen, hilft Ihnen dieses VLOOKUP: Rückgabe einer leeren Zelle oder eines bestimmten Werts statt 0 oder NV-Tutorial weiter.

Erweiterte VLOOKUP-Beispiele

3,1 Zweiwegsuche (VLOOKUP in Zeile und Spalte)

Gelegentlich müssen Sie möglicherweise eine zweidimensionale Suche durchführen – das heißt, Sie suchen gleichzeitig nach einem Wert sowohl in einer Zeile als auch in einer Spalte. Angenommen, Sie haben den folgenden Datenbereich und möchten den Wert für ein bestimmtes Produkt in einem festgelegten Quartal ermitteln. In diesem Abschnitt stellen wir Ihnen eine Formel vor, mit der Sie diese Aufgabe in Excel mühelos lösen können.

In Excel können Sie mithilfe einer Kombination aus den Funktionen VLOOKUP und MATCH eine Zwei-Wege-Suche durchführen.

Wenden Sie die folgende Formel in einer leeren Zelle an und drücken Sie anschließend die Eingabetaste („Enter“), um das Ergebnis zu erhalten.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3,2 VLOOKUP – Übereinstimmungswert basierend auf zwei oder mehr Kriterien

Es ist einfach, einen Übereinstimmungswert anhand eines einzigen Kriteriums zu finden. Doch was tun Sie, wenn zwei oder mehr Kriterien erfüllt sein müssen?

3,2.1 VLOOKUP – Übereinstimmungswert basierend auf zwei oder mehr Kriterien mithilfe von Formeln

In diesem Fall helfen Ihnen die Excel-Funktionen LOOKUP sowie MATCH und INDEX, diese Aufgabe schnell und einfach zu meistern.

Angenommen, Sie haben die folgende Datentabelle und möchten den passenden Preis für ein bestimmtes Produkt und eine bestimmte Größe ermitteln – dann helfen Ihnen die nachfolgenden Formeln weiter.

Schritt 1: Wenden Sie eine der unten stehenden Formeln an

Formel 1: Geben Sie die folgende Formel ein und drücken Sie „Enter“.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Formel 2: Geben Sie die folgende Formel ein und drücken Sie gleichzeitig „Strg“ + „Umschalt“ + „Enter“.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Ergebnis:

3,2.2 VLOOKUP – Übereinstimmungswert basierend auf zwei oder mehr Kriterien mit Kutools für Excel

Es kann schwierig sein, sich die oben genannten komplexen Formeln zu merken, die immer wieder angewendet werden müssen – was Ihre Arbeitseffizienz beeinträchtigen kann. Mit der Funktion „Suche – Mehrere Bedingungen suchen“ von Kutools für Excel erhalten Sie jedoch mit nur wenigen Klicks das gewünschte Ergebnis basierend auf einer oder mehreren Bedingungen.

1. Klicken Sie auf „Kutools“ > „Erweiterte SVERWEIS“ > „Suche – Mehrere Bedingungen suchen“, um diese Funktion zu aktivieren.
2. Geben Sie anschließend im Dialogfeld die gewünschten Operationen entsprechend Ihren Daten an.

3,3 VLOOKUP – Rückgabe mehrerer Werte mit einer oder mehreren Bedingungen

In Excel sucht die VLOOKUP-Funktion nach einem Wert und gibt – falls mehrere Übereinstimmungen vorliegen – stets nur den ersten gefundenen Wert zurück. Manchmal möchten Sie jedoch alle zugehörigen Werte in einer Zeile, einer Spalte oder sogar innerhalb einer einzigen Zelle abrufen. In diesem Abschnitt erfahren Sie, wie Sie mehrere übereinstimmende Werte anhand einer oder mehrerer Bedingungen in einer Arbeitsmappe zurückgeben können.

3,3.1 VLOOKUP – Alle übereinstimmenden Werte basierend auf einer oder mehreren Bedingungen horizontal zurückgeben

Angenommen, Sie haben eine Datentabelle mit Ländern, Städten und Namen im Bereich A1:C14 und möchten nun alle Namen aus den „USA“ horizontal auflisten – so wie in der folgenden Abbildung dargestellt. Um diese Aufgabe zu meistern,klicken Sie hier, um Schritt für Schritt zum Ergebnis zu gelangen.

3,3.2 VLOOKUP – Alle übereinstimmenden Werte basierend auf einer oder mehreren Bedingungen vertikal zurückgeben

Wenn Sie mit VLOOKUP alle übereinstimmenden Werte vertikal basierend auf bestimmten Kriterien zurückgeben möchten – wie in der folgenden Abbildung gezeigt –, klicken Sie hier, um die detaillierte Lösung zu erhalten.

3,3.3 VLOOKUP – Alle übereinstimmenden Werte basierend auf einer oder mehreren Bedingungen in eine einzelne Zelle zurückgeben

Wenn Sie mit VLOOKUP mehrere übereinstimmende Werte in eine einzelne Zelle mit einem festgelegten Trennzeichen zusammenführen möchten, hilft Ihnen die neue TEXTJOIN-Funktion, diese Aufgabe schnell und einfach zu erledigen.

3,4 VLOOKUP – Rückgabe von Ganze Zeile einer übereinstimmenden Zelle

In diesem Abschnitt erfahren Sie, wie Sie mithilfe der VLOOKUP-Funktion die gesamte Zeile eines übereinstimmenden Werts abrufen können.

Schritt 1: Wenden Sie die folgende Formel an

Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein, in der das Ergebnis angezeigt werden soll, und drücken Sie die Eingabetaste („Enter“), um den ersten Wert zu erhalten. Ziehen Sie die Formelzelle anschließend nach rechts, bis die Daten der gesamten Zeile angezeigt werden.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Ergebnis:

Nun sehen Sie, dass die gesamten Zeilendaten zurückgegeben wurden. Siehe Abbildung:

3,5 Geschachtelte VLOOKUP-Funktion in Excel

Manchmal müssen Sie Werte suchen, die über mehrere Tabellen hinweg miteinander verknüpft sind. In solchen Fällen können Sie mehrere VLOOKUP-Funktionen ineinander verschachteln, um den gewünschten Wert zu erhalten.

Angenommen, Sie haben ein Arbeitsblatt mit zwei separaten Tabellen. Die erste Tabelle listet alle Produktnamen zusammen mit dem jeweiligen Verkäufer auf. Die zweite Tabelle enthält die Gesamtverkäufe jedes Verkäufers. Wenn Sie nun die Verkäufe jedes Produkts ermitteln möchten, wie in der folgenden Abbildung dargestellt, können Sie die VLOOKUP-Funktion verschachteln, um diese Aufgabe zu erfüllen.

Die allgemeine Formel für eine geschachtelte VLOOKUP-Funktion lautet:

Schritt 1: Wenden und füllen Sie die folgende Formel an

Wenden Sie die folgende Formel in eine leere Zelle an und ziehen Sie anschließend den Ausfüllkorb nach unten auf alle Zellen, auf die Sie die Formel übertragen möchten.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Ergebnis:

Sie erhalten nun das Ergebnis, wie in der folgenden Abbildung dargestellt:

3,6 Prüfen, ob ein Wert basierend auf einer Liste in einer anderen Spalte vorhanden ist

Die VLOOKUP-Funktion hilft Ihnen zudem dabei, zu prüfen, ob Werte in einer anderen Spalte basierend auf einer Datenliste vorhanden sind. Angenommen, Sie möchten die Namen in Spalte C suchen und lediglich „Ja“ oder „Nein“ zurückgeben – je nachdem, ob der jeweilige Name in Spalte A gefunden wurde, wie in der folgenden Abbildung dargestellt.

Schritt 1: Wenden Sie die folgende Formel an

Wenden Sie die folgende Formel in eine leere Zelle an und ziehen Sie anschließend den Ausfüllkorb nach unten, um die Formel in die gewünschten Zellen zu übertragen.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Ergebnis:

Sie erhalten nun das gewünschte Ergebnis – siehe Abbildung:

3,7 VLOOKUP und Summe aller übereinstimmenden Werte in Zeilen oder Spalten

Beim Arbeiten mit numerischen Daten müssen Sie möglicherweise übereinstimmende Werte aus einer Tabelle extrahieren und die Zahlen über mehrere Spalten oder Zeilen summieren. In diesem Abschnitt stellen wir Ihnen einige hilfreiche Formeln vor, die Sie bei dieser Aufgabe unterstützen.

3,7.1 VLOOKUP und Summe aller übereinstimmenden Werte in einer Zeile oder mehreren Zeilen

Angenommen, Sie verfügen über eine Produktliste mit Verkaufsdaten für mehrere Monate, wie in der folgenden Abbildung dargestellt. Jetzt möchten Sie alle Bestellungen aller Monate basierend auf den angegebenen Produkten summieren.

Schritt 1: Wenden Sie die folgende Formel an

Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein und drücken Sie anschließend gleichzeitig „Strg“ + „Umschalt“ + „Enter“, um das erste Ergebnis zu erhalten. Ziehen Sie danach den Ausfüllkorb nach unten, um die Formel auf weitere benötigte Zellen zu übertragen.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Ergebnis:

Alle Werte in der Zeile des ersten übereinstimmenden Eintrags wurden summiert. Siehe Abbildung:

3,7.2 VLOOKUP und Summe aller übereinstimmenden Werte in einer Spalte oder mehreren Spalten

Wenn Sie den Gesamtwert für bestimmte Monate summieren möchten – wie in der folgenden Abbildung gezeigt –, reicht die herkömmliche VLOOKUP-Funktion nicht aus. In diesem Fall empfiehlt es sich, SUMME, INDEX und VERGLEICH zu kombinieren, um eine leistungsstarke Formel zu erstellen.

Schritt 1: Wenden Sie die folgende Formel an

Wenden Sie die folgende Formel in eine leere Zelle an und ziehen Sie anschließend den Ausfüllkorb nach unten, um sie auf weitere Zellen zu übertragen.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Ergebnis:

Nun wurden die ersten übereinstimmenden Werte je Monat in einer Spalte summiert. Siehe Abbildung:

3,7.3 VLOOKUP und Summe des ersten oder aller übereinstimmenden Werte mit Kutools für Excel

Die oben genannten Formeln sind möglicherweise schwer zu merken. In diesem Fall empfehle ich Ihnen die leistungsstarke Funktion „Suchen und Summieren“ aus „Kutools für Excel“: Damit summieren Sie mithilfe von VLOOKUP den ersten oder alle übereinstimmenden Werte in Zeilen oder Spalten so einfach wie möglich.

1. Klicken Sie auf „Kutools“ > „Erweiterte SVERWEIS“ > „Suchen und Summieren“, um diese Funktion zu aktivieren.
2. Geben Sie im Anschluss im Dialogfeld die gewünschten Operationen gemäß Ihren Anforderungen an.

3,7.4 VLOOKUP und Summe aller übereinstimmenden Werte sowohl in Zeilen als auch in Spalten

Wenn Sie Werte summieren möchten, bei denen sowohl Spalte als auch Zeile übereinstimmen müssen – beispielsweise den Gesamtwert des Produkts „Pullover“ im Monat März, wie in der folgenden Abbildung dargestellt –

Können Sie hierfür die SUMMENPRODUKT-Funktion nutzen, um diese Aufgabe zu erledigen?

Wenden Sie die folgende Formel in einer Zelle an und drücken Sie anschließend die Eingabetaste („Enter“), um das Ergebnis zu erhalten. Siehe Abbildung:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3,8 VLOOKUP – Zusammenführen zweier Tabellen basierend auf Schlüsselspalte

Bei der täglichen Datenanalyse müssen Sie unter Umständen alle erforderlichen Informationen anhand einer oder mehrerer Schlüsselspalten in einer einzigen Tabelle zusammenführen. Statt der VLOOKUP-Funktion empfiehlt sich hierfür die Kombination aus INDEX und VERGLEICH.

3,8.1 VLOOKUP – Zusammenführen zweier Tabellen basierend auf einem Schlüsselspalte

Angenommen, Sie verfügen über zwei Tabellen: Die erste enthält Produkt- und Namensdaten, die zweite Produkt- und Bestelldaten. Jetzt möchten Sie beide Tabellen anhand der gemeinsamen Produktspalte abgleichen und zu einer einzigen Tabelle zusammenführen.

Schritt 1: Wenden Sie die folgende Formel an

Wenden Sie die folgende Formel in eine leere Zelle an, und ziehen Sie anschließend den Ausfüllkorb nach unten auf alle Zellen, auf die Sie die Formel übertragen möchten.

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Ergebnis:

Sie erhalten nun eine zusammengeführte Tabelle, bei der die Bestellspalte basierend auf den Daten der Schlüsselspalte an die erste Tabelle angefügt wurde.

3,8.2 VLOOKUP – Zusammenführen zweier Tabellen basierend auf mehreren Schlüsselspalte

Wenn die beiden Tabellen, die Sie verknüpfen möchten, mehrere Schlüsselspalten enthalten und Sie sie anhand dieser gemeinsamen Spalten zusammenführen wollen, gehen Sie bitte wie folgt vor.

Die allgemeine Formel lautet:

Schritt 1: Wenden Sie die folgende Formel an

Bitte wenden Sie die unten stehende Formel in einer leeren Zelle an, in der Sie das Ergebnis anzeigen möchten, und drücken Sie anschließend gleichzeitig „Strg“ + „Umschalt“ + „Eingabe“, um den ersten übereinstimmenden Wert zu erhalten. Siehe Screenshot:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Schritt 2: Füllen Sie die Formel in andere Zellen

Wählen Sie dann die erste Formelzelle aus und ziehen Sie am Ausfüllkästchen, um diese Formel nach Bedarf in andere Zellen zu kopieren:

3,9 VLOOKUP-Übereinstimmungswerte über mehrere Arbeitsblätter hinweg

Mussten Sie jemals eine VLOOKUP-Abfrage über mehrere Arbeitsblätter in Excel durchführen? Angenommen, Sie haben drei Arbeitsblätter mit Datenbereichen und möchten bestimmte Werte basierend auf Ihren Kriterien aus diesen Blättern abrufen – dann befolgen Sie einfach die schrittweise Anleitung VLOOKUP-Werte über mehrere Arbeitsblätter hinweg, um diese Aufgabe mühelos zu erledigen.

VLOOKUP-Übereinstimmungswerte behalten die Zellenformatierung

Beim Suchen nach übereinstimmenden Werten wird die ursprüngliche Zellenformattierung wie Schriftfarbe, Hintergrundfarbe, das Datenformat usw. nicht beibehalten. Um die Zellen- oder Datenformatierung zu erhalten, werden in diesem Abschnitt einige Tricks zur Lösung dieser Aufgaben vorgestellt.

4,1 VLOOKUP-Übereinstimmungswert mit Beibehaltung von Zellenfarbe und Schriftformatierung

Wie allgemein bekannt ist, kann die herkömmliche VLOOKUP-Funktion lediglich den übereinstimmenden Wert aus einem anderen Datenbereich abrufen. Es gibt jedoch Situationen, in denen Sie nicht nur den passenden Wert, sondern auch dessen Zellenformatierung – etwa Füllfarbe, Schriftfarbe und Schriftschnitt – übernehmen möchten. In diesem Abschnitt erfahren Sie, wie Sie übereinstimmende Werte abrufen und gleichzeitig die Formatierung der Quelle in Excel beibehalten können.

Führen Sie bitte die folgenden Schritte aus, um den zugehörigen Wert zusammen mit der Zellenformatierung zu suchen und zurückzugeben:

Schritt 1: Kopieren Sie Code 1 in das Arbeitsblatt-Code-Modul

1. Klicken Sie im Arbeitsblatt, das die Daten enthält, die Sie mit VLOOKUP abfragen möchten, mit der rechten Maustaste auf die Blattregisterkarte und wählen Sie im Kontextmenü „Code anzeigen“ (siehe Screenshot).
   
2. Kopieren Sie im geöffneten Fenster „Microsoft Visual Basic for Applications“ den folgenden VBA-Code in das Codefenster.
3. VBA-Code 1: VLOOKUP zur Übernahme der Zellenformatierung zusammen mit dem Suchwert

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

Schritt 2: Kopieren Sie Code 2 in das Modulfenster

1. Klicken Sie im Fenster „Microsoft Visual Basic for Applications“ auf „Einfügen“ > „Modul“ und fügen Sie anschließend den folgenden VBA-Code 2 in das Modul-Fenster ein.
2. VBA-Code 2: VLOOKUP zur Übernahme der Zellenformatierung zusammen mit dem Suchwert

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

Schritt 3: Wählen Sie die Option für das VBA-Projekt aus

1. Nachdem Sie die oben genannten Codes eingefügt haben, klicken Sie im Fenster „Microsoft Visual Basic for Applications“ auf „Extras“ > „Verweise“. Aktivieren Sie im daraufhin erscheinenden Dialogfeld „Verweise – VBAProject“ das Kontrollkästchen „Microsoft Scripting Runtime“. Siehe Screenshots:
2. Klicken Sie anschließend auf „OK“, um das Dialogfeld zu schließen, und speichern Sie das Code-Fenster, bevor Sie es schließen.

Schritt 4: Geben Sie die Formel zur Ergebniserzeugung ein

1. Wechseln Sie nun zum Arbeitsblatt und wenden Sie die folgende Formel an. Ziehen Sie anschließend am Ausfüllkästchen nach unten, um alle Ergebnisse mitsamt ihrer Formatierung zu übernehmen. Siehe Screenshot:
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4,2 Behalten Sie das Datumsformat aus einer VLOOKUP-Rückgabewert

Wenn Sie die VLOOKUP-Funktion nutzen, um einen Wert im Datumsformat zu suchen und zurückzugeben, erscheint das Ergebnis möglicherweise als Zahl. Um das Datumsformat im Ergebnis beizubehalten, betten Sie die VLOOKUP-Funktion einfach in die TEXT-Funktion ein.

Schritt 1: Wenden Sie die folgende Formel an

Wenden Sie die folgende Formel in einer leeren Zelle an und ziehen Sie anschließend am Ausfüllkästchen, um sie auf weitere Zellen zu übertragen.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Ergebnis:

Alle übereinstimmenden Datumsangaben wurden wie im folgenden Screenshot gezeigt zurückgegeben:

4,3 Rückgabe des Kommentar aus VLOOKUP

Haben Sie jemals sowohl die übereinstimmenden Zelldaten als auch den zugehörigen Kommentar mit VLOOKUP in Excel abrufen müssen – so wie im folgenden Screenshot gezeigt? Dann unterstützt Sie die unten bereitgestellte benutzerdefinierte Funktion dabei, diese Aufgabe mühelos zu erledigen.

Schritt 1: Kopieren Sie den Code in ein Modul

1. Halten Sie die Tasten „ALT“ + „F11“ gedrückt, um das Fenster „Microsoft Visual Basic for Applications“ zu öffnen.
2. Klicken Sie auf „Einfügen“ > „Modul“ und kopieren Sie den folgenden Code in das „Modul“-Fenster.
   VBA-Code: VLOOKUP und Rückgabe des übereinstimmenden Werts mit Kommentar:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. Anschließend Speichern und schließen das Code-Fenster.

Schritt 2: Geben Sie die Formel zur Ergebniserzeugung ein

1. Geben Sie nun die folgende Formel ein und ziehen Sie am Ausfüllkästchen, um sie auf weitere Zellen zu übertragen. Dabei werden sowohl die übereinstimmenden Werte als auch die zugehörigen Kommentare gleichzeitig angezeigt. Siehe Screenshot:
   

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4,4 VLOOKUP bei als Text gespeicherten Zahlen

Angenommen, in Ihrem Datenbereich liegt die ID-Nummer in der Originaltabelle im Zahlenformat vor, während sie in den Suchzellen als Text gespeichert ist. In einem solchen Fall kann die normale VLOOKUP-Funktion einen #NV-Fehler liefern. Um dennoch die korrekten Informationen abzurufen, können Sie die TEXT- und WERT-Funktionen direkt innerhalb der VLOOKUP-Funktion nutzen. Die folgende Formel macht das möglich:

Schritt 1: Wenden und füllen Sie die folgende Formel an

Wenden Sie die folgende Formel in eine leere Zelle an und ziehen Sie anschließend am Ausfüllkästchen nach unten, um sie zu kopieren.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Ergebnis:

Sie erhalten nun die korrekten Ergebnisse wie im folgenden Screenshot gezeigt: