20+ SVERWEIS-Beispiele für Excel-Einsteiger & Fortgeschrittene Benutzer

Die VLOOKUP-Funktion ist eine der beliebtesten Funktionen in Excel. In diesem Tutorial wird schrittweise vorgestellt, wie man die VLOOKUP-Funktion in Excel mit Dutzenden von grundlegenden und fortgeschrittenen Beispielen verwendet.

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

In Excel ist die SVERWEIS-Funktion eine leistungsstarke Funktion für die meisten Excel-Benutzer. Sie ermöglicht es Ihnen, nach einem Wert in der am weitesten links stehenden Spalte des Datenbereichs zu suchen und einen passenden Wert aus einer von Ihnen angegebenen Spalte in derselben Zeile zurückzugeben, wie im folgenden Screenshot dargestellt.

Die Syntax der SVERWEIS-Funktion:

Argumente:

Funktionshinweise:

Einfache SVERWEIS Beispiele

In diesem Abschnitt werden wir über einige VLOOKUP-Formeln sprechen, die Sie häufig verwenden.

2.1 Exakte Übereinstimmung und ungefähre Übereinstimmung SVERWEIS

 2.1.1 Führen Sie eine exakte Übereinstimmung mit SVERWEIS durch

Normalerweise benötigen Sie, wenn Sie mit der SVERWEIS-Funktion nach einer genauen Übereinstimmung suchen, nur FALSE als letztes Argument.

Um beispielsweise die entsprechenden Mathematiknoten auf Basis der spezifischen ID-Nummern zu erhalten, gehen Sie wie folgt vor:

Bitte kopieren Sie die folgende Formel in eine leere Zelle (hier wähle ich G2) und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten:

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

Wie funktioniert die SVERWEIS-Formel?

Zuerst sucht es nach der ID - C1005 in der am weitesten links stehenden Spalte der Tabelle. Es geht von oben nach unten und findet den Wert in Zelle A6.

Sobald es den Wert findet, geht es direkt zur dritten Spalte und extrahiert den Wert daraus.

So erhalten Sie das Ergebnis, wie im folgenden Screenshot gezeigt:

 2.1.2 Führen Sie eine ungefähre Übereinstimmung mit SVERWEIS durch

Die ungefähre Übereinstimmung ist nützlich, um Werte zwischen Datenbereichen zu suchen. Wenn keine exakte Übereinstimmung gefunden wird, gibt die ungefähre VLOOKUP den größten Wert zurück, der kleiner als der Suchwert ist.

Wenn Sie beispielsweise den folgenden Datenbereich haben und die angegebenen Bestellungen nicht in der Spalte „Orders“ enthalten sind, wie erhalten Sie dann den nächstgelegenen Rabatt in Spalte B?

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

Kopieren Sie die folgende Formel in eine Zelle, in der Sie das Ergebnis platzieren möchten, und ziehen Sie dann den Ausfüllknauf nach unten, um diese Formel auf andere Zellen anzuwenden.

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

Ergebnis:

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

2.2 Führen Sie eine VLOOKUP durch, die die Groß-/Kleinschreibung beachtet, in Excel aus

Standardmäßig führt die VLOOKUP-Funktion eine Suche durch, die nicht zwischen Groß- und Kleinschreibung unterscheidet, was bedeutet, dass sie Klein- und Großbuchstaben als identisch behandelt. Manchmal müssen Sie jedoch möglicherweise eine groß-/kleinschreibungsabhängige Suche in Excel durchführen, was die normale VLOOKUP-Funktion möglicherweise nicht lösen kann. In diesem Fall können Sie alternative Funktionen wie INDEX und VERGLEICH mit der EXACT-Funktion oder die LOOKUP- und EXACT-Funktionen verwenden.

Zum Beispiel habe ich den folgenden Datenbereich, in dem die ID-Spalte Textzeichenfolgen in Groß- oder Kleinbuchstaben enthält. Nun möchte ich die entsprechende Mathematiknote der angegebenen ID-Nummer zurückgeben.

Schritt 1: Wenden Sie eine beliebige Formel an und füllen Sie sie in andere Zellen

