Wenn Sie hier sind, bedeutet dies, dass Sie versuchen, mit VLOOKUP mehrere Werte aus einem Datensatz abzurufen. Aber lassen Sie es uns klarstellen, VLOOKUP KANN NICHT MEHRERE WERTE ZURÜCKKEHREN. Das heißt aber nicht, dass wir das nicht können. Wir können eine Suche durchführen, bei der mehrere Werte anstelle des ersten nur abgerufen werden.

286

Generische Formel

{=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(first cell of lookup_value_range)+1),ROW(1:1)))}

Array: Der Bereich, aus dem Sie Daten abrufen möchten.

lookup_value: Ihr lookup_value, den Sie filtern möchten.

lookup_value_range: Der Bereich, in dem Sie lookup_value filtern möchten.

Die erste Zelle im Bereich lookup_value: Wenn Ihr Bereich lookup_value $ A $ 5: $ A $ 100 beträgt, sind es $ A $ 5.

Wichtig: Alles sollte „link: / excel-range-name-absolute-reference-in-excel [absolut referenziert]“ sein. lookup_value kann je nach Anforderung relativ sein.

Geben Sie es als Array-Formel ein. Drücken Sie nach dem Schreiben der Formel STRG + UMSCHALT + EINGABETASTE, um eine Array-Formel zu erstellen.

Beispiel für die Suche nach mehreren Ergebnissen Ich habe diese Studentendaten im Bereich A2: E14. In Zelle G1 habe ich eine Dropdown-Liste von Regionswerten, z. Zentral, Ost, Nord, Süd und West.

Jetzt möchte ich, unabhängig von der Region, die ich in G1 habe, eine Liste aller Schüler aus dieser Region in der Spalte H anzeigen.

287

Um mehrere Werte in Excel nachzuschlagen, identifizieren wir unsere Variablen.

Array: $ C $ 2: $ C $ 14 Lookup_Wert: $ G $ 1 Lookup_Wert_Range: $ A $ 2: $ A $ 14 Die erste Zelle des Lookup_Wert-Bereichs: $ A $ 2 Nach den obigen Daten lautet unsere Formel zum Abrufen mehrerer Werte in Excel:

{=INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1)))}

Kopieren Sie diese Formel in H2 und drücken Sie STRG + UMSCHALT + EINGABETASTE. Ziehen Sie nun diese Formel nach unten, bis Sie einen # NUM-Fehler erhalten. #NUM gibt an, dass für diesen Lookup-Wert keine weiteren übereinstimmenden Werte mehr vorhanden sind, und dies ist das Ende der Liste.

288

Wenn #NUM Sie nervt, können Sie die link: / logische-Formeln-excel-iferror-Funktion [IFERROR-Funktion] vor der Formel haben und anstelle des Fehlers einige aussagekräftige Informationen anzeigen.

{=IFERROR(INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1))),"--List Ends--")}

Dies zeigt –List Ends– an, wenn alle Elemente angezeigt werden.

Jetzt wollen wir verstehen, wie es funktioniert.

Die Formel mag zwar komplex aussehen, aber die Idee ist einfach. Wir müssen die Indexnummer jedes Wertvorkommens abrufen und dann Werte mit der Funktion „link: / lookup-formulas-excel-index-function [INDEX function]“ von Excel abrufen.

Daher besteht die größte Herausforderung darin, ein Array von Indexnummern von lookup_value zu erhalten. Um Indexnummern zu erhalten, haben wir „link: / tips-if-condition-in-excel [IF]“ und „link: / lookup-and-reference-excel-row-function [ROW]“

verwendet Funktionen. Die Formel ist in der Tat insgesamt komplex. Lassen Sie es uns zusammenfassen.

Wir möchten Werte aus der Studentenspalte abrufen, daher lautet unser Array für „link: / lookup-formulas-excel-index-function [INDEX-Funktion]“ $ C $ 2: $ C $ 14. Jetzt müssen wir Zeilennummern von $ A $ 2: $ A $ 14 (Suchwert) angeben, in denen der Wert von G1 vorhanden ist (sagen wir, G1 hat vorerst einen zentralen Wert).

IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): Dieser Teil gibt nun die Zeilennummer zurück, wenn der Wert eines Zellencountains von G1 (zentral) im Bereich $ A $ 2: $ A liegt $ 14 else gibt FALSE zurück. In diesem Beispiel wird

\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE} zurückgegeben.

  • Da das obige Array die Zeilennummer aus der ersten Zeile (1: 1) enthält und wir eine Zeile ab unserem Array (A2: A14) benötigen, verwenden wir dazu -ROW ($ A $ 2). +1 in der IF-Formel. Dies gibt eine Anzahl von Zeilen zurück, bevor unser Array gestartet wird.

In diesem Beispiel ist es -1. Wenn es von A3 ausgeht, wird -2 usw. zurückgegeben. Dies Die Zahl wird von jeder Zahl in dem von IF zurückgegebenen Array abgezogen. Also schließlich IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) Dies wird übersetzt in \ {1; FALSE; FALSE; FALSE; FALSE; 6; 7; FALSE; FALSE; 10; FALSE; FALSE; FALSE}.

  • Als nächstes wird dieses Array von`link: / umgeben statistische-Formeln-Excel-kleine-Funktion [SMALL] `

Funktion. Diese Funktion gibt die N-te kleine zurück est-Wert im angegebenen Array. Jetzt haben wir link: / static-formulas-excel-small-function [SMALL] (\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE} , REIHE (1: 1)).

ROW (1: 1) gibt 1 zurück. Daher gibt die obige Funktion den 1. kleinsten Wert im Array zurück, nämlich 2.

Wenn Sie diese Formel in die folgenden Zellen kopieren, wird ROW (1: 1) zu ROW (2: 2) und gibt den zweitkleinsten Wert im Array zurück, nämlich 7 usw. Dadurch kann die Funktion zuerst den zuerst gefundenen Wert zurückgeben. Wenn Sie jedoch zuerst den zuletzt gefundenen Wert erhalten möchten, verwenden Sie die Funktion LARGE anstelle der Funktion SMALL.

Bei Verwendung der über den Funktionen zurückgegebenen Werte gibt die INDEX-Funktion problemlos jeden übereinstimmenden Wert aus einem Bereich zurück.

Also, Leute, Sie können mehrere entsprechende Werte mit einem Suchwert nachschlagen. Im obigen GIF habe ich die IFERROR-Funktion verwendet, um Fehler und bedingte Formatierungen abzufangen, um sie ein wenig visuell zu gestalten. Im obigen Beispiel habe ich viele andere Funktionen und Techniken verwendet, die ich in anderen Artikeln erklärt habe.

Datei herunterladen: