Die SVERWEIS-Funktion ist eine der beliebtesten Funktionen in Excel. In diesem Tutorial erfahren Sie Schritt für Schritt, wie Sie die VLOOKUP-Funktion in Excel anhand von Dutzenden grundlegender und fortgeschrittener Beispiele verwenden.

Einführung der VLOOKUP-Funktion - Syntax und Argumente

In Excel ist die VLOOKUP-Funktion für die meisten Excel-Benutzer eine leistungsstarke Funktion. Sie ermöglicht es Ihnen, ganz links im Datenbereich nach einem Wert zu suchen und einen passenden Wert in derselben Zeile aus einer Spalte zurückzugeben, die Sie wie im folgenden Screenshot angegeben haben .

Die Syntax der VLOOKUP-Funktion:

Argumente:

Funktionshinweise:

Grundlegende VLOOKUP-Beispiele

In diesem Abschnitt werden wir über einige Vlookup-Formeln sprechen, die Sie häufig verwendet haben.

2.1 Genaue Übereinstimmung und ungefähre Übereinstimmung SVERWEIS

 2.1.1 Führen Sie einen SVERWEIS mit exakter Übereinstimmung durch

Wenn Sie mit der VLOOKUP-Funktion nach einer genauen Übereinstimmung suchen, müssen Sie normalerweise nur FALSE als letztes Argument verwenden.

Gehen Sie folgendermaßen vor, um beispielsweise die entsprechenden Mathe-Ergebnisse basierend auf den spezifischen ID-Nummern zu erhalten:

Bitte kopieren Sie die folgende Formel, fügen Sie sie in eine leere Zelle ein (hier wähle ich G2) und drücken Sie Enter Schlüssel, um das Ergebnis zu erhalten:

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

Wie funktioniert die SVERWEIS-Formel?

Zunächst wird in der Spalte ganz links der Tabelle nach der ID C1005 gesucht. Es geht von oben nach unten und findet den Wert in Zelle A6.

Sobald der Wert gefunden wurde, geht er nach rechts in die dritte Spalte und extrahiert den darin enthaltenen Wert.

Sie erhalten also das Ergebnis wie im folgenden Screenshot gezeigt:

 2.1.2 Führen Sie einen ungefähren Übereinstimmungs-SVERWEIS durch

Die ungefähre Übereinstimmung ist nützlich für die Suche nach Werten zwischen Datenbereichen. Wenn keine genaue Übereinstimmung gefunden wird, gibt der ungefähre VLOOKUP den größten Wert zurück, der kleiner als der Suchwert ist.

Wenn Sie beispielsweise über den folgenden Datenbereich verfügen und die angegebenen Bestellungen nicht in der Spalte „Bestellungen“ 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 auf andere Zellen aus

Kopieren Sie die folgende Formel und fügen Sie sie in eine Zelle ein, in der Sie das Ergebnis einfügen möchten. Ziehen Sie dann den Füllpunkt nach unten, um diese Formel auf andere Zellen anzuwenden.

=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 in Excel einen SVERWEIS unter Berücksichtigung der Groß- und Kleinschreibung durch

Standardmäßig führt die VLOOKUP-Funktion eine Suche ohne Berücksichtigung der Groß- und Kleinschreibung durch, was bedeutet, dass sie Klein- und Großbuchstaben als identisch behandelt. Manchmal müssen Sie möglicherweise eine Suche unter Berücksichtigung der Groß- und Kleinschreibung in Excel durchführen. Die normale SVERWEIS-Funktion löst das Problem möglicherweise nicht. In diesem Fall können Sie alternative Funktionen wie INDEX und MATCH mit der EXACT-Funktion oder den LOOKUP- und EXACT-Funktionen verwenden.

Zum Beispiel habe ich den folgenden Datenbereich, dessen ID-Spalte eine Textzeichenfolge mit Groß- oder Kleinbuchstaben enthält. Jetzt möchte ich die entsprechende mathematische Bewertung der angegebenen ID-Nummer zurückgeben.

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