Bitte kopieren Sie eine der folgenden Formeln in eine leere Zelle, in der Sie das Ergebnis erhalten möchten. Wählen Sie dann die Formelzelle aus und ziehen Sie den Ausfüllkasten nach unten in die Zellen, in denen Sie diese Formel ausfüllen möchten.

Formel 1: Drücken Sie nach dem Einfügen der Formel bitte die Tasten „Ctrl“ + „Shift“ + „Enter“.

=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 bitte die Eingabetaste.

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

Ergebnis:

Dann erhalten Sie die richtigen Ergebnisse, die Sie benötigen. Siehe Screenshot:

2.3 VLOOKUP-Werte von rechts nach links in Excel

Die VLOOKUP-Funktion sucht immer nach einem Wert in der am weitesten links stehenden Spalte eines Datenbereichs und gibt den entsprechenden Wert aus einer Spalte rechts davon zurück. Wenn Sie einen umgekehrten VLOOKUP durchführen möchten, was bedeutet, dass Sie einen bestimmten Wert in der rechten Spalte suchen und den entsprechenden Wert in der linken Spalte zurückgeben, wie im folgenden Screenshot gezeigt:

Klicken Sie hier, um die Details Schritt für Schritt zu dieser Aufgabe zu erfahren…

2.4 VLOOKUP zum zweiten, n-ten oder letzten übereinstimmenden Wert in Excel

Normalerweise wird bei der Verwendung der SVERWEIS-Funktion, wenn mehrere übereinstimmende Werte gefunden werden, nur der erste übereinstimmende Datensatz zurückgegeben. In diesem Abschnitt werde ich darüber sprechen, wie man den zweiten, n-ten oder letzten übereinstimmenden Wert in einem Datenbereich erhält.

 2.4.1 SVERWEIS und Rückgabe des 2. oder n-ten übereinstimmenden Werts

Angenommen, Sie haben eine Liste von Namen in Spalte A und die von ihnen gekaufte Schulung in Spalte B. Nun möchten Sie den 2. oder n-ten Kurs finden, den der jeweilige Kunde gekauft hat. Siehe Screenshot:

Hier kann die VLOOKUP-Funktion diese Aufgabe möglicherweise nicht direkt lösen. Sie können jedoch die INDEX-Funktion als Alternative verwenden.

Schritt 1: Formel anwenden und auf andere Zellen ausfüllen

Um beispielsweise den zweiten übereinstimmenden Wert basierend auf den gegebenen Kriterien zu erhalten, wenden Sie bitte die folgende Formel in eine leere Zelle an, und drücken Sie gleichzeitig die Tasten "Strg" + "Umschalt" + "Eingabe", um das erste Ergebnis zu erhalten. Wählen Sie dann die Formelzelle aus und ziehen Sie den Ausfüllkursor nach unten in die Zellen, in denen Sie diese Formel ausfüllen möchten.

=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 gegebenen Namen auf einmal angezeigt.

 2.4.2 SVERWEIS und Rückgabe des letzten übereinstimmenden Werts

Wenn Sie nach einem Wert suchen und den letzten übereinstimmenden Wert wie im folgenden Screenshot zurückgeben möchten, kann Ihnen dieses Tutorial zum Thema 'SVERWEIS und Rückgabe des letzten übereinstimmenden Werts' helfen, den letzten übereinstimmenden Wert detailliert zu erhalten.

2.5 VLOOKUP zum Abgleichen von Werten zwischen zwei angegebenen Werten oder Datumsangaben

Manchmal möchten Sie möglicherweise Werte oder Daten zwischen zwei Werten suchen und die entsprechenden Ergebnisse zurückgeben, wie im Screenshot unten dargestellt. In einem solchen Fall können Sie die SVERWEIS-Funktion anstelle der VLOOKUP-Funktion mit einer sortierten Tabelle verwenden.

 2.5.1 SVERWEIS zum Abgleichen von Werten zwischen zwei angegebenen Werten oder Datumsangaben mit Formel

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

Ihre ursprüngliche Tabelle sollte ein sortierter Datenbereich sein. Kopieren Sie dann die folgende Formel in eine leere Zelle oder geben Sie sie ein. Ziehen Sie anschließend den Ausfüllkursor, um diese Formel in andere benötigte Zellen zu übertragen.

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

