In diesen Beispielen erfahren Sie, wie Sie mehrere Kriterien anzeigen, eine bestimmte Instanz oder alle Übereinstimmungen zurückgeben und dynamisches Vlookup ausführen Mehrere Blätter und mehr.
Dies ist der zweite Teil der Serie, mit dem Sie die Leistung von Excel VLOOKUP nutzen können. Die Beispiele implizieren, dass Sie wissen, wie diese Funktion funktioniert. Wenn nicht, liegt es nahe, mit den grundlegenden Verwendungen von VLOOKUP in Excel zu beginnen.
Bevor ich fortfahre, möchte ich Sie kurz an die Syntax erinnern:
Nachdem sich nun alle auf derselben Seite befinden, sehen wir uns die Beispiele für erweiterte VLOOKUP-Formeln genauer an:
- So sehen Sie mehrere Kriterien in Excel
- Vlookup und n-te Übereinstimmung zurückgeben
- Mehrere Vlookup-Werte
- Vlookup basierend auf Zeilen- und Spaltenwerten
- Verschachtelte Vlookup-Formel
- VLOOKUP und INDIRECT zum Abrufen von Daten aus mehreren Arbeitsblättern
Anzeigen mehrerer Kriterien
Die Excel VLOOKUP-Funktion ist sehr hilfreich bei der Suche in einer Datenbank Für einen bestimmten Wert fehlt jedoch eine wichtige Funktion – die Syntax ermöglicht nur einen Suchwert. Was ist jedoch, wenn Sie mit mehreren Bedingungen nachschlagen möchten? Es stehen verschiedene Lösungen zur Auswahl.
Formel 1. VLOOKUP basierend auf tw o Werte
Angenommen, Sie haben eine Liste mit Bestellungen und möchten die Menge anhand von zwei Kriterien ermitteln: Kundenname und Produkt. Ein erschwerender Faktor ist, dass jeder Kunde mehrere Produkte bestellt hat, wie in der folgenden Tabelle gezeigt:
Eine übliche VLOOKUP-Formel funktioniert hier nicht Situation, weil die erste gefundene Übereinstimmung basierend auf einem einzelnen von Ihnen angegebenen Suchwert zurückgegeben wird.
Um dies zu überwinden, können Sie eine Hilfsspalte hinzufügen und die Werte aus zwei Suchspalten (Kunde und Produkt) dort verketten. Es ist wichtig, dass die Hilfsspalte die Spalte ganz links im Tabellenarray ist, da Excel VLOOKUP immer nach dem Suchwert sucht.
Fügen Sie also links neben Ihrer Tabelle eine Spalte hinzu und kopieren Sie sie die folgende Formel in dieser Spalte. Dadurch wird die Hilfsspalte mit den Werten aus den Spalten B und C gefüllt (das Leerzeichen wird zur besseren Lesbarkeit dazwischen verkettet):
=B2&" "&C2
Verwenden Sie dann eine Standard-VLOOKUP-Formel und fügen Sie beide Kriterien in das Argument lookup_value ein, getrennt durch ein Leerzeichen:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Oder geben Sie die Kriterien in separate Zellen ein (in unserem Fall G1 und G2) und verketten Sie diese Zellen:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
Wie wir Wenn Sie einen Wert aus Spalte D zurückgeben möchten, die im Tabellenarray an vierter Stelle steht, verwenden wir 4 für col_index_num. Das Argument range_lookup wird auf FALSE gesetzt, um eine genaue Übereinstimmung mit Vlookup zu erzielen. Der folgende Screenshot zeigt das Ergebnis:
Wenn sich Ihre Nachschlagetabelle in einem anderen Blatt befindet, fügen Sie den Namen des Blattes in Ihre VLOOKUP-Formel ein. Beispiel:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
Alternativ können Sie einen benannten Bereich für die Nachschlagetabelle (z. B. Bestellungen) erstellen, um die Formel zu vereinfachen. zu lesen:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
Weitere Informationen finden Sie unter Anzeigen von einem anderen Blatt in Excel.
Formel 2. Excel-VLOOKUP mit mehreren Bedingungen
Theoretisch können Sie mit dem obigen Ansatz mehr als zwei Kriterien für Vlookup verwenden. H. Es gibt jedoch ein paar Einschränkungen. Erstens ist ein Suchwert auf 255 Zeichen begrenzt, und zweitens erlaubt das Design des Arbeitsblatts möglicherweise nicht das Hinzufügen einer Hilfsspalte.
Glücklicherweise bietet Microsoft Excel häufig mehr als eine Möglichkeit, dasselbe zu tun. Um mehrere Kriterien anzuzeigen, können Sie entweder eine INDEX MATCH-Kombination oder die kürzlich in Office 365 eingeführte XLOOKUP-Funktion verwenden.
Zum Beispiel, um anhand von 3 verschiedenen Werten (Datum, Kundenname und Produkt) nachzuschlagen. Verwenden Sie eine der folgenden Formeln:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Wobei:
Eine ausführliche Erläuterung der Formeln finden Sie unter:
- XLOOKUP mit mehreren Kriterien
- INDEX MATCH-Formel mit mehreren Kriterien
Verwenden von VLOOKUP zum Abrufen der 2., 3. oder n-ten Übereinstimmung
Wie Sie bereits wissen, kann Excel VLOOKUP nur einen übereinstimmenden Wert abrufen, genauer gesagt, es gibt die erste gefundene Übereinstimmung zurück. Aber was ist, wenn Ihr Lookup-Array mehrere Übereinstimmungen enthält und Sie die 2. oder 3. Instanz erhalten möchten? Die Aufgabe klingt ziemlich kompliziert, aber die Lösung existiert!
Formel 1. Vlookup N-te Instanz
Angenommen, Sie haben Kundennamen in einer Spalte, die Produkte, die sie in einer anderen gekauft haben, und Sie suchen nach dem 2. oder 3. Produkt, das von einem bestimmten Kunden gekauft wurde.
Der einfachste Weg besteht darin, links in der Tabelle eine Hilfsspalte hinzuzufügen, wie wir es im ersten Beispiel getan haben. Dieses Mal werden wir es jedoch mit Kundennamen und Vorkommensnummern wie „John Doe1“, „John Doe2“ usw. füllen.
Um das Vorkommen zu erhalten, verwenden Sie die COUNTIF-Funktion mit einer Referenz für gemischte Bereiche (die Die erste Referenz ist absolut und die zweite ist relativ wie $ B $ 2: B2). Da sich die relative Referenz basierend auf einer Position der Zelle ändert, an der die Formel kopiert wird, wird sie in Zeile 3 zu $ B $ 2: B3, in Zeile 4 – $ B $ 2: B4 usw.
Mit dem Kundennamen (B2) verknüpft, hat die Formel folgende Form:
=B2&COUNTIF($B$2:B2, B2)
Die obige Formel geht an A2 und Anschließend kopieren Sie es in so viele Zellen wie nötig.
Geben Sie anschließend den Zielnamen und die Vorkommensnummer in separate Zellen (F1 und F2) ein und verwenden Sie die folgende Formel, um ein bestimmtes Vorkommen anzuzeigen:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Formel 2. Vlookup 2. Vorkommen
Wenn Sie nach der zweiten Instanz des Suchwerts suchen, können Sie auf die Hilfsspalte verzichten. Erstellen Sie stattdessen das Tabellenarray dynamisch, indem Sie die INDIRECT-Funktion zusammen mit MATCH verwenden:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Wobei:
- E1 ist der Suchwert
- A2: A11 ist der Suchbereich
- B11 ist die letzte (rechts unten) Zelle der Nachschlagetabelle
Bitte beachten Sie, dass die obige Formel für einen bestimmten Fall geschrieben wurde, in dem Datenzellen in der Nachschlagetabelle in Zeile 2 beginnen. Wenn sich Ihre Tabelle irgendwo in befindet Verwenden Sie in der Mitte des Blattes diese universelle Formel, wobei A1 die obere linke Zelle der Nachschlagetabelle ist, die eine Spaltenüberschrift enthält:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Funktionsweise dieser Formel
Hier ist der Schlüsselteil der Formel, mit dem ein dynamischer Lookup-Bereich erstellt wird:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
Die für die genaue Übereinstimmung konfigurierte MATCH-Funktion (0 im letzten Argument) vergleicht den Zielnamen (E1) mit der Liste der Namen (A2: A11) und gibt die Position der ersten gefundenen Übereinstimmung zurück, nämlich 3 in o Ihr Fall. Diese Zahl wird als Startzeilenkoordinate für den vlookup-Bereich verwendet, daher fügen wir 2 hinzu (+1, um die erste Instanz auszuschließen, und +1, um Zeile 1 mit den Spaltenüberschriften auszuschließen). Alternativ können Sie 1 + REIHE (A1) verwenden, um die erforderliche Anpassung automatisch basierend auf der Position der Kopfzeile (in unserem Fall A1) zu berechnen.
Als Ergebnis erhalten wir die folgende Textzeichenfolge: welches INDIRECT in eine Bereichsreferenz konvertiert:
INDIRECT("A"&5&":B11") -> A5:B11
Dieser Bereich geht an das Argument table_array von VLOOKUP und zwingt ihn, die Suche in Zeilen zu starten 5, wobei die erste Instanz des Suchwerts weggelassen wird:
VLOOKUP(E1, A5:B11, 2, FALSE)
So suchen Sie mehrere Werte in Excel h2 und geben sie zurück >
Die Excel VLOOKUP-Funktion gibt nur eine Übereinstimmung zurück. Gibt es eine Möglichkeit, mehrere Instanzen zu durchsuchen? Ja, das ist zwar nicht einfach. Dies erfordert die kombinierte Verwendung mehrerer Funktionen wie INDEX, SMALL und ROW ist eine Array-Formel.
Im Folgenden können beispielsweise alle Vorkommen des Suchwerts F2 im Suchbereich B2: B16 gefunden und zurückgegeben werden Mehrere Übereinstimmungen aus Spalte C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Es gibt zwei Möglichkeiten, die Formel in Ihr Arbeitsblatt einzugeben:
- Geben Sie die Formel in die erste Zelle ein, drücken Sie Strg + Umschalt + Eingabetaste und ziehen Sie sie dann auf einige weitere Zellen.
- Wählen Sie mehrere benachbarte Zellen in einer einzelnen Spalte aus (F1: F11 in Geben Sie die Formel ein und drücken Sie Strg + Umschalt + Eingabetaste, um sie zu vervollständigen.
In beiden Fällen sollte die Anzahl der Zellen, in die Sie die Formel eingeben, gleich oder größer sein Die maximale Anzahl möglicher Übereinstimmungen.
Eine ausführliche Erläuterung der Formellogik und weitere Beispiele finden Sie unter VLOOKUP mehrerer Werte in Excel
Anzeigen in Zeilen und Spalten (bidirektionale Suche)
Zwei-Wege-Suche (auch bekannt als Matrix-Suche oder zweidimensionale Suche) ist ein ausgefallenes Wort für die Suche nach einem Wert am Schnittpunkt einer bestimmten Zeile und Spalte.Es gibt verschiedene Möglichkeiten, eine zweidimensionale Suche in Excel durchzuführen. Da der Schwerpunkt dieses Tutorials jedoch auf der VLOOKUP-Funktion liegt, werden wir sie natürlich verwenden.
In diesem Beispiel nehmen wir die Tabelle unten mit monatlichen Verkäufen und Erarbeiten einer VLOOKUP-Formel zum Abrufen der Verkaufszahlen für einen bestimmten Artikel in einem bestimmten Monat.
Bei Artikelnamen in A2: A9, Monatsnamen in B1: F1, dem Zielartikel In I1 und dem Zielmonat in I2 lautet die Formel wie folgt:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Funktionsweise dieser Formel
Der Kern der Formel ist die Standard-VLOOKUP-Funktion, die nach einer genauen Übereinstimmung mit dem Suchwert in I1 sucht. Da wir dies jedoch nicht wissen In welcher Spalte genau die Verkäufe für einen bestimmten Monat liegen, können wir die Spaltennummer nicht direkt an das Argument col_index_num übergeben. Um diese Spalte zu finden, verwenden wir die folgende MATCH-Funktion:
MATCH(I2, A1:F1, 0)
Die ins Englische übersetzte Formel lautet: Nachschlagen den I2-Wert in A1: F1 und geben Sie seine relative Position im Array zurück. Indem Sie dem 3. Argument 0 geben, weisen Sie MATCH an, den Wert genau gleich dem Suchwert zu finden (ähnlich wie bei der Verwendung von FALSE für das Argument range_lookup von VLOOKUP).
Da Mar in der 4. Spalte steht Im Lookup-Array gibt die MATCH-Funktion 4 zurück, was direkt zum Argument col_index_num von VLOOKUP führt:
VLOOKUP(I1, A2:F9, 4, FALSE)
Bitte beachten Sie Obwohl die Monatsnamen in Spalte B beginnen, verwenden wir A1: I1 für das Sucharray. Dies geschieht, damit die von MATCH zurückgegebene Nummer der Position der Spalte in table_array von VLOOKUP entspricht.
Weitere Informationen zum Durchführen der Matrixsuche in Excel finden Sie unter INDEX MATCH MATCH und andere Formeln für die zweidimensionale Suche.
So führen Sie mehrere Vlookups in Excel durch (verschachtelte Vlookup)
Manchmal kann es vorkommen, dass Ihre Haupttabelle und Nachschlagetabelle keine einzige Spalte gemeinsam haben, sodass Sie keine Vlookup zwischen zwei Tabellen durchführen können. Es gibt jedoch eine andere Tabelle, die nicht die gesuchten Informationen enthält, sondern eine gemeinsame Spalte mit der Haupttabelle und eine weitere gemeinsame Spalte mit der Nachschlagetabelle enthält.
Das folgende Bild zeigt die Situation:
Ziel ist es, die Preise basierend auf den Artikel-IDs in die Haupttabelle zu kopieren. Das Problem ist, dass die Tabelle mit den Preisen nicht die Artikel-IDs enthält, was bedeutet, dass zwei Vlookups in einer Formel erstellt werden müssen.
Erstellen Sie der Einfachheit halber zunächst einige benannte Bereiche :
- Nachschlagetabelle 1 heißt Produkte (D3: E3).
- Nachschlagetabelle 2 heißt Preise (G3: H3).
Die Tabellen können sich in demselben oder in verschiedenen Arbeitsblättern befinden.
Und jetzt führen wir das sogenannte doppelte Vlookup durch, auch bekannt als verschachteltes Vlookup.
Erstellen Sie zunächst ein VLOOKUP Formel zum Suchen des Produktnamens in der Nachschlagetabelle 1 (benannte Produkte) basierend auf der Artikel-ID (A3):
=VLOOKUP(A3, Products, 2, FALSE)
Fügen Sie als Nächstes die obige Formel in das Argument lookup_value einer anderen VLOOKUP-Funktion ein, um Preise aus der Nachschlagetabelle 2 (mit dem Namen Prices) basierend auf dem vom verschachtelten VLOOKUP zurückgegebenen Produktnamen abzurufen:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
Der folgende Screenshot zeigt unsere verschachtelte Vlookup-Formel in Aktion:
So sehen Sie mehrere an Blätter dynamisch
Manchmal haben Sie Daten im gleichen Format, die auf mehrere Arbeitsblätter verteilt sind. Ihr Ziel ist es, Daten aus einem bestimmten Blatt abzurufen, abhängig vom Schlüsselwert in einer bestimmten Zelle.
Dies ist möglicherweise anhand eines Beispiels leichter zu verstehen. Nehmen wir an, Sie haben einige regionale Verkaufsberichte im gleichen Format und möchten die Verkaufszahlen für ein bestimmtes Produkt in bestimmten Regionen abrufen:
Wie im vorherigen Beispiel beginnen wir mit der Definition einiger Namen:
- Bereich A2: B5 im CA-Blatt heißt CA_Sales.
- Bereich A2 : B5 im FL-Blatt heißt FL_Sales.
- Bereich A2: B5 im KS-Blatt heißt KS_Sales.
Wie Sie sehen können, haben alle benannten Bereiche eine gemeinsame Teile (Vertrieb) und Einzelteile (CA, FL, KS). Bitte benennen Sie Ihre Bereiche auf ähnliche Weise, wie es für die Formel, die wir erstellen werden, wesentlich ist.
Formel 1. INDIREKTES VLOOKUP zum dynamischen Abrufen von Daten aus verschiedenen Blättern
Wenn Sie Daten aus mehreren Blättern abrufen möchten, ist eine VLOOKUP-INDIREKTE Formel die beste Lösung – kompakt und leicht verständlich.
In diesem Beispiel organisieren wir die Übersichtstabelle folgendermaßen:
- Geben Sie die gewünschten Produkte in A2 und A3 ein. Dies sind unsere Suchwerte.
- Geben Sie die eindeutigen Teile der genannten Bereiche in B1, C1 und D1 ein.
Und jetzt verketten wir die Zelle, die den eindeutigen Teil (B1) enthält, mit dem gemeinsamen Teil („_Sales“) und geben die resultierende Zeichenfolge an INDIRECT weiter:
INDIRECT(B$1&"_Sales")
Die INDIRECT-Funktion wandelt die Zeichenfolge in einen Namen um, den Excel verstehen kann, und Sie fügen ihn in das Argument table_array von VLOOKUP ein:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
Die obige Formel geht zu B2, und dann kopieren Sie sie nach unten und rechts.
Bitte beachten Sie Folgendes: Im Suchwert ($ A2) haben wir die Spaltenkoordinate mit der absoluten Zellreferenz gesperrt, sodass die Spalte beim Kopieren der Formel nach rechts fest bleibt. In der B $ 1-Referenz haben wir die Zeile gesperrt, weil wir die Spalte möchten Koordinieren Sie, um INDIRECT abhängig von der Spalte, in die die Formel kopiert wird, zu ändern und einen geeigneten Namensteil bereitzustellen:
Wenn Ihre Haupttabelle organisiert ist anders, die Suchwerte in einer Zeile und eindeutige Teile der Bereichsnamen in einer Spalte mn, dann sollten Sie die Zeilenkoordinate im Suchwert (B $ 1) und die Spaltenkoordinate in den Namensteilen ($ A2) sperren:
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Formel 2. VLOOKUP und verschachtelte IFs zum Nachschlagen mehrerer Blätter
In einer Situation, in der Sie gerade haben Bei zwei oder drei Suchblättern können Sie eine relativ einfache VLOOKUP-Formel mit verschachtelten IF-Funktionen verwenden, um das richtige Blatt basierend auf dem Schlüsselwert in einer bestimmten Zelle auszuwählen:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Wobei $ A2 der Suchwert (Elementname) und B $ 1 der Schlüsselwert (Status) ist:
In diesem Fall müssen Sie nicht unbedingt Namen definieren und können externe Verweise verwenden, um auf ein anderes Blatt oder eine andere Arbeitsmappe zu verweisen.
Weitere Formelbeispiele finden Sie unter VLOOKUP über mehrere Blätter in Excel
So verwenden Sie VLOOKUP in Excel. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche auf unserem Blog zu sehen!
Übungsarbeitsmappe zum Herunterladen
Beispiele für erweiterte VLOOKUP-Formeln (XLSX-Datei)
- Excel VLOOKUP-Tutorial für Anfänger
- So suchen Sie mehrere Übereinstimmungen in Excel
- So verwenden Sie VLOOKUP & SUM- oder SUMIF-Funktionen in Excel
- INDEX & MATCH-Funktionen in Excel – eine bessere Alternative zu VLOOKUP
- 4 Möglichkeiten, ein Vlookup mit Groß- und Kleinschreibung in Excel
- Excel VLOOKUP funktioniert nicht – N / A-, NAME- und VALUE-Fehler