Bitte kopieren Sie eine der folgenden Formeln und fügen Sie sie in eine leere Zelle ein, in der Sie das Ergebnis erhalten möchten. Wählen Sie dann die Formelzelle aus und ziehen Sie den Füllpunkt nach unten zu den Zellen, in die Sie diese Formel füllen möchten.

Formel 1: Nachdem Sie die Formel eingefügt haben, drücken Sie bitte Strg + Umschalt + Enter Schlüssel.

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

Formel 2: Nachdem Sie die Formel eingefügt haben, drücken Sie bitte Enter Key.

=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 SVERWEIS-Werte von rechts nach links in Excel

Die SVERWEIS-Funktion sucht immer nach einem Wert in der Spalte ganz links eines Datenbereichs und gibt den entsprechenden Wert aus einer Spalte rechts zurück. Wenn Sie einen umgekehrten SVERWEIS durchführen möchten, d. h. einen bestimmten Wert in der rechten Spalte suchen und den entsprechenden Wert in der linken Spalte zurückgeben möchten, wie im folgenden Screenshot gezeigt:

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

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

Wenn bei Verwendung der Vlookup-Funktion mehrere übereinstimmende Werte gefunden werden, wird normalerweise 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 VLOOKUP und den zweiten oder n-ten passenden Wert zurückgeben

Angenommen, Sie haben in Spalte A eine Liste mit Namen und in Spalte B den Schulungskurs, den sie gekauft haben. Jetzt suchen Sie nach dem zweiten oder x-ten Schulungskurs, den der angegebene Kunde gekauft hat. Siehe Screenshot:

Hier kann die SVERWEIS-Funktion diese Aufgabe möglicherweise nicht direkt lösen. Sie können jedoch alternativ auch die INDEX-Funktion verwenden.

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

Um beispielsweise den zweiten passenden Wert basierend auf den angegebenen Kriterien zu erhalten, wenden Sie bitte die folgende Formel in eine leere Zelle an und drücken Sie Strg + Umschalt + Enter Tasten zusammen, um das erste Ergebnis zu erhalten. Wählen Sie dann die Formelzelle aus und ziehen Sie den Füllpunkt nach unten zu den Zellen, in die Sie diese Formel fü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:

Jetzt werden alle zweiten übereinstimmenden Werte, die auf den angegebenen Namen basieren, auf einmal angezeigt.

 2.4.2 VLOOKUP und den letzten übereinstimmenden Wert zurückgeben

Wenn Sie den letzten übereinstimmenden Wert wie im folgenden Screenshot anzeigen und zurückgeben möchten, klicken Sie hier VLOOKUP und den letzten übereinstimmenden Wert zurückgeben Das Tutorial kann Ihnen dabei helfen, den letzten übereinstimmenden Wert im Detail zu ermitteln.

2.5 VLOOKUP-Übereinstimmung von Werten zwischen zwei gegebenen Werten oder Daten

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

 2.5.1 SVERWEIS zum Abgleichen von Werten zwischen zwei gegebenen Werten oder Daten mit Formel

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

Ihre Originaltabelle sollte ein sortierter Datenbereich sein. Kopieren Sie dann die folgende Formel oder geben Sie sie in eine leere Zelle ein. Ziehen Sie dann den Füllpunkt, um diese Formel in andere benötigte Zellen zu füllen.

=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 gegebenen Werten oder Daten mit einer praktischen Funktion

Wenn Sie Schwierigkeiten haben, sich die obige Formel zu merken und zu verstehen, stelle ich Ihnen hier ein einfaches Tool vor: Kutools for ExcelMit seinen LOOKUP zwischen zwei Werten Mit dieser Funktion können Sie den entsprechenden Artikel ganz einfach basierend auf dem spezifischen Wert oder Datum zwischen zwei Werten oder Daten zurückgeben.

1. Klicken Sie Kutoolen > Super NACHSCHLAG > LOOKUP zwischen zwei Werten , um diese Funktion zu aktivieren.
2. Geben Sie dann die Vorgänge im Dialogfeld basierend auf Ihren Daten an.

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

In Excel können die Platzhalter innerhalb der SVERWEIS-Funktion verwendet werden, wodurch Sie eine teilweise Übereinstimmung mit einem Suchwert durchführen können. Beispielsweise können Sie VLOOKUP verwenden, um übereinstimmende Werte aus einer Tabelle basierend auf einem Teil eines Suchwerts zurückzugeben.