Ergebnis:

Und jetzt erhalten Sie alle übereinstimmenden Datensätze basierend auf dem angegebenen Wert, siehe Screenshot:

 2.5.2 SVERWEIS zum Abgleichen von Werten zwischen zwei angegebenen Werten oder Datumsangaben mit einer praktischen Funktion

Wenn Sie Schwierigkeiten haben, die obige Formel zu verstehen und sich zu merken, möchte ich Ihnen hier ein einfaches Tool vorstellen – "Kutools für Excel". Mit der Funktion "Daten zwischen zwei Werten finden", können Sie mühelos das entsprechende Element basierend auf einem bestimmten Wert oder Datum zwischen zwei Werten oder Daten zurückgeben.

1. Klicken Sie auf „Kutools“ > „Erweiterte SVERWEIS“ > „Daten zwischen zwei Werten finden“, um diese Funktion zu aktivieren.
2. Geben Sie dann die Operationen im Dialogfeld entsprechend Ihren Daten an.

2.6 Verwendung von Platzhaltern für Teilübereinstimmungen in der SVERWEIS-Funktion

In Excel können Platzhalter innerhalb der SVERWEIS-Funktion verwendet werden, was Ihnen ermöglicht, eine teilweise Übereinstimmung mit einem Suchwert durchzuführen. Zum Beispiel können Sie SVERWEIS verwenden, um basierend auf einem Teil des Suchwerts einen passenden Wert aus einer Tabelle zurückzugeben.

Angenommen, ich habe einen Datenbereich wie im folgenden Screenshot gezeigt. Nun möchte ich die Punktzahl basierend auf dem Vornamen (nicht dem vollständigen Namen) extrahieren. Wie kann ich diese Aufgabe in Excel lösen?

Schritt 1: Wenden Sie die Formel an und füllen Sie die anderen Zellen aus

Bitte kopieren Sie die folgende Formel in eine leere Zelle, oder geben Sie sie ein, und ziehen Sie dann den Ausfüllkursor, um diese Formel in andere benötigte Zellen zu übertragen:

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

Ergebnis:

Und alle übereinstimmenden Ergebnisse wurden wie im folgenden Screenshot dargestellt zurückgegeben:

2.7 SVERWEIS-Werte aus einem anderen Arbeitsblatt

Normalerweise arbeiten Sie möglicherweise mit mehr als einem Arbeitsblatt. Die VLOOKUP-Funktion kann verwendet werden, um Daten von einem anderen Blatt zu suchen, genau wie auf einem einzelnen Arbeitsblatt.

Wenn Sie beispielsweise zwei Arbeitsblätter haben, wie im folgenden Screenshot gezeigt, und die entsprechenden Daten aus dem von Ihnen angegebenen Arbeitsblatt suchen und zurückgeben möchten, gehen Sie bitte wie folgt vor:

Schritt 1: Wenden Sie die Formel an und füllen Sie die anderen Zellen aus

Bitte geben Sie die folgende Formel in eine leere Zelle ein oder kopieren Sie sie dorthin, wo Sie die übereinstimmenden Elemente erhalten möchten. Ziehen Sie dann den Ausfüllkursor nach unten in die Zellen, auf die Sie diese Formel anwenden möchten.

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

Ergebnis:

Sie erhalten die entsprechenden Ergebnisse, die Sie benötigen, siehe Screenshot:

2.8 VLOOKUP-Werte aus einer anderen Arbeitsmappe

Dieser Abschnitt behandelt die Suche und Rückgabe der übereinstimmenden Werte aus einer anderen Arbeitsmappe mithilfe der VLOOKUP-Funktion.

Angenommen, Sie haben zwei Arbeitsmappen. Die erste Arbeitsmappe enthält eine Liste von Produkten und deren jeweilige Kosten. In der zweiten Arbeitsmappe möchten Sie die entsprechenden Kosten für jedes Produkt wie im folgenden Screenshot dargestellt extrahieren.

Schritt 1: Wenden Sie die Formel an

