Note: The other languages of the website are Google-translated. Back to English
Einloggen  \/ 
x
or
x
Registrieren  \/ 
x

or

VLOOKUP-Funktion mit einigen grundlegenden und erweiterten Beispielen in Excel

In Excel ist die VLOOKUP-Funktion für die meisten Excel-Benutzer eine leistungsstarke Funktion, mit der nach einem Wert ganz links im Datenbereich gesucht und ein übereinstimmender Wert in derselben Zeile aus einer Spalte zurückgegeben wird, die Sie wie im folgenden Screenshot angegeben angegeben haben . In diesem Tutorial wird die Verwendung der VLOOKUP-Funktion anhand einiger grundlegender und erweiterter Beispiele in Excel erläutert.

Inhaltsverzeichnis:

1. Einführung der VLOOKUP-Funktion - Syntax und Argumente

2. Grundlegende VLOOKUP-Beispiele

3. Erweiterte VLOOKUP-Beispiele

4. VLOOKUP-übereinstimmende Werte behalten die Zellenformatierung bei

5. Laden Sie die VLOOKUP-Beispieldateien herunter


Einführung der VLOOKUP-Funktion - Syntax und Argumente

Die Syntax der VLOOKUP-Funktion:

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

Argumente:

Lookup-Wert: Der Wert, den Sie suchen möchten. Es muss sich in der ersten Spalte des Bereichs table_array befinden.

Table_array: Der Datenbereich oder die Tabelle, in der sich die Suchwertspalte und die Ergebniswertspalte befinden.

Spalte_index_num: Die Anzahl der Spalten, aus denen der übereinstimmende Wert zurückgegeben wird. Es beginnt mit 1 aus der Spalte ganz links im Tabellenarray.

Range_lookup: Ein logischer Wert, der bestimmt, ob diese VLOOKUP-Funktion eine genaue oder eine ungefähre Übereinstimmung zurückgibt.

  • Ungefähre Übereinstimmung - 1 / WAHR: Wenn keine genaue Übereinstimmung gefunden wird, sucht die Formel nach der nächsten Übereinstimmung - dem größten Wert, der kleiner als der Suchwert ist. In diesem Fall sollten Sie die Nachschlagespalte in aufsteigender Reihenfolge sortieren.
    = VLOOKUP (lookup_value, table_array, col_index, TRUE)
    = VLOOKUP (lookup_value, table_array, col_index, 1)
  • Exakte Übereinstimmung - 0 / FALSCH: Hiermit wird nach einem Wert gesucht, der genau dem Suchwert entspricht. Wird keine genaue Übereinstimmung gefunden, wird der Fehlerwert # N / A zurückgegeben.
    = VLOOKUP (lookup_value, table_array, col_index, FALSE)
    = VLOOKUP (lookup_value, table_array, col_index, 0)

Hinweise:

  • 1. Die Vlookup-Funktion sucht nur von links nach rechts nach Werten.
  • 2. Wenn basierend auf dem Suchwert mehrere Übereinstimmungswerte vorhanden sind, wird mithilfe der Vlookup-Funktion nur der erste Übereinstimmungswert zurückgegeben.
  • 3. Es wird der Fehlerwert # N / A zurückgegeben, wenn der Suchwert nicht gefunden werden kann.

Grundlegende VLOOKUP-Beispiele

1. Führen Sie eine genaue Übereinstimmung mit Vlookup und eine ungefähre Übereinstimmung mit Vlookup durch

Führen Sie eine exakte Übereinstimmung mit Vlookup in Excel durch

Wenn Sie nach einer genauen Übereinstimmung mit der Vlookup-Funktion suchen, müssen Sie normalerweise nur FALSE im letzten Argument verwenden.

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

1. Wenden Sie die folgende Formel in eine leere Zelle an, in der Sie das Ergebnis erhalten möchten:

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

2. Ziehen Sie dann den Füllpunkt nach unten zu den Zellen, die Sie mit dieser Formel füllen möchten, und Sie erhalten die gewünschten Ergebnisse. Siehe Screenshot:

Hinweise:

  • 1. In der obigen Formel F2 ist der Wert, für den Sie den entsprechenden Wert zurückgeben möchten. A2: D7 ist das Tabellenarray, die Nummer 3 ist die Spaltennummer, von der Ihr übereinstimmender Wert zurückgegeben wird, und die FALSCH bezieht sich auf die genaue Übereinstimmung.
  • 2. Wenn Ihr Kriterienwert nicht im Datenbereich gefunden wird, wird ein Fehlerwert # N / A angezeigt.

Führen Sie eine ungefähre Übereinstimmung mit Vlookup in Excel durch

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

Wenn Sie beispielsweise die folgenden Bereichsdaten haben, befinden sich die angegebenen Bestellungen nicht in der Spalte Bestellungen. Wie erhalten Sie den nächstgelegenen Rabatt in Spalte B?

1. Geben Sie die folgende Formel in eine Zelle ein, in die Sie das Ergebnis einfügen möchten:

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

2. Ziehen Sie dann den Füllpunkt nach unten auf die Zellen, um diese Formel anzuwenden. Sie erhalten die ungefähren Übereinstimmungen basierend auf den angegebenen Werten, siehe Screenshot:

Hinweise:

  • 1. In der obigen Formel D2 ist der Wert, für den Sie die relativen Informationen zurückgeben möchten. A2: B9 ist der Datenbereich, die Nummer 2 gibt die Spaltennummer an, an die Ihr übereinstimmender Wert zurückgegeben wird, und die TRUE bezieht sich auf die ungefähre Übereinstimmung.
  • 2. Die ungefähre Übereinstimmung gibt den größten Wert zurück, der kleiner als Ihr spezifischer Suchwert ist.
  • 3. Um mit der Vlookup-Funktion einen ungefähren Übereinstimmungswert zu erhalten, müssen Sie die Spalte ganz links im Datenbereich in aufsteigender Reihenfolge sortieren. Andernfalls wird ein falsches Ergebnis zurückgegeben.

2. Führen Sie in Excel ein Vlookup mit Groß- und Kleinschreibung durch

Standardmäßig führt die Vlookup-Funktion eine Suche ohne Berücksichtigung der Groß- und Kleinschreibung durch. Dies bedeutet, dass Klein- und Großbuchstaben als identisch behandelt werden. Manchmal müssen Sie in Excel nach Groß- und Kleinschreibung suchen. Die Funktionen Index, Match and Exact oder Lookup and Exact können Ihnen einen Gefallen tun.

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.