Angenommen, ich habe eine Reihe von Daten wie im folgenden Screenshot gezeigt. Jetzt möchte ich die Punktzahl basierend auf dem Vornamen (nicht dem vollständigen Namen) extrahieren. Wie könnte diese Aufgabe in Excel gelöst werden?

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

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

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

Ergebnis:

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

2.7 SVERWEIS-Werte aus einem anderen Arbeitsblatt

Normalerweise müssen Sie möglicherweise mit mehr als einem Arbeitsblatt arbeiten. Die VLOOKUP-Funktion kann verwendet werden, um Daten aus einem anderen Blatt genauso nachzuschlagen wie auf einem Arbeitsblatt.

Sie haben beispielsweise zwei Arbeitsblätter (siehe Abbildung unten). Führen Sie die folgenden Schritte aus, um die entsprechenden Daten aus dem von Ihnen angegebenen Arbeitsblatt nachzuschlagen und zurückzugeben: Führen Sie die folgenden Schritte aus:

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

Bitte geben Sie die folgende Formel ein oder kopieren Sie sie in eine leere Zelle, in der Sie die passenden Artikel erhalten möchten. Ziehen Sie dann den Füllpunkt nach unten zu den Zellen, auf die Sie diese Formel anwenden möchten.

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

Ergebnis:

Sie erhalten bei Bedarf die entsprechenden Ergebnisse, siehe Screenshot:

2.8 SVERWEIS-Werte aus einer anderen Arbeitsmappe

In diesem Abschnitt geht es um 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 der Produkte und deren jeweilige Kosten. In der zweiten Arbeitsmappe möchten Sie die entsprechenden Kosten für jeden Produktartikel extrahieren, wie im folgenden Screenshot gezeigt.

Schritt 1: Tragen Sie die Formel auf und füllen Sie sie aus

Öffnen Sie beide Arbeitsmappen, die Sie verwenden möchten, und wenden Sie dann die folgende Formel in eine Zelle an, in der Sie das Ergebnis in die zweite Arbeitsmappe einfügen möchten. Ziehen Sie diese Formel dann per Drag & Drop in andere Zellen, die Sie benötigen, und kopieren Sie sie

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

Ergebnis:

2.9 Geben Sie einen leeren oder spezifischen Text anstelle von 0 oder #N/A-Fehler zurück

Wenn Sie die VLOOKUP-Funktion verwenden, um einen entsprechenden Wert zurückzugeben, wird normalerweise 0 zurückgegeben, wenn die übereinstimmende Zelle leer ist. Und wenn der übereinstimmende Wert nicht gefunden wird, erhalten Sie den Fehlerwert #N/A, wie in der Abbildung gezeigt Screenshot unten. Wenn Sie eine leere Zelle oder einen bestimmten Wert anstelle von 0 oder #N/A anzeigen möchten, ist dies der Fall VLOOKUP, um einen leeren oder spezifischen Wert anstelle von 0 oder N/A zurückzugeben Das Tutorial kann Ihnen einen Gefallen tun.

Erweiterte VLOOKUP-Beispiele

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

Manchmal müssen Sie möglicherweise eine zweidimensionale Suche durchführen, d. h., Sie müssen gleichzeitig in Zeile und Spalte nach einem Wert suchen. Wenn Sie beispielsweise über den folgenden Datenbereich verfügen und möglicherweise den Wert für ein bestimmtes Produkt in einem bestimmten Quartal ermitteln müssen. In diesem Abschnitt wird eine Formel für die Bearbeitung dieses Jobs in Excel vorgestellt.

In Excel können Sie eine Kombination aus VLOOKUP- und MATCH-Funktionen verwenden, um eine bidirektionale Suche durchzuführen.

Bitte wenden Sie die folgende Formel in eine leere Zelle an und drücken Sie dann Enter Schlüssel, 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 für Sie einfach, den Übereinstimmungswert anhand eines Kriteriums zu ermitteln. Was können Sie jedoch tun, wenn Sie über zwei oder mehr Kriterien verfügen?

 3.2.1 VLOOKUP-Übereinstimmungswert basierend auf zwei oder mehr Kriterien mit Formeln

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