Öffnen Sie beide Arbeitsmappen, die Sie verwenden möchten, und wenden Sie dann die folgende Formel in einer Zelle an, in der Sie das Ergebnis in der zweiten Arbeitsmappe anzeigen möchten. Ziehen Sie anschließend diese Formel in die anderen Zellen, die Sie benötigen.

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

Ergebnis:

2.9 Gib Leerzeichen oder spezifischen Text statt 0 oder #N/A Fehler zurück

Normalerweise gibt die VLOOKUP-Funktion einen entsprechenden Wert zurück. Wenn die übereinstimmende Zelle jedoch leer ist, wird 0 zurückgegeben. Und wenn der übereinstimmende Wert nicht gefunden wird, erhalten Sie einen Fehlerwert #NV, wie im folgenden Screenshot dargestellt. Wenn Sie stattdessen eine leere Zelle oder einen bestimmten Wert anzeigen möchten, anstatt 0 oder #NV, kann Ihnen dieses Tutorial „Erweiterte SVERWEIS: Rückgabe einer leeren Zelle oder eines bestimmten Werts statt 0 oder NV“ helfen.

Erweiterte SVERWEIS Beispiele

3.1 Zwei-Wege-Suche (VLOOKUP in Zeile und Spalte)

Manchmal müssen Sie möglicherweise eine zweidimensionale Suche durchführen, was bedeutet, dass Sie gleichzeitig in Zeile und Spalte nach einem Wert suchen. Zum Beispiel, wenn Sie den folgenden Datenbereich haben und Sie müssen den Wert für ein bestimmtes Produkt in einem bestimmten Quartal ermitteln. Dieser Abschnitt stellt eine Formel vor, um diese Aufgabe in Excel zu bewältigen.

In Excel können Sie eine Kombination aus den Funktionen SVERWEIS und VERGLEICH verwenden, um eine zweidimensionale Suche durchzuführen.

Bitte wenden Sie die folgende Formel in einer leeren Zelle an, und drücken Sie dann die Eingabetaste, um das Ergebnis zu erhalten.

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

3.2 SVERWEIS passender Wert basierend auf zwei oder mehr Kriterien

Es ist einfach, den passenden Wert auf Basis eines Kriteriums zu finden, aber was können Sie tun, wenn Sie zwei oder mehr Kriterien haben?

 3.2.1 VLOOKUP zum Abgleichen von Werten basierend auf zwei oder mehr Kriterien mit Formeln

In diesem Fall können die SVERWEIS- oder VERGLEICH- und INDEX-Funktionen in Excel Ihnen helfen, diese Aufgabe schnell und einfach zu lösen.

Wenn Sie beispielsweise die folgende Datentabelle haben und den passenden Preis basierend auf einem bestimmten Produkt und einer bestimmten Größe ermitteln möchten, können Ihnen die folgenden Formeln helfen.

Schritt 1: Wenden Sie eine der folgenden 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 "Strg" + "Umschalt" + "Eingabe".

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

Ergebnis:

 3.2.2 SVERWEIS-Wertabgleich basierend auf zwei oder mehr Kriterien mit Kutools für Excel

Es kann schwierig sein, sich die oben genannten komplexen Formeln zu merken, die wiederholt angewendet werden müssen, was Ihre Arbeitseffizienz verlangsamen kann. Allerdings bietet "Kutools für Excel" eine Funktion namens "Mehrere Bedingungen suchen", mit der Sie das entsprechende Ergebnis basierend auf einer oder mehreren Bedingungen mit nur wenigen Klicks zurückgeben können.

1. Klicken Sie auf „Kutools“ > „Erweiterte SVERWEIS“ > „Mehrere Bedingungen suchen“, um diese Funktion zu aktivieren.
2. Geben Sie dann die Operationen im Dialogfeld entsprechend Ihren Daten an.

3.3 VLOOKUP, um mehrere Werte mit einem oder mehreren Kriterien zurückzugeben