Formel 1: Verwenden der Funktionen EXACT, INDEX, MATCH

1. Bitte geben Sie die folgende Array-Formel ein oder kopieren Sie sie in eine leere Zelle, in der Sie das Ergebnis erhalten möchten:

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

2. Dann drücken Strg + Umschalt + Enter Drücken Sie gleichzeitig die Tasten, um das erste Ergebnis zu erhalten, und wählen Sie dann die Formelzelle aus. Ziehen Sie den Füllpunkt nach unten zu den Zellen, die Sie mit dieser Formel füllen möchten. Dann erhalten Sie die richtigen Ergebnisse, die Sie benötigen. Siehe Screenshot:

Hinweise:

  • 1. In der obigen Formel A2: A10 ist die Spalte, die die spezifischen Werte enthält, in denen Sie nachschlagen möchten. F2 ist der Suchwert, C2: C10 ist die Spalte, aus der das Ergebnis zurückgegeben wird.
  • 2. Wenn mehrere Übereinstimmungen gefunden wurden, gibt diese Formel immer die erste Übereinstimmung zurück.

Formel 2: Verwenden von Lookup- und Exact-Funktionen

1. Bitte wenden Sie die folgende Formel in eine leere Zelle an, in der Sie das Ergebnis erhalten möchten:

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

2. Ziehen Sie dann den Füllpunkt nach unten zu den Zellen, in die Sie diese Formel kopieren möchten, und Sie erhalten die übereinstimmenden Werte mit Groß- und Kleinschreibung, wie im folgenden Screenshot gezeigt:

Hinweise:

  • 1. In der obigen Formel A2: A10 ist die Spalte, die die spezifischen Werte enthält, in denen Sie nachschlagen möchten. F2 ist der Suchwert, C2: C10 ist die Spalte, aus der das Ergebnis zurückgegeben wird.
  • 2. Wenn mehrere Übereinstimmungen gefunden wurden, gibt diese Formel immer die letzte Übereinstimmung zurück.

3. Vlookup-Werte von rechts nach links in Excel

Die Vlookup-Funktion sucht immer nach einem Wert in der linken Spalte eines Datenbereichs und gibt den entsprechenden Wert aus einer Spalte rechts zurück. Wenn Sie eine umgekehrte Vlookup durchführen möchten, dh einen bestimmten Wert rechts suchen und den entsprechenden Wert in der linken Spalte zurückgeben, wie im folgenden Screenshot gezeigt:

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


4. Suchen Sie den zweiten, n-ten oder letzten übereinstimmenden Wert 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 der zweite, n-te oder letzte übereinstimmende Wert mit der Vlookup-Funktion ermittelt wird.

Suchen Sie nach und geben Sie den zweiten oder n-ten übereinstimmenden Wert zurück

Angenommen, Sie haben eine Liste mit Namen in Spalte A, den in Spalte B gekauften Schulungskurs, und suchen nun nach dem zweiten oder n-ten Schulungskurs, den der jeweilige Kunde gekauft hat. Siehe Screenshot:

1. Um den zweiten oder n-ten Übereinstimmungswert basierend auf den angegebenen Kriterien zu erhalten, wenden Sie bitte die folgende Arrayformel in eine leere Zelle an:

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

2. Dann drücken Strg + Umschalt + Enter Geben Sie die Tasten zusammen, um das erste Ergebnis zu erhalten, und wählen Sie dann die Formelzelle aus. Ziehen Sie den Füllpunkt nach unten zu den Zellen, in die Sie diese Formel füllen möchten. Alle zweiten übereinstimmenden Werte, die auf den angegebenen Namen basieren, wurden sofort angezeigt (siehe Abbildung):

Hinweis:

  • In dieser Formel A2: A14 ist der Bereich mit allen Werten für die Suche, B2: B14 ist der Bereich der übereinstimmenden Werte, aus denen Sie zurückkehren möchten. E2 ist der Suchwert und die letzte Zahl 2 Gibt den zweiten übereinstimmenden Wert an, den Sie erhalten möchten. Wenn Sie den dritten übereinstimmenden Wert zurückgeben möchten, müssen Sie ihn nur nach Bedarf auf 3 ändern.

Suchen Sie nach und geben Sie den letzten übereinstimmenden Wert zurück

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


5. Vlookup-Übereinstimmungswerte zwischen zwei angegebenen Werten oder Daten

Manchmal möchten Sie möglicherweise Werte zwischen zwei Werten oder Datumsangaben suchen und die entsprechenden Ergebnisse wie im folgenden Screenshot zurückgeben. In diesem Fall können Sie die Funktion LOOKUP und eine sortierte Tabelle verwenden.

Vlookup-Übereinstimmungswerte zwischen zwei angegebenen Werten oder Daten mit Formel

1. Zunächst sollte Ihre ursprüngliche Tabelle ein sortierter Datenbereich sein. Kopieren Sie anschließend die folgende Formel oder geben Sie sie in eine leere Zelle ein:

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

2. Ziehen Sie dann den Füllpunkt, um diese Formel in andere Zellen zu füllen, die Sie benötigen. Jetzt erhalten Sie alle übereinstimmenden Datensätze basierend auf dem angegebenen Wert (siehe Abbildung):

Hinweise:

  • 1. In der obigen Formel A2: A6 ist der Bereich kleinerer Werte und B2: B6 ist der Bereich größerer Zahlen in Ihrem Datenbereich, der E2 ist der angegebene Wert, für den Sie den entsprechenden Wert erhalten möchten. C2: C6 sind die Spaltendaten, aus denen Sie extrahieren möchten.
  • 2. Diese Formel kann auch zum Extrahieren übereinstimmender Werte zwischen zwei Daten verwendet werden (siehe Abbildung unten):

Vlookup-Übereinstimmungswerte zwischen zwei angegebenen Werten oder Daten mit einer nützlichen Funktion

Wenn Sie mit der obigen Formel Schmerzen haben, werde ich hier ein einfaches Werkzeug vorstellen - Kutools for ExcelMit seinen LOOKUP zwischen zwei Werten Mit dieser Funktion können Sie das entsprechende Element basierend auf dem spezifischen Wert oder Datum zwischen zwei Werten oder Datumsangaben zurückgeben, ohne sich an eine Formel zu erinnern.   Klicken Sie hier, um Kutools für Excel jetzt herunterzuladen!


6. Verwenden von Platzhaltern für Teilübereinstimmungen in der Vlookup-Funktion

In Excel können die Platzhalter in der Vlookup-Funktion verwendet werden, mit der eine teilweise Übereinstimmung mit einem Suchwert durchgeführt wird. Sie können beispielsweise Vlookup verwenden, um einen übereinstimmenden Wert 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?

1. Die normale Vlookup-Funktion funktioniert nicht richtig. Sie müssen den Text oder die Zellreferenz mit einem Platzhalter verknüpfen. Kopieren Sie die folgende Formel oder geben Sie sie in eine leere Zelle ein:

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

2. Ziehen Sie dann den Füllpunkt, um diese Formel in andere Zellen zu füllen, die Sie benötigen. Alle übereinstimmenden Ergebnisse wurden wie im folgenden Screenshot gezeigt zurückgegeben:

Hinweise:

  • 1. In der obigen Formel E2 & ”*” ist der Suchwert, der Wert in E2 und der * Platzhalter ("*" kennzeichnet ein oder mehrere Zeichen), A2: C11 ist der Suchbereich, die Nummer 3 Die Spalte, die den zurückzugebenden Wert enthält.
  • 2. Vlookup Wenn Sie Platzhalter verwenden, müssen Sie den genauen Übereinstimmungsmodus mit FALSE oder 0 für das letzte Argument in der Vlookup-Funktion festlegen.

Tipps:

1. Suchen Sie die übereinstimmenden Werte und geben Sie sie zurück, die mit einem bestimmten Wert enden. Wenden Sie diese Formel an: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Um den übereinstimmenden Wert basierend auf einem Teil der Textzeichenfolge zu suchen und zurückzugeben, unabhängig davon, ob sich der angegebene Text vor, hinter oder in der Mitte der Textzeichenfolge befindet, müssen Sie nur zwei * Zeichen um die Zellreferenz oder den Text verbinden. Bitte machen Sie mit dieser Formel: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Vlookup-Werte aus einem anderen Arbeitsblatt

Normalerweise müssen Sie möglicherweise mit mehr als einem Arbeitsblatt arbeiten. Mit der Vlookup-Funktion können Sie Daten von einem anderen Blatt wie auf einem Arbeitsblatt nachschlagen.

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:

1. Bitte geben Sie die folgende Formel ein oder kopieren Sie sie in eine leere Zelle, in der Sie die übereinstimmenden Elemente erhalten möchten:

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

2. Ziehen Sie dann den Füllpunkt nach unten auf die Zellen, auf die Sie diese Formel anwenden möchten, und Sie erhalten die entsprechenden Ergebnisse nach Bedarf, siehe Screenshot:

Hinweis: In der obigen Formel:

  • A2 stellt den Suchwert dar;
  • Datenblatt ist der Name des Arbeitsblatts, von dem aus Sie nach Daten suchen möchten. (Wenn der Blattname Leerzeichen oder Satzzeichen enthält, sollten Sie den Blattnamen in einfache Anführungszeichen setzen. Andernfalls können Sie den Blattnamen wie = VLOOKUP (A2, direkt verwenden). Datenblatt! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 ist der Datenbereich im Datenblatt, nach dem wir suchen;
  • die Anzahl 3 ist die Spaltennummer, die übereinstimmende Daten enthält, von denen Sie zurückkehren möchten.

8. Vlookup-Werte aus einer anderen Arbeitsmappe

In diesem Abschnitt wird die Suche erläutert und die übereinstimmenden Werte aus einer anderen Arbeitsmappe mithilfe der Vlookup-Funktion zurückgegeben.

Beispielsweise enthält die erste Arbeitsmappe die Produkt- und Kostenlisten. Jetzt möchten Sie die entsprechenden Kosten in der zweiten Arbeitsmappe basierend auf dem Produktelement extrahieren (siehe Abbildung unten).

1. Um die relativen Kosten aus einer anderen Arbeitsmappe abzurufen, öffnen Sie zunächst beide Arbeitsmappen, die Sie verwenden möchten, und wenden Sie dann die folgende Formel in eine Zelle an, in die Sie das Ergebnis einfügen möchten:

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

2. Ziehen Sie diese Formel dann in andere Zellen, die Sie benötigen, und kopieren Sie sie (siehe Abbildung):

Hinweise:

  • 1. In der obigen Formel:
    B2 stellt den Suchwert dar;
    [Produktliste.xlsx] Blatt1 ist der Name der Arbeitsmappe und des Arbeitsblatts, aus dem Sie Daten abrufen möchten (Der Verweis auf die Arbeitsmappe ist in eckigen Klammern und die gesamte Arbeitsmappe + das Arbeitsblatt in einfache Anführungszeichen eingeschlossen).
    A2: B6 ist der Datenbereich im Arbeitsblatt einer anderen Arbeitsmappe, nach der wir Daten suchen;
    die Anzahl 2 ist die Spaltennummer, die übereinstimmende Daten enthält, von denen Sie zurückkehren möchten.
  • 2. Wenn die Sucharbeitsmappe geschlossen ist, wird der vollständige Dateipfad für die Sucharbeitsmappe in der Formel wie folgt angezeigt:

9. Suchen Sie nach und geben Sie leeren oder bestimmten Text anstelle des Fehlerwerts 0 oder # N / A zurück

Wenn Sie die vlookup-Funktion anwenden, um den entsprechenden Wert zurückzugeben, wird normalerweise, wenn Ihre übereinstimmende Zelle leer ist, 0 zurückgegeben. Wenn Ihr übereinstimmender Wert nicht gefunden wird, wird der Fehlerwert # N / A angezeigt (siehe Abbildung unten). Anstatt den Wert 0 oder # N / A mit einer leeren Zelle oder einem anderen gewünschten Wert anzuzeigen, wird dies angezeigt Vlookup, um anstelle von 0 oder N / A einen leeren oder bestimmten Wert zurückzugeben Tutorial kann Ihnen Schritt für Schritt einen Gefallen tun.


Erweiterte VLOOKUP-Beispiele

1. Zweiwege-Suche mit Vlookup-Funktion (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 suchen. Angenommen, Sie haben den folgenden Datenbereich und müssen jetzt möglicherweise den Wert für ein bestimmtes Produkt in einem bestimmten Quartal ermitteln. In diesem Abschnitt wird eine Formel für die Bearbeitung dieses Jobs in Excel vorgestellt.

Formel 1: Verwenden der Funktionen VLOOKUP und MATCH

In Excel können Sie eine Kombination aus VLOOKUP- und MATCH-Funktionen verwenden, um eine bidirektionale Suche durchzuführen. Wenden Sie die folgende Formel in eine leere Zelle an und drücken Sie dann Weiter Schlüssel, um das Ergebnis zu erhalten.

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Hinweis: In der obigen Formel:

  • H1: der Suchwert in der Spalte, auf der der entsprechende Wert abgerufen werden soll;
  • A2: E6: der Datenbereich einschließlich der Zeilenüberschriften;
  • H2: der Suchwert in der Zeile, auf der der entsprechende Wert abgerufen werden soll;
  • A1: E1: die Zellen der Spaltenüberschriften.

Formel 2: Verwenden von INDEX- und MATCH-Funktionen

Hier ist eine andere Formel, die Ihnen auch bei der Durchführung einer zweidimensionalen Suche helfen kann. Wenden Sie die folgende Formel an und drücken Sie dann Weiter Schlüssel, um das gewünschte Ergebnis zu erhalten.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Hinweis: In der obigen Formel:

  • B2: E6: der Datenbereich, aus dem das übereinstimmende Element zurückgegeben werden soll;
  • H1: der Suchwert in der Spalte, auf der der entsprechende Wert abgerufen werden soll;
  • A2: A6: Die Zeilenüberschriften enthalten das Produkt, nach dem Sie suchen möchten.
  • H2: der Suchwert in der Zeile, auf der der entsprechende Wert abgerufen werden soll;
  • B1: E1: Die Spaltenüberschriften enthalten das Viertel, nach dem Sie suchen möchten.

2. Vlookup-Übereinstimmungswert basierend auf zwei oder mehr Kriterien

Es ist für Sie einfach, den übereinstimmenden Wert anhand eines Kriteriums nachzuschlagen. Was können Sie jedoch tun, wenn Sie zwei oder mehr Kriterien haben? Mit den Funktionen LOOKUP oder MATCH und INDEX in Excel können Sie diesen Job schnell und einfach 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.

Formel 1: Verwenden der LOOKUP-Funktion

Wenden Sie die folgende Formel auf eine Zelle an, in der Sie das Ergebnis erhalten möchten, und drücken Sie die Eingabetaste (siehe Abbildung):

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

Hinweise:

  • 1. In der obigen Formel:
    A2: A12 = G1: bedeutet, die Kriterien von G1 im Bereich A2 zu suchen: A12;
    B2: B12 = G2: bedeutet, die Kriterien von G2 im Bereich B2 zu suchen: B12;
    D2: D12: Der Bereich, in dem Sie den entsprechenden Wert zurückgeben möchten.
  • 2. Wenn Sie mehr als zwei Kriterien haben, müssen Sie nur die anderen Kriterien in die Formel einfügen, z. =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Formel 2: Verwenden der Funktionen INDEXT und MATCH

Die Kombination aus Index- und Übereinstimmungsfunktion kann auch verwendet werden, um den übereinstimmenden Wert basierend auf mehreren Kriterien zurückzugeben. Bitte kopieren Sie die folgende Formel oder geben Sie sie ein:

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

Drücken Sie dann gleichzeitig Strg + Umschalt + Eingabetaste, um den gewünschten relativen Wert zu erhalten. Siehe Screenshot:

Hinweise:

  • 1. In der obigen Formel:
    A2: A12 = G1: bedeutet, die Kriterien von G1 im Bereich A2 zu suchen: A12;
    B2: B12 = G2: bedeutet, die Kriterien von G2 im Bereich B2 zu suchen: B12;
    D2: D12: Der Bereich, in dem Sie den entsprechenden Wert zurückgeben möchten.
  • 2. Wenn Sie mehr als zwei Kriterien haben, müssen Sie nur die neuen Kriterien in die Formel einfügen, z. =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup, um mehrere übereinstimmende Werte mit einer oder mehreren Bedingungen zurückzugeben

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

Zeigen Sie alle übereinstimmenden Werte basierend auf einer oder mehreren Bedingungen horizontal an

Alle übereinstimmenden Werte basierend auf einer Bedingung horizontal anzeigen:

Um alle übereinstimmenden Werte basierend auf einem bestimmten Wert horizontal anzuzeigen und zurückzugeben, lautet die generische Formel:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
Hinweis: m ist die Zeilennummer der ersten Zelle im Rückgabebereich minus 1.
      n ist die Spaltennummer der ersten Formelzelle minus 1.

1. Bitte wenden Sie die folgende Formel in eine leere Zelle an und drücken Sie dann Strg + Umschalt + Enter Tasten zusammen, um den ersten übereinstimmenden Wert zu erhalten, siehe Screenshot:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Wählen Sie dann die erste Formelzelle aus und ziehen Sie den Füllpunkt in die rechten Zellen, bis eine leere Zelle angezeigt wird und alle entsprechenden Elemente extrahiert wurden (siehe Abbildung):

Tipps:

Wenn die zurückgegebene Liste doppelte übereinstimmende Werte enthält, verwenden Sie diese Formeln, um die doppelten Werte zu ignorieren, und drücken Sie dann Weiter um das erste Ergebnis zu erhalten: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Geben Sie diese Formel weiter ein: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") in eine Zelle neben dem ersten Ergebnis und drücken Sie dann Strg + Umschalt + Enter Drücken Sie die Tasten zusammen, um das zweite Ergebnis zu erhalten. Ziehen Sie diese Formel dann in die rechten Zellen, um alle anderen übereinstimmenden Werte zu erhalten, bis leere Zellen angezeigt werden (siehe Abbildung):


Alle übereinstimmenden Werte basierend auf zwei oder mehr Bedingungen horizontal anzeigen:

Um alle übereinstimmenden Werte basierend auf spezifischeren Werten horizontal anzuzeigen und zurückzugeben, lautet die generische Formel:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
Hinweis: m ist die Zeilennummer der ersten Zelle im Rückgabebereich minus 1.
      n ist die Spaltennummer der ersten Formelzelle minus 1.

1. Wenden Sie die folgende Formel in eine leere Zelle an, in der Sie das Ergebnis ausgeben möchten:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Wählen Sie dann die Formelzelle aus und ziehen Sie den Füllpunkt in die rechten Zellen, bis eine leere Zelle angezeigt wird und alle übereinstimmenden Werte basierend auf den spezifischen Kriterien zurückgegeben wurden (siehe Abbildung):

Hinweis: Für weitere Kriterien müssen Sie nur lookup_value und lookup_range in die Formel einfügen, z. =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


Überprüfen Sie alle übereinstimmenden Werte basierend auf einer oder mehreren Bedingungen vertikal

Alle übereinstimmenden Werte basierend auf einer Bedingung vertikal anzeigen:

Um alle übereinstimmenden Werte basierend auf einem bestimmten Wert vertikal anzuzeigen und zurückzugeben, lautet die generische Formel:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
Hinweis: m ist die Zeilennummer der ersten Zelle im Rückgabebereich minus 1.
      n ist die Zeilennummer der ersten Formelzelle minus 1.

1. Kopieren Sie die folgende Formel oder geben Sie sie in eine Zelle ein, in der Sie das Ergebnis erhalten möchten, und drücken Sie dann Strg + Umschalt + Enter Tasten zusammen, um den ersten übereinstimmenden Wert zu erhalten, siehe Screenshot:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Wählen Sie dann die erste Formelzelle aus und ziehen Sie den Füllpunkt nach unten in andere Zellen, bis eine leere Zelle angezeigt wird und alle entsprechenden Elemente in einer Spalte aufgelistet wurden (siehe Abbildung):

Tipps:

Verwenden Sie die folgenden Formeln, um die Duplikate in den zurückgegebenen übereinstimmenden Werten zu ignorieren: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Dann drücken Strg + Umschalt + Enter Geben Sie die Tasten zusammen, um den ersten übereinstimmenden Wert zu erhalten, und ziehen Sie diese Formelzelle dann auf andere Zellen, bis eine leere Zelle angezeigt wird. Sie erhalten das gewünschte Ergebnis:


Alle übereinstimmenden Werte basierend auf zwei oder mehr Bedingungen vertikal anzeigen:

Um alle übereinstimmenden Werte basierend auf spezifischeren Werten vertikal anzuzeigen und zurückzugeben, lautet die generische Formel:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
Hinweis: m ist die Zeilennummer der ersten Zelle im Rückgabebereich minus 1.
      n ist die Zeilennummer der ersten Formelzelle minus 1.

1. Kopieren Sie die folgende Formel in eine leere Zelle und drücken Sie dann Strg + Umschalt + Enter Schlüssel zusammen, um das erste übereinstimmende Element zu erhalten.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Ziehen Sie dann die Formelzelle nach unten in andere Zellen, bis eine leere Zelle angezeigt wird (siehe Screenshot:

Hinweis: Für weitere Kriterien müssen Sie nur lookup_value und lookup_range in die Formel einfügen, z. =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Suchen Sie alle übereinstimmenden Werte basierend auf zwei oder mehr Bedingungen in einer einzelnen Zelle

Wenn Sie mehrere übereinstimmende Werte in einer einzelnen Zelle mit angegebenem Trennzeichen anzeigen und zurückgeben möchten, können Sie mit der neuen Funktion von TEXTJOIN diesen Job schnell und einfach lösen.

Suchen Sie alle übereinstimmenden Werte basierend auf einer Bedingung in einer einzelnen Zelle:

Bitte wenden Sie die folgende einfache Formel in eine leere Zelle an und drücken Sie dann Strg + Umschalt + Enter Schlüssel zusammen, um das Ergebnis zu erhalten:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Tipps:

Verwenden Sie die folgenden Formeln, um die Duplikate in den zurückgegebenen übereinstimmenden Werten zu ignorieren: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Suchen Sie alle übereinstimmenden Werte basierend auf zwei oder mehr Bedingungen in einer einzelnen Zelle:

Um mit mehreren Bedingungen umzugehen, wenn alle übereinstimmenden Werte in eine einzelne Zelle zurückgegeben werden, wenden Sie bitte die folgende Formel an und drücken Sie dann Strg + Umschalt + Enter Schlüssel zusammen, um das Ergebnis zu erhalten:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Hinweise:

1. Die TEXTJOIN-Funktion ist nur in Excel 2019 und Office 365 verfügbar.

2. Wenn Sie Excel 2016 und frühere Versionen verwenden, verwenden Sie bitte die benutzerdefinierte Funktion der folgenden Artikel:


4. Vlookup, um die gesamte oder ganze Zeile einer übereinstimmenden Zelle zurückzugeben

In diesem Abschnitt werde ich erläutern, wie die gesamte Zeile eines übereinstimmenden Werts mithilfe der Vlookup-Funktion abgerufen wird.

1. Bitte kopieren Sie die folgende Formel oder geben Sie sie in eine leere Zelle ein, in der Sie das Ergebnis ausgeben möchten, und drücken Sie Weiter Schlüssel, um den ersten Wert zu erhalten.

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

2. Ziehen Sie dann die Formelzelle nach rechts, bis die Daten der gesamten Zeile angezeigt werden (siehe Screenshot):

Hinweis: In der obigen Formel F2 ist der Suchwert, auf dessen Grundlage Sie die gesamte Zeile zurückgeben möchten. A1: D12 ist der Datenbereich, den Sie verwenden möchten, A1 gibt die erste Spaltennummer in Ihrem Datenbereich an.

Tipps:

Wenn mehrere Zeilen basierend auf dem übereinstimmenden Wert gefunden wurden, wenden Sie diese Formel an, um alle entsprechenden Zeilen zurückzugeben: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), und drücken Sie dann Strg + Umschalt + Enter Drücken Sie die Tasten zusammen, um das erste Ergebnis zu erhalten, und ziehen Sie dann den Füllgriff nach rechts in die Zellen. Siehe Screenshot:

Ziehen Sie dann den Füllpunkt nach unten über die Zellen, um alle übereinstimmenden Zeilen zu erhalten (siehe Abbildung unten):


5. Führen Sie mehrere Vlookup-Funktionen (verschachteltes Vlookup) in Excel aus

Manchmal möchten Sie möglicherweise Werte in mehreren Tabellen nachschlagen. Wenn eine der Tabellen den angegebenen Suchwert enthält (siehe Abbildung unten), können Sie in diesem Fall eine oder mehrere Vlookup-Funktionen mit der IFERROR-Funktion kombinieren, um mehrere Suchvorgänge durchzuführen.

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

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Hinweis:

  • Lookup-Wert: der Wert, den Sie suchen;
  • Table1, Table2, Table3, ...: die Tabellen, in denen der Suchwert und der Rückgabewert vorhanden sind;
  • col: Die Spaltennummer in der Tabelle, aus der Sie den übereinstimmenden Wert zurückgeben möchten.
  • 0: Dies wird für eine genaue Übereinstimmung verwendet.

1. Bitte wenden Sie die folgende Formel in eine leere Zelle an, in die Sie das Ergebnis einfügen möchten:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Ziehen Sie dann den Füllpunkt nach unten auf die Zellen, auf die Sie diese Formel anwenden möchten. Alle übereinstimmenden Werte wurden wie im folgenden Screenshot gezeigt zurückgegeben:

Hinweise:

  • 1. In der obigen Formel J3 ist der Wert, den Sie suchen; A3: B7, D3: E7, G3: H7 sind die Tabellenbereiche, in denen der Suchwert und der Rückgabewert vorhanden sind; Die Nummer 2 ist die Spaltennummer im Bereich, von dem der übereinstimmende Wert zurückgegeben werden soll.
  • 2. Wenn der Suchwert nicht gefunden werden kann, wird ein Fehlerwert angezeigt. Um den Fehler durch einen lesbaren Text zu ersetzen, verwenden Sie bitte diese Formel: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup, um anhand von Listendaten in einer anderen Spalte zu überprüfen, ob ein Wert vorhanden ist

Mit der Vlookup-Funktion können Sie auch anhand einer anderen Liste überprüfen, ob Werte vorhanden sind, z. B. wenn Sie nach den Namen in Spalte C suchen und einfach Ja oder Nein zurückgeben möchten, wenn der Name in Spalte A gefunden wurde oder nicht (siehe Abbildung unten) gezeigt.

1. Bitte wenden Sie die folgende Formel in eine leere Zelle an:

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

2. Ziehen Sie dann den Füllgriff nach unten zu den Zellen, die Sie mit dieser Formel füllen möchten, und Sie erhalten das gewünschte Ergebnis, siehe Screenshot:

Hinweis: In der obigen Formel C2 ist der Suchwert, den Sie überprüfen möchten; A2: A10 ist die Liste der Bereiche, aus denen Suchwerte gefunden werden; die Nummer 1 ist die Spaltennummer, von der Sie den Wert in Ihrem Bereich abrufen möchten.


7. Suchen und summieren Sie alle übereinstimmenden Werte in Zeilen oder Spalten

Wenn Sie mit numerischen Daten arbeiten, müssen Sie beim Extrahieren der übereinstimmenden Werte aus der Tabelle manchmal auch die Zahlen in mehreren Spalten oder Zeilen summieren. In diesem Abschnitt werden einige Formeln vorgestellt, um diesen Job in Excel abzuschließen.

Suchen und summieren Sie alle übereinstimmenden Werte in einer Zeile oder mehreren Zeilen

Angenommen, Sie haben eine Produktliste mit Verkäufen für mehrere Monate (siehe Abbildung unten). Jetzt müssen Sie alle Bestellungen in allen Monaten basierend auf den angegebenen Produkten summieren.

Sehen Sie sich die ersten übereinstimmenden Werte in einer Reihe an und summieren Sie sie:

1. 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.

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

2. Ziehen Sie dann den Füllpunkt nach unten, um diese Formel in andere benötigte Zellen zu kopieren. Alle Werte in einer Zeile des ersten übereinstimmenden Werts wurden summiert (siehe Abbildung):

Hinweis: In der obigen Formel: H2 ist die Zelle, die den gesuchten Wert enthält; A2: F9 ist der Datenbereich (ohne Spaltenüberschriften), der den Suchwert und die übereinstimmenden Werte enthält; Die Nummer 2,3,4,5,6 {} sind Spaltennummern, die zur Berechnung der Gesamtsumme des Bereichs verwendet werden.


Überprüfen und summieren Sie alle übereinstimmenden Werte in mehreren Zeilen:

Die obige Formel kann nur Werte in einer Zeile für den ersten übereinstimmenden Wert summieren. Wenn Sie alle Übereinstimmungen in mehreren Zeilen zusammenfassen möchten, verwenden Sie die folgende Formel und ziehen Sie den Füllpunkt nach unten zu den Zellen, auf die Sie diese Formel anwenden möchten. Sie erhalten das gewünschte Ergebnis (siehe Abbildung):

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

Hinweis: In der obigen Formel: H2 ist der Suchwert, den Sie suchen; A2: A9 sind die Zeilenüberschriften, die den Suchwert enthalten; B2: F9 Der Datenbereich der numerischen Werte, die Sie summieren möchten.


Suchen und summieren Sie alle übereinstimmenden Werte in einer Spalte oder mehreren Spalten

Sehen Sie sich die ersten übereinstimmenden Werte in einer Spalte an und summieren Sie sie:

Wenn Sie den Gesamtwert für die jeweiligen Monate summieren möchten, wie im folgenden Screenshot gezeigt.

Wenden Sie die folgende Formel in eine leere Zelle an und ziehen Sie den Füllpunkt nach unten, um diese Formel in andere Zellen zu kopieren. Jetzt wurden die ersten übereinstimmenden Werte basierend auf dem bestimmten Monat in einer Spalte summiert (siehe Abbildung):

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

Hinweis: In der obigen Formel: H2 ist der Suchwert, den Sie suchen; B1: F1 ist die Spaltenüberschrift, die den Suchwert enthält; B2: F9 Der Datenbereich der numerischen Werte, die Sie summieren möchten.


Überprüfen und summieren Sie alle übereinstimmenden Werte in mehreren Spalten:

Verwenden Sie die folgende Formel, um alle übereinstimmenden Werte in mehreren Spalten anzuzeigen und zu summieren:

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

Hinweis: In der obigen Formel: H2 ist der Suchwert, den Sie suchen; B1: F1 ist die Spaltenüberschrift, die den Suchwert enthält; B2: F9 Der Datenbereich der numerischen Werte, die Sie summieren möchten.


Suchen und summieren Sie die ersten übereinstimmenden oder alle übereinstimmenden Werte mit einer leistungsstarken Funktion

Vielleicht sind die oben genannten Formeln für Sie schwer zu merken. In diesem Fall empfehle ich eine praktische Funktion - Nachschlagen und Summe of Kutools for ExcelMit dieser Funktion können Sie das Ergebnis so einfach wie möglich erhalten.    Klicken Sie hier, um Kutools für Excel jetzt herunterzuladen!


Überprüfen und summieren Sie alle übereinstimmenden Werte in Zeilen und 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.

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

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

Hinweis: In der obigen Formel: B2: F9 ist der Datenbereich der numerischen Werte, die Sie summieren möchten; B1: F1 is Die Spaltenüberschriften enthalten den Suchwert, auf dessen Grundlage Sie summieren möchten. I2 ist der Suchwert in den gesuchten Spaltenüberschriften; A2: A9 Ist, dass die Zeilenüberschriften den Suchwert enthalten, auf dessen Grundlage Sie summieren möchten? H2 ist der Suchwert in den gesuchten Zeilenüberschriften.


8. Vlookup zum Zusammenführen von zwei Tabellen basierend auf einer oder mehreren Schlüsselspalten

Bei Ihrer täglichen Arbeit müssen Sie bei der Analyse der Daten möglicherweise alle erforderlichen Informationen in einer einzigen Tabelle zusammenfassen, die auf einer oder mehreren Schlüsselspalten basiert. Um diesen Job zu lösen, kann Ihnen die Vlookup-Funktion auch einen Gefallen tun.

Vlookup zum Zusammenführen von zwei 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 Produkte und Bestellungen. Jetzt möchten Sie diese beiden Tabellen kombinieren, indem Sie die gemeinsame Produktspalte in einer Tabelle abgleichen.

Formel 1: Verwenden der VLOOKUP-Funktion

Um die beiden Tabellen basierend auf einer Schlüsselspalte zu einer zusammenzuführen, wenden Sie die folgende Formel in eine leere Zelle an, in der Sie das Ergebnis erhalten möchten, und ziehen Sie dann den Füllpunkt nach unten auf die Zellen, auf die Sie diese Formel anwenden möchten Holen Sie sich eine zusammengeführte Tabelle, wobei die Auftragsspalte mit den ersten Tabellendaten basierend auf den Schlüsselspaltendaten verknüpft wird.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

Hinweis: In der obigen Formel A2 ist der Wert, den Sie suchen, E2: F8 ist die zu durchsuchende Tabelle, die Nummer 2 ist die Spaltennummer in der Tabelle, aus der der Wert abgerufen werden soll.

Formel 2: Verwenden von INDEX- und MATCH-Funktionen

Wenn Ihre allgemeinen Daten auf der rechten Seite und die zurückgegebenen Daten in der linken Spalte in der zweiten Tabelle zum Zusammenführen der Auftragsspalte vorhanden sind, kann die Vlookup-Funktion den Job nicht ausführen. Um von rechts nach links nach oben zu schauen, können Sie die Funktionen INDEX und MATCH verwenden, um die Vlookup-Funktion zu ersetzen.

Bitte kopieren Sie die folgende Formel oder geben Sie sie in eine leere Zelle ein. Kopieren Sie dann die Formel in die Spalte. Die Auftragsspalte wurde mit der ersten Tabelle verknüpft (siehe Abbildung):

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

Hinweis: In der obigen Formel A2 ist der Suchwert, den Sie suchen, E2: E8 ist der Datenbereich, den Sie zurückgeben möchten. F2: F8 ist der Suchbereich, der den Suchwert enthält.


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

Wenn die beiden Tabellen, denen Sie beitreten möchten, mehrere Schlüsselspalten haben, um die Tabellen basierend auf diesen gemeinsamen Spalten zusammenzuführen, können Ihnen die Funktionen INDEX und MATCH helfen.

Die generische Formel zum Zusammenführen von zwei Tabellen basierend auf mehreren Schlüsselspalten lautet:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1. 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)

Hinweis: In der obigen Formel wird der folgende Screenshot wie folgt dargestellt:

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

Tipps: In Excel 2016 und späteren Versionen können Sie auch die verwenden Power Query Funktion zum Zusammenführen von zwei oder mehr Tabellen zu einer Tabelle basierend auf Schlüsselspalten. Bitte klicken Sie, um die Details Schritt für Schritt zu erfahren.

9. Vlookup-Übereinstimmungswerte über mehrere Arbeitsblätter hinweg

Haben Sie jemals versucht, Werte über mehrere Arbeitsblätter hinweg anzuzeigen? Angenommen, ich habe die folgenden drei Arbeitsblätter mit Datenbereich, und jetzt möchte ich einen Teil der entsprechenden Werte basierend auf den Kriterien aus diesen drei Arbeitsblättern abrufen, um das Ergebnis wie im folgenden Screenshot zu erhalten. In diesem Fall ist die Vlookup-Werte in mehreren Arbeitsblättern Tutorial kann Ihnen Schritt für Schritt einen Gefallen tun.


VLOOKUP-übereinstimmende Werte behalten die Zellenformatierung bei

1. Vlookup, um die Zellenformatierung (Zellenfarbe, Schriftfarbe) zusammen mit dem Suchwert abzurufen

Wie wir alle wissen, kann uns die normale Vlookup-Funktion nur dabei helfen, den übereinstimmenden Wert aus einem anderen Datenbereich zurückzugeben. Manchmal möchten Sie jedoch möglicherweise den entsprechenden Wert zusammen mit der Zellenformatierung zurückgeben, z. B. Füllfarbe, Schriftfarbe, Schriftstil wie unten Screenshot gezeigt. In diesem Abschnitt wird erläutert, wie Sie die Zellenformatierung mit dem zurückgegebenen Wert in Excel abrufen.

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

1. Klicken Sie im Arbeitsblatt mit den Daten, die Sie für Vlookup verwenden möchten, mit der rechten Maustaste auf die Registerkarte Blatt und wählen 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.

VBA-Code 1: Vlookup zum Abrufen der Zellenformatierung zusammen mit dem Suchwert

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

3. Immer 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.

VBA-Code 2: Vlookup zum Abrufen der Zellenformatierung zusammen mit dem Suchwert

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. Klicken Sie nach dem Einfügen der obigen Codes auf Tools > Referenzen in Microsoft Visual Basic für Applikationen Fenster. Dann überprüfen Sie die Microsoft Script-Laufzeit Kontrollkästchen in der Referenzen - VBAProject Dialogbox. Siehe Screenshots:

5. Dann klick OK Um das Dialogfeld zu schließen und dann das Codefenster zu speichern und zu schließen, gehen Sie jetzt zum Arbeitsblatt zurück und wenden Sie diese Formel an: =LookupKeepFormat(E2,$A$1:$C$10,3) in eine leere Zelle, in der Sie das Ergebnis ausgeben möchten, und drücken Sie dann die Eingabetaste. Siehe Screenshot:

Hinweis: In der obigen Formel E2 ist der Wert, den Sie nachschlagen werden, A1: C10 ist der Tabellenbereich und die Nummer 3 ist die Spaltennummer der Tabelle, für die der übereinstimmende Wert zurückgegeben werden soll.

6. Wählen Sie dann die erste Ergebniszelle aus und ziehen Sie den Füllpunkt nach unten, um alle Ergebnisse zusammen mit ihrer Formatierung zu erhalten. Siehe Screenshot.


2. Behalten Sie das Datumsformat von einem von Vlookup zurückgegebenen Wert bei

Wenn Sie die Vloook-Funktion verwenden, um den übereinstimmenden Datumsformatwert nachzuschlagen und zurückzugeben, wird normalerweise ein Zahlenformat angezeigt (siehe Abbildung unten). Um das Datumsformat von einem zurückgegebenen Ergebnis fernzuhalten, sollten Sie die TEXT-Funktion in die Vlookup-Funktion einschließen.

Wenden Sie die folgende Formel in eine leere Zelle an und ziehen Sie dann den Füllpunkt, um diese Formel in andere Zellen zu kopieren. Alle übereinstimmenden Daten wurden wie im folgenden Screenshot gezeigt zurückgegeben:

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

Hinweis: In der obigen Formel E2 ist der Look-Wert, A2: C9 ist der Suchbereich, die Nummer 3 ist die Spaltennummer, für die der Wert zurückgegeben werden soll. mm/dd/yyy ist das Datumsformat, das Sie beibehalten möchten.


3. Suchen Sie den übereinstimmenden Wert mit dem Zellenkommentar und geben Sie ihn zurück

Haben Sie jemals versucht, in Vlookup nicht nur die übereinstimmenden Zellendaten, sondern auch den Zellkommentar in Excel zurückzugeben, wie im folgenden Screenshot gezeigt? Um diese Aufgabe zu lösen, kann Ihnen die folgende benutzerdefinierte Funktion einen Gefallen tun.

1. Halten Sie die Taste gedrückt 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. Speichern und schließen Sie dann das Codefenster und geben Sie die folgende Formel ein: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) in eine leere Zelle, um das Ergebnis zu suchen, und ziehen Sie dann den Füllpunkt, um diese Formel in andere Zellen zu kopieren. Jetzt werden die übereinstimmenden Werte sowie die Kommentare sofort zurückgegeben, siehe Screenshot:

Hinweis: In der obigen Formel D2 ist der Suchwert, für den Sie den entsprechenden Wert zurückgeben möchten. A2: B9 ist die Datentabelle, die Sie verwenden möchten, die Nummer 2 ist die Spaltennummer, die den übereinstimmenden Wert enthält, den Sie zurückgeben möchten.


4. Beschäftige dich mit dem Text und den reellen Zahlen in Vlookup

Zum Beispiel habe ich einen Datenbereich, die ID-Nummer in der Originaltabelle ist das Zahlenformat. In der Suchzelle, die als Text gespeichert ist, wird bei Anwendung der normalen Vlookup-Funktion ein # N / A-Fehlerergebnis angezeigt (siehe Abbildung unten) gezeigt. Wie können Sie in diesem Fall die richtigen Informationen erhalten, wenn die Suchnummer und die ursprüngliche Nummer in der Tabelle das unterschiedliche Datenformat haben?

Wenden Sie für den Umgang mit Text und reellen Zahlen in der Vlookup-Funktion die folgende Formel in eine leere Zelle an und ziehen Sie den Füllpunkt nach unten, um diese Formel zu kopieren. Sie erhalten die richtigen Ergebnisse wie im folgenden Screenshot gezeigt:

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

Hinweise:

  • 1. In der obigen Formel D2 ist der Suchwert, für den Sie den entsprechenden Wert zurückgeben möchten. A2: B8 ist die Datentabelle, die Sie verwenden möchten, die Nummer 2 ist die Spaltennummer, die den übereinstimmenden Wert enthält, den Sie zurückgeben möchten.
  • 2. Diese Formel funktioniert auch gut, wenn Sie nicht sicher sind, wo Sie Zahlen und wo Sie Text haben.