Zum Beispiel habe ich die folgende Datentabelle, um den passenden Preis basierend auf dem spezifischen Produkt und der Größe zurückzugeben. Die folgenden Formeln können Ihnen helfen.

Schritt 1: Wenden Sie eine beliebige Formel an

Formel 1: Nachdem Sie die Formel eingefügt haben, drücken Sie bitte Enter Key.

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

Formel 2: Nachdem Sie die Formel eingefügt haben, drücken Sie bitte Strg + Umschalt + Enter Schlüssel.

=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 einer intelligenten Funktion

Es kann schwierig sein, sich die oben genannten komplexen Formeln zu merken, die wiederholt angewendet werden müssen, was Ihre Arbeitseffizienz beeinträchtigen kann. Jedoch, Kutools for Excel bietet eine Suche nach mehreren Bedingungen Funktion, mit der Sie mit nur wenigen Klicks das entsprechende Ergebnis basierend auf einer oder mehreren Bedingungen zurückgeben können.

1. Klicken Sie Kutoolen > Super NACHSCHLAG > Suche nach mehreren Bedingungen , um diese Funktion zu aktivieren.
2. Geben Sie dann die Vorgänge im Dialogfeld basierend auf Ihren Daten an.

3.3 SVERWEIS zur Rückgabe mehrerer Werte mit einem oder mehreren Kriterien

In Excel sucht die SVERWEIS-Funktion nach einem Wert und gibt nur dann den ersten übereinstimmenden Wert zurück, wenn mehrere entsprechende Werte gefunden werden. Manchmal möchten Sie möglicherweise alle entsprechenden Werte in einer Zeile, einer Spalte oder einer einzelnen Zelle zurückgeben. In diesem Abschnitt wird erläutert, wie mehrere übereinstimmende Werte mit einer oder mehreren Bedingungen in einer Arbeitsmappe zurückgegeben werden.

 3.3.1 SVERWEIS aller übereinstimmenden Werte basierend auf einer oder mehreren Bedingungen horizontal

Angenommen, Sie haben eine Datentabelle, die Land, Stadt und Namen im Bereich A1:C14 enthält, und möchten nun alle Namen horizontal zurückgeben, die aus „USA“ stammen, wie im folgenden Screenshot gezeigt. Bitte lösen Sie diese Aufgabe Klicken Sie hier, um das Ergebnis Schritt für Schritt zu erhalten.

 3.3.2 VLOOKUP aller übereinstimmenden Werte basierend auf einer oder mehreren Bedingungen vertikal

Wenn Sie eine Vlookup-Suche durchführen und alle übereinstimmenden Werte basierend auf bestimmten Kriterien vertikal zurückgeben müssen, wie im folgenden Screenshot gezeigt, Bitte klicken Sie hier, um die Lösung im Detail zu erhalten.

 3.3.3 VLOOKUP aller übereinstimmenden Werte basierend auf einer oder mehreren Bedingungen in einer einzelnen Zelle

Wenn Sie eine Vsuche durchführen und mehrere übereinstimmende Werte in einer einzelnen Zelle mit dem angegebenen Trennzeichen zurückgeben möchten, Die neue Funktion von TEXTJOIN kann Ihnen dabei helfen, diese Aufgabe schnell und einfach zu lösen.

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

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

Schritt 1: Tragen Sie die folgende Formel auf und füllen Sie sie aus

Bitte kopieren oder geben Sie die folgende Formel in eine leere Zelle ein, in der Sie das Ergebnis ausgeben möchten, und drücken Sie Enter Taste, 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 gesamten Zeilendaten zurückgegeben werden. Siehe Screenshot:

3.5 Verschachtelter VLOOKUP in Excel

Manchmal müssen Sie möglicherweise nach Werten suchen, die über mehrere Tabellen hinweg miteinander verknüpft sind. In diesem Fall können Sie mehrere SVERWEIS-Funktionen verschachteln, um den endgültigen Wert zu erhalten.