In Excel sucht die VLOOKUP-Funktion nach einem Wert und gibt nur den ersten übereinstimmenden Wert zurück, wenn mehrere entsprechende Werte gefunden werden. Manchmal möchten Sie jedoch alle entsprechenden Werte in einer Zeile, in einer Spalte oder in einer einzelnen Zelle zurückgeben. Dieser Abschnitt behandelt, wie man mehrere übereinstimmende Werte mit einer oder mehreren Bedingungen in einer Arbeitsmappe zurückgibt.

 3.3.1 Alle übereinstimmenden Werte basierend auf einer oder mehreren Bedingungen horizontal mit SVERWEIS suchen

Angenommen, Sie haben eine Datentabelle, die Länder, Städte und Namen im Bereich A1:C14 enthält, und möchten nun alle Namen horizontal ausgeben, die aus den „USA“ stammen, wie im folgenden Screenshot dargestellt. Um diese Aufgabe zu lösen, klicken Sie bitte hier, um das Ergebnis Schritt für Schritt zu erhalten.

 3.3.2 Alle übereinstimmenden Werte basierend auf einer oder mehreren Bedingungen vertikal suchen

Wenn Sie nach bestimmten Kriterien einen SVERWEIS durchführen und alle übereinstimmenden Werte vertikal zurückgeben möchten, wie im folgenden Screenshot dargestellt, klicken Sie bitte hier, um die ausführliche Lösung zu erhalten.

 3.3.3 Alle übereinstimmenden Werte basierend auf einer oder mehreren Bedingungen in eine einzige Zelle übernehmen

Wenn Sie mit VLOOKUP mehrere übereinstimmende Werte in eine einzelne Zelle mit einem bestimmten Trennzeichen zurückgeben möchten, kann Ihnen die neue Funktion TEXTVERBINDEN dabei helfen, diese Aufgabe schnell und einfach zu erledigen.

3.4 SVERWEIS, um die gesamte Zeile einer übereinstimmenden Zelle zurückzugeben

In diesem Abschnitt werde ich darüber sprechen, wie man mit der VLOOKUP-Funktion die gesamte Zeile eines übereinstimmenden Werts abruft.

Schritt 1: Wenden Sie die folgende Formel an

Bitte kopieren Sie die folgende Formel in eine leere Zelle, in der Sie das Ergebnis ausgeben möchten, oder geben Sie sie ein, und drücken Sie die Eingabetaste, um den ersten Wert zu erhalten. Ziehen Sie dann die Formelzelle nach rechts, bis die Daten der gesamten Zeile angezeigt werden.

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

Ergebnis:

Jetzt können Sie sehen, dass die Daten der gesamten Zeile zurückgegeben werden. Siehe Screenshot:

3.5 Verschachtelte SVERWEIS-Funktion in Excel

Manchmal müssen Sie möglicherweise Werte nachschlagen, die über mehrere Tabellen verknüpft sind. In diesem Fall können Sie mehrere VLOOKUP-Funktionen ineinander verschachteln, um den endgültigen Wert zu erhalten.

Angenommen, ich habe ein Arbeitsblatt, das zwei separate Tabellen enthält. Die erste Tabelle listet alle Produktnamen zusammen mit den entsprechenden Verkäufern auf. Die zweite Tabelle zeigt die Gesamtumsätze jedes Verkäufers. Wenn Sie nun die Umsätze jedes Produkts finden möchten, wie im folgenden Screenshot gezeigt, können Sie die VLOOKUP-Funktion schachteln, um diese Aufgabe zu erledigen.

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

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

Bitte wenden Sie die folgende Formel in eine leere Zelle an, und ziehen Sie dann das Ausfüllkästchen nach unten in die Zellen, auf die Sie diese Formel anwenden möchten.

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

Ergebnis:

Nun erhalten Sie das Ergebnis, wie im folgenden Screenshot gezeigt:

3.6 Überprüfen, ob ein Wert basierend auf einer Listen-Daten in einer anderen Spalte vorhanden ist

Die VLOOKUP-Funktion kann Ihnen auch helfen, zu überprüfen, ob Werte basierend auf der Datenliste in einer anderen Spalte vorhanden sind. Zum Beispiel, wenn Sie die Namen in Spalte C suchen und nur Ja oder Nein zurückgeben möchten, falls der Name in Spalte A gefunden wird oder nicht, wie im folgenden Screenshot dargestellt.