Laden Sie die VLOOKUP-Beispieldateien herunter

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Super Formula Bar (leicht mehrere Textzeilen und Formeln bearbeiten); Layout lesen (leichtes Lesen und Bearbeiten einer großen Anzahl von Zellen); In gefilterten Bereich einfügen...
  • Zellen / Zeilen / Spalten zusammenführen und Speichern von Daten; Inhalt geteilter Zellen; Kombinieren Sie doppelte Zeilen und Summe / Durchschnitt... doppelte Zellen verhindern; Bereiche vergleichen...
  • Wählen Sie Duplizieren oder Eindeutig Reihen; Wählen Sie Leere Zeilen (alle Zellen sind leer); Super Find und Fuzzy Find in vielen Arbeitsmappen; Zufällige Auswahl ...
  • Exakte Kopie Mehrere Zellen ohne Änderung der Formelreferenz; Referenzen automatisch erstellen zu mehreren Blättern; Aufzählungszeichen einfügen, Kontrollkästchen und mehr ...
  • Lieblingsformeln und schnell einfügen, Bereiche, Diagramme und Bilder; Zellen verschlüsseln mit Passwort; Mailingliste erstellen und E-Mails senden ...
  • Text extrahieren, Text hinzufügen, Nach Position entfernen, Leerzeichen entfernen;; Paging-Zwischensummen erstellen und drucken; Inhalt und Kommentare zwischen Zellen konvertieren...
  • Superfilter (Speichern und Anwenden von Filterschemata auf andere Blätter); Erweiterte Sortierung nach Monat / Woche / Tag, Häufigkeit und mehr; Spezialfilter fett, kursiv ...
  • Kombinieren Sie Arbeitsmappen und Arbeitsblätter;; Tabellen basierend auf Schlüsselspalten zusammenführen; Daten in mehrere Blätter aufteilen; Batch-Konvertierung von xls, xlsx und PDF...
  • Pivot-Tabellengruppierung nach Wochennummer, Wochentag und mehr ... Entsperrte, gesperrte Zellen anzeigen durch verschiedene Farben; Markieren Sie Zellen mit Formel / Name...
kte tab 201905
  • Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
  • Erhöht Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
officetab unten
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Anton · 29 days ago
    Здравствуйте, возможно ли сравнить любое значение из столбцов в таблице. Суть в том, что значения сделаны неправильно и есть слова "CO" "BE" как сделанные на английском так и на русском, чтобы каждый раз не делать замену их неудобно, т.к. файл обновляется постоянно. Необходимо чтобы Впр искал любой вариант из первого и второго столбца. 1 столбец русские символы, 2-ой английские.