Ich habe beispielsweise ein Arbeitsblatt, das zwei separate Tabellen enthält. Die erste Tabelle listet alle Produktnamen zusammen mit dem entsprechenden Verkäufer auf. In der zweiten Tabelle sind die Gesamtverkäufe jedes Verkäufers aufgeführt. Wenn Sie nun die Verkäufe jedes Produkts ermitteln möchten, wie im folgenden Screenshot gezeigt, können Sie die SVERWEIS-Funktion verschachteln, um diese Aufgabe zu erfüllen.

Die generische Formel für die verschachtelte VLOOKUP-Funktion lautet:

Schritt 1: Tragen Sie die folgende Formel auf und füllen Sie sie aus

Bitte wenden Sie die folgende Formel in eine leere Zelle an und ziehen Sie dann den Füllpunkt nach unten zu den 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 dargestellt:

3.6 Überprüfen Sie anhand von Listendaten in einer anderen Spalte, ob ein Wert vorhanden ist

Mit der VLOOKUP-Funktion können Sie auch anhand der Datenliste in einer anderen Spalte überprüfen, ob Werte vorhanden sind. Wenn Sie beispielsweise nach den Namen in Spalte C suchen und einfach „Ja“ oder „Nein“ zurückgeben möchten, wenn der Name in Spalte A gefunden wird oder nicht, wie im folgenden Screenshot gezeigt.

Schritt 1: Tragen Sie die folgende Formel auf und füllen Sie sie aus

Bitte wenden Sie die folgende Formel in eine leere Zelle an und ziehen Sie dann den Füllpunkt nach unten zu den Zellen, die Sie mit dieser Formel füllen möchten.

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

Ergebnis:

Und Sie erhalten das gewünschte Ergebnis, siehe Screenshot:

3.7 SVERWEIS und Summierung aller übereinstimmenden Werte in Zeilen oder Spalten

Wenn Sie mit numerischen Daten arbeiten, 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 bei der Bewältigung dieser Aufgabe helfen können.

 3.7.1 SVERWEIS und Summierung 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 gezeigt. Jetzt müssen Sie alle Bestellungen in allen Monaten basierend auf den angegebenen Produkten summieren.

Schritt 1: Tragen Sie die folgende Formel auf und füllen Sie sie aus

Bitte kopieren Sie die folgende Formel oder geben Sie sie in eine leere Zelle ein und drücken Sie dann Strg + Umschalt + Enter Tasten zusammen, um das erste Ergebnis zu erhalten. Ziehen Sie dann den Füllpunkt 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 Werts wurden summiert, siehe Screenshot:

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

Wenn Sie den Gesamtwert für die einzelnen Monate summieren möchten, wie im Screenshot unten gezeigt. Die normale VLOOKUP-Funktion hilft Ihnen möglicherweise nicht weiter. Hier sollten Sie die Funktionen SUM, INDEX und MATCH zusammen anwenden, um eine Formel zu erstellen.

Schritt 1: Wenden Sie die folgende Formel an

Wenden Sie die folgende Formel in eine leere Zelle an und ziehen Sie dann den Füllpunkt 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:

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

 3.7.3 SVERWEIS und Summierung der ersten oder aller übereinstimmenden Werte mit einer leistungsstarken Funktion

Möglicherweise fällt es Ihnen schwer, sich die oben genannten Formeln zu merken. In diesem Fall empfehle ich eine leistungsstarke Funktion: Nachschlagen und Summe of Kutools for ExcelMit dieser Funktion können Sie die ersten übereinstimmenden oder alle übereinstimmenden Werte in Zeilen oder Spalten so einfach wie möglich nachschlagen und summieren.

1. Klicken Sie Kutoolen > Super NACHSCHLAG > LOOKUP und Summe , um diese Funktion zu aktivieren.
2. Geben Sie dann die Vorgänge im Dialogfeld entsprechend Ihren Anforderungen an.

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

Wenn Sie die Werte summieren möchten, wenn Sie beispielsweise sowohl mit der Spalte als auch mit der Zeile übereinstimmen müssen, um den Gesamtwert des Produkts Sweater im Monat März zu erhalten, wie im folgenden Screenshot gezeigt.