Schritt 1: Wenden Sie die folgende Formel an

Bitte wenden Sie die folgende Formel in eine leere Zelle an, und ziehen Sie dann das Ausfüllkästchen nach unten in die Zellen, in denen Sie diese Formel ausfüllen möchten.

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

Ergebnis:

Und Sie erhalten das Ergebnis, das Sie benötigen, siehe Screenshot:

3.7 SVERWEIS und Summieren 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 in mehreren Spalten oder Zeilen summieren. In diesem Abschnitt werden einige Formeln vorgestellt, die Ihnen helfen können, diese Aufgabe zu erledigen.

 3.7.1 SVERWEIS und Summieren aller übereinstimmenden Werte in einer Zeile oder mehreren Zeilen

Angenommen, Sie haben eine Produktliste mit Verkäufen für mehrere Monate, wie im folgenden Screenshot dargestellt. Nun müssen Sie alle Bestellungen in allen Monaten basierend auf den angegebenen Produkten summieren.

Schritt 1: Wenden Sie die folgende Formel an

Bitte kopieren Sie die folgende Formel in eine leere Zelle, oder geben Sie sie ein, und drücken Sie dann gleichzeitig die Tasten "Strg" + "Umschalt" + "Eingabe", um das erste Ergebnis zu erhalten. Ziehen Sie anschließend das Ausfüllkästchen nach unten, um diese Formel in andere benötigte Zellen zu kopieren.

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

Ergebnis:

Alle Werte in einer Zeile des ersten übereinstimmenden Wertes wurden zusammen summiert, siehe Screenshot:

 3.7.2 SVERWEIS und Summieren aller übereinstimmenden Werte in einer Spalte oder mehreren Spalten

Wenn Sie den Gesamtwert für bestimmte Monate summieren möchten, wie im Screenshot unten dargestellt. Die normale SVERWEIS-Funktion kann Ihnen möglicherweise nicht helfen, hier sollten Sie die Funktionen SUMME, INDEX und VERGLEICH zusammen anwenden, um eine Formel zu erstellen.

Schritt 1: Wenden Sie die folgende Formel an

Wenden Sie die folgende Formel in einer leeren Zelle an, und ziehen Sie dann das Ausfüllkästchen nach unten, um diese Formel in andere Zellen zu kopieren.

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

Ergebnis:

Nun wurden die ersten übereinstimmenden Werte basierend auf dem spezifischen Monat in einer Spalte zusammengefasst, siehe Screenshot:

 3.7.3 SVERWEIS und Summieren der ersten oder aller übereinstimmenden Werte mit Kutools für Excel

Vielleicht sind die oben genannten Formeln schwer zu merken. In diesem Fall empfehle ich Ihnen eine leistungsstarke Funktion: „Suchen und Summieren“ von „Kutools für Excel“. Mit dieser Funktion können Sie Werte mit Vlookup suchen und die ersten übereinstimmenden oder alle übereinstimmenden Werte in Zeilen oder Spalten so einfach wie möglich summieren.

1. Klicken Sie auf „Kutools“ > „Erweiterte SVERWEIS“ > „Suchen und Summieren“, um diese Funktion zu aktivieren.
2. Legen Sie dann die Operationen im Dialogfeld entsprechend Ihren Anforderungen fest.

 3.7.4 SVERWEIS und Summieren aller übereinstimmenden Werte sowohl in Zeilen als auch in Spalten

Wenn Sie die Werte summieren möchten, wenn Sie sowohl Spalte als auch Zeile abgleichen müssen, zum Beispiel, um den Gesamtwert des Produkts Pullover im Monat März zu erhalten, wie im folgenden Screenshot gezeigt.

Hier können Sie die SUMMENPRODUKT-Funktion verwenden, um diese Aufgabe zu erledigen.

Bitte wenden Sie die folgende Formel in einer Zelle an, und drücken Sie dann die Eingabetaste, um das Ergebnis zu erhalten, siehe Screenshot:

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

3.8 SVERWEIS zum Zusammenführen von zwei Tabellen basierend auf Schlüsselspalten