Hier können Sie die Funktion SUMPRODCT verwenden, um diese Aufgabe auszuführen.

Bitte wenden Sie die folgende Formel in eine Zelle an und drücken Sie dann Enter Schlüssel, um das Ergebnis zu erhalten, siehe Screenshot:

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

3.8 VLOOKUP zum Zusammenführen zweier Tabellen basierend auf Schlüsselspalten

Bei Ihrer täglichen Arbeit müssen Sie beim Analysieren von Daten möglicherweise alle erforderlichen Informationen auf der Grundlage einer oder mehrerer Schlüsselspalten in einer einzigen Tabelle zusammenfassen. Um diese Aufgabe zu erfüllen, können Sie anstelle der VLOOKUP-Funktion die Funktionen INDEX und MATCH verwenden.

 3.8.1 VLOOKUP zum Zusammenführen zweier Tabellen basierend auf einer Schlüsselspalte

Sie haben beispielsweise zwei Tabellen, die erste Tabelle enthält die Produkt- und Namensdaten und die zweite Tabelle enthält die Produkt- und Bestelldaten. Jetzt möchten Sie diese beiden Tabellen kombinieren, indem Sie die gemeinsame Produktspalte in einer Tabelle zusammenführen.

Schritt 1: Tragen Sie die folgende Formel auf und füllen Sie sie aus

Bitte wenden Sie die folgende Formel in eine leere Zelle an. Ziehen Sie dann den Füllpunkt nach unten zu den Zellen, auf die Sie diese Formel anwenden möchten

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

Ergebnis:

Jetzt erhalten Sie eine zusammengeführte Tabelle, in der die Bestellspalte basierend auf den Schlüsselspaltendaten mit der ersten Tabelle verbunden wird.

 3.8.2 VLOOKUP zum Zusammenführen zweier Tabellen basierend auf mehreren Schlüsselspalten

Wenn die beiden Tabellen, die Sie verbinden möchten, mehrere Schlüsselspalten haben, führen Sie zum Zusammenführen der Tabellen basierend auf diesen gemeinsamen Spalten bitte die folgenden Schritte aus.

Die generische Formel lautet:

Schritt 1: Wenden Sie die folgende Formel an

Bitte wenden Sie die folgende Formel in eine leere Zelle an, in die Sie das Ergebnis einfügen möchten, und drücken Sie dann Strg + Umschalt + Enter Tasten zusammen, 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 aus

Wählen Sie dann die erste Formelzelle aus und ziehen Sie den Füllpunkt, um diese Formel nach Bedarf in andere Zellen zu kopieren:

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

Mussten Sie jemals einen SVERWEIS über mehrere Arbeitsblätter in Excel durchführen? Wenn Sie beispielsweise über drei Arbeitsblätter mit Datenbereichen verfügen und bestimmte Werte basierend auf Kriterien aus diesen Blättern abrufen möchten, können Sie der Schritt-für-Schritt-Anleitung folgen SVERWEIS-Werte über mehrere Arbeitsblätter hinweg um diese Aufgabe zu erfüllen.

VLOOKUP-übereinstimmende Werte behalten die Zellenformatierung bei

Bei der Suche nach übereinstimmenden Werten wird die ursprüngliche Zellenformatierung wie Schriftfarbe, Hintergrundfarbe, Datenformat usw. nicht beibehalten. Um die Zellen- oder Datenformatierung beizubehalten, werden in diesem Abschnitt einige Tricks zum Lösen der Aufgaben vorgestellt.

4.1 VLOOKUP-Übereinstimmungswert und Beibehaltung der Zellenfarbe und Schriftartformatierung

Wie wir alle wissen, kann die normale SVERWEIS-Funktion nur den passenden Wert aus einem anderen Datenbereich abrufen. Es kann jedoch Fälle geben, in denen Sie den entsprechenden Wert zusammen mit der Zellformatierung erhalten möchten, z. B. Füllfarbe, Schriftfarbe und Schriftstil. In diesem Abschnitt besprechen wir, wie man übereinstimmende Werte abruft und dabei die Quellformatierung in Excel beibehält.

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

Schritt 1: Kopieren Sie den Code 1 in das Sheet Code Module

1. Wenn das Arbeitsblatt die Daten enthält, die Sie SVERWEISEN möchten, klicken Sie mit der rechten Maustaste auf die Blattregisterkarte und wählen Sie sie aus Code anzeigen aus dem Kontextmenü. Siehe Screenshot:
   
2. In der geöffneten Microsoft Visual Basic für Applikationen Fenster, bitte kopieren Sie den folgenden VBA-Code in das Code-Fenster.
3. VBA-Code 1: VLOOKUP zum Abrufen 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 den Code 2 in das Modulfenster

1. Noch in der Microsoft Visual Basic für Applikationen Klicken Sie im Fenster Insert > ModulKopieren Sie anschließend den folgenden VBA-Code 2 in das Modulfenster.
2. VBA-Code 2: VLOOKUP zum Abrufen 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 VBAproject

1. Nachdem Sie die oben genannten Codes eingegeben haben, klicken Sie auf Tools > Bibliographie der Microsoft Visual Basic für Applikationen Fenster. Dann überprüfen Sie die Microsoft Scripting-Laufzeit Kontrollkästchen in der Referenzen - VBAProject Dialogbox. Siehe Screenshots:
2. Dann klick OK , um das Dialogfeld zu schließen, und speichern und schließen Sie dann das Codefenster.

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

1. Gehen Sie nun zurück zum Arbeitsblatt und wenden Sie die folgende Formel an. Ziehen Sie dann den Füllpunkt nach unten, um alle Ergebnisse zusammen mit ihrer Formatierung anzuzeigen. Siehe Screenshot:
   

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

   
   

4.2 Behalten Sie das Datumsformat eines von VLOOKUP zurückgegebenen Werts bei

Wenn Sie die SVERWEIS-Funktion verwenden, um einen Wert im 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 in die TEXT-Funktion einschließen.

Schritt 1: Tragen Sie die folgende Formel auf und füllen Sie sie aus

Bitte wenden Sie die folgende Formel in eine leere Zelle an. Ziehen Sie dann den Füllpunkt, 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 zurückgegeben, wie im folgenden Screenshot gezeigt:

4.3 Zellenkommentar von VLOOKUP zurückgeben

Mussten Sie jemals sowohl die übereinstimmenden Zelldaten als auch den zugehörigen Kommentar mithilfe von SVERWEIS in Excel abrufen, wie im folgenden Screenshot gezeigt? Wenn ja, kann Ihnen die unten bereitgestellte benutzerdefinierte Funktion bei der Bewältigung dieser Aufgabe helfen.

Schritt 1: Kopieren Sie den Code in ein Modul

1. Halten Sie die ALT + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.
2. Klicken Sie Insert > ModulKopieren Sie dann den folgenden Code und fügen Sie ihn in das Modulfenster ein.
   VBA-Code: Vlookup und Rückgabe des übereinstimmenden Werts mit Zellkommentar:

   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 Sie das Codefenster.

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

1. Geben Sie nun die folgende Formel ein und ziehen Sie den Füllpunkt, um diese Formel in andere Zellen zu kopieren. Es werden sowohl die übereinstimmenden Werte als auch die Kommentare gleichzeitig zurückgegeben, siehe Screenshot:
   

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

   
   

4.4 VLOOKUP-Nummern als Text gespeichert

Ich habe beispielsweise einen Datenbereich, in dem die ID-Nummer in der Originaltabelle im Zahlenformat vorliegt und die ID-Nummer in den Nachschlagezellen als Text gespeichert ist. Bei Verwendung der normalen VLOOKUP-Funktion kann es zu einem #N/A-Fehler kommen. Um in diesem Fall die richtigen Informationen abzurufen, können Sie die Funktionen TEXT und VALUE in die VLOOKUP-Funktion einschließen. Nachfolgend finden Sie die Formel, um dies zu erreichen:

Schritt 1: Tragen Sie die folgende Formel auf und füllen Sie sie aus

Bitte wenden Sie die folgende Formel in eine leere Zelle an und ziehen Sie dann den Füllpunkt 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:

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