In Ihrer täglichen Arbeit müssen Sie beim Analysieren von Daten möglicherweise alle notwendigen Informationen basierend auf einer oder mehreren Schlüsselspalten in einer einzigen Tabelle zusammenfassen. Um diese Aufgabe zu erledigen, können Sie die Funktionen INDEX und VERGLEICH anstelle der SVERWEIS-Funktion verwenden.

 3.8.1 SVERWEIS zum Zusammenführen von zwei Tabellen basierend auf einer Schlüsselspalte

Angenommen, Sie haben zwei Tabellen: Die erste Tabelle enthält die Produktdaten und Namen, und die zweite Tabelle enthält die Produktdaten und Bestellungen. Nun möchten Sie diese beiden Tabellen kombinieren, indem Sie die gemeinsame Produktespalte abgleichen und in eine einzige Tabelle zusammenführen.

Schritt 1: Wenden Sie die folgende Formel an

Bitte wenden Sie die folgende Formel in einer leeren Zelle an. Ziehen Sie dann den Ausfüllknauf nach unten in die Zellen, auf die Sie diese Formel anwenden 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 mit der ersten Tabelle verbunden ist.

 3.8.2 SVERWEIS zum Zusammenführen von zwei Tabellen basierend auf mehreren Schlüsselspalten

Wenn die beiden Tabellen, die Sie verbinden möchten, mehrere Schlüsselspalten haben, folgen Sie bitte den untenstehenden Schritten, um die Tabellen auf Basis dieser gemeinsamen Spalten zusammenzuführen.

Die allgemeine Formel lautet:

Schritt 1: Wenden Sie die folgende Formel an

Bitte wenden Sie die folgende Formel in eine leere Zelle an, in der Sie das Ergebnis platzieren möchten, und drücken Sie dann gleichzeitig die Tasten "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 den Ausfüllknauf, um diese Formel nach Bedarf in andere Zellen zu kopieren:

3.9 VLOOKUP zum Abgleichen von Werten über mehrere Arbeitsblätter hinweg

Müssen Sie jemals eine SVERWEIS über mehrere Arbeitsblätter in Excel durchführen? Zum Beispiel, wenn Sie drei Arbeitsblätter mit Datenbereichen haben und Sie bestimmte Werte basierend auf Kriterien aus diesen Blättern abrufen möchten, können Sie dem schrittweisen Tutorial „SVERWEIS Werte über mehrere Arbeitsblätter“ folgen, um diese Aufgabe zu erledigen.

SVERWEIS übernimmt Zellformatierung bei übereinstimmenden Werten

Bei der Suche nach übereinstimmenden Werten werden die ursprünglichen Zellenformatierungen wie Schriftfarbe, Hintergrundfarbe, Datenformat usw. nicht beibehalten. Um die Zellen- oder Datenformatierung beizubehalten, wird dieser Abschnitt einige Tricks zur Lösung dieser Aufgaben vorstellen.

4.1 SVERWEIS-Wert mit Übereinstimmung und Beibehaltung der Zellfarbe, Schriftformatierung

Wie wir alle wissen, kann die normale SVERWEIS-Funktion nur den übereinstimmenden Wert aus einem anderen Datenbereich abrufen. Es kann jedoch vorkommen, dass Sie den entsprechenden Wert zusammen mit der Zellformatierung, wie zum Beispiel der Füllfarbe, Schriftfarbe und Schriftart, erhalten möchten. In diesem Abschnitt werden wir besprechen, wie man übereinstimmende Werte abruft und dabei die Quellformatierung in Excel beibehält.

Bitte gehen Sie wie folgt vor, um den entsprechenden Wert zusammen mit der Zellformatierung zu suchen und zurückzugeben:

Schritt 1: Kopieren Sie den Code 1 in das Modul des Tabellenblatts

1. In dem Arbeitsblatt, das die Daten enthält, die Sie mit SVERWEIS suchen möchten, klicken Sie mit der rechten Maustaste auf die Registerkarte des Blatts und wählen Sie "Code anzeigen" aus dem Kontextmenü. Siehe Screenshot:
   
2. Im geöffneten Fenster "Microsoft Visual Basic for Applications" kopieren Sie bitte den folgenden VBA-Code in das Codefenster.
3. VBA-Code 1: SVERWEIS, um die Zellformatierung zusammen mit dem Suchwert zu erhalten

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 den Code 2 in das Modulfenster

1. Noch im Fenster "Microsoft Visual Basic for Applications" auf "Einfügen" > "Modul" klicken und dann den folgenden VBA-Code 2 in das "Modul"-Fenster kopieren.
2. VBA-Code 2: SVERWEIS, um die Zellformatierung zusammen mit dem Suchwert zu erhalten

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 VBA-Projekt aus

1. Nachdem Sie die oben genannten Codes eingefügt haben, klicken Sie auf "Tools" > "References" im Fenster "Microsoft Visual Basic for Applications". Aktivieren Sie dann das Kontrollkästchen "Microsoft Scripting Runtime" im Dialogfeld "References – VBAProject". Siehe Screenshots:
2. Klicken Sie dann auf "OK", um das Dialogfeld zu schließen, und speichern Sie anschließend die Codeansicht und schließen Sie sie.

Schritt 4: Geben Sie die Formel zur Ermittlung des Ergebnisses ein

1. Gehen Sie nun zurück zum Arbeitsblatt und wenden Sie die folgende Formel an. Ziehen Sie dann den Ausfüllkursor nach unten, um alle Ergebnisse zusammen mit deren Formatierung zu erhalten. Siehe Screenshot:
   

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

   
   

4.2 Behalten Sie das Datumsformat bei einem von SVERWEIS zurückgegebenen Wert bei

Wenn Sie die VLOOKUP-Funktion verwenden, um nach einem Wert mit Datumsformat zu suchen und zurückzugeben, wird das zurückgegebene Ergebnis möglicherweise als Zahl angezeigt. Um das Datumsformat im zurückgegebenen Ergebnis beizubehalten, sollten Sie die VLOOKUP-Funktion innerhalb der TEXT-Funktion platzieren.

Schritt 1: Wenden Sie die folgende Formel an

Bitte wenden Sie die folgende Formel in eine leere Zelle an. Ziehen Sie dann das Ausfüllkästchen, um diese Formel in andere Zellen zu kopieren.

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

Ergebnis:

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

4.3 Zellkommentar aus SVERWEIS zurückgeben

Haben Sie jemals sowohl die Daten der übereinstimmenden Zelle als auch den dazugehörigen Kommentar mithilfe von SVERWEIS in Excel abrufen müssen, wie im folgenden Screenshot gezeigt? Falls ja, kann Ihnen die unten stehende benutzerdefinierte Funktion dabei helfen, diese Aufgabe 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 dann den folgenden Code in das "Modul"-Fenster.
   VBA-Code: SVERWEIS und Rückgabe des übereinstimmenden Werts mit Zellenkommentar:

   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. Dann speichern und das Codefenster schließen.

Schritt 2: Geben Sie die Formel ein, um das Ergebnis zu erhalten

1. Geben Sie nun die folgende Formel ein, und ziehen Sie den Ausfüllknauf, um diese Formel in andere Zellen zu kopieren. Sie gibt sowohl die übereinstimmenden Werte als auch Kommentare gleichzeitig zurück, siehe Screenshot:
   

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

   
   

4.4 VLOOKUP Zahlen, die als Text gespeichert sind

Angenommen, ich habe einen Datenbereich, in dem die ID-Nummer in der Originaltabelle im Zahlenformat vorliegt und die ID-Nummer in den Suchzellen als Text gespeichert ist. In diesem Fall können Sie auf einen #NV-Fehler stoßen, wenn Sie die normale SVERWEIS-Funktion verwenden. Um die richtigen Informationen abzurufen, können Sie die TEXT- und WERT-Funktionen innerhalb der SVERWEIS-Funktion einfügen. Unten sehen Sie die Formel, um dies zu erreichen:

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

Bitte wenden Sie die folgende Formel in eine leere Zelle an, und ziehen Sie dann das Ausfüllkästchen nach unten, um diese Formel zu kopieren.

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

Ergebnis:

Nun erhalten Sie die richtigen Ergebnisse, wie im folgenden Screenshot gezeigt: