VLOOKUP für zwei oder mehr Kriterienspalten – Excel University

Wenn Sie jemals versucht haben, eine VLOOKUP-Funktion mit zwei oder mehr Kriterienspalten zu verwenden, haben Sie dies schnell festgestellt Es wurde einfach nicht für diesen Zweck gebaut. Glücklicherweise gibt es eine andere Funktion, die je nach dem, was Sie zurückgeben möchten, als Alternative zu VLOOKUP fungieren kann.

Mehrspaltiges Suchziel

Lassen Sie uns zunächst unser Ziel anhand von betrachten eine Beispielarbeitsmappe. Wir haben einige Informationen aus unserem Buchhaltungssystem exportiert und es werden im Wesentlichen die Transaktionssummen für den Monat nach Klasse und Konto zusammengefasst. Ein Beispiel für den Export ist unten dargestellt:

Aus diesen exportierten Daten möchten wir ausgewählte Beträge basierend auf dem abrufen Klassen- und Kontospalten. Wir möchten die Beträge abrufen und in unseren kleinen Bericht einfügen, der unten abgebildet ist:

Wenn Sie mit dem VLOOKUP vertraut sind Funktion, es fühlt sich natürlich an, zu versuchen, den Bericht mit dieser Funktion zu erstellen, da dies schließlich eine Suchaufgabe ist. Und Suchaufgaben lassen sich am besten mit herkömmlichen Suchfunktionen lösen… richtig? Es hängt davon ab. Dies hängt davon ab, was Sie abrufen möchten.

Bedingte Summierung für Suchvorgänge

Wenn Sie versuchen, einen numerischen Wert abzurufen, z. B. einen Betrag, ist dies bei einer herkömmlichen Suchfunktion möglicherweise nicht der Fall Sei deine beste Wahl. Hier ist der Grund. Ab Excel 2007 hat Microsoft die bedingte Summierungsfunktion SUMIFS integriert. Diese Funktion zum Summieren mehrerer Bedingungen dient zum Addieren einer Zahlenspalte und enthält nur Zeilen, die eine oder mehrere Bedingungen erfüllen. Beginnen sich die Punkte bereits zu verbinden?

Wenn wir diese Idee auf unsere jeweilige Aufgabe anwenden, werden wir schnell erkennen, dass wir diese bedingte Summierungsfunktion verwenden können, um unsere Berichtswerte abzurufen.

Das erste Argument der SUMIFS-Funktion ist der Summenbereich, dh die Spalte der hinzuzufügenden Zahlen. In unserem Fall die Spalte mit dem Wert, den wir zurückgeben möchten. Die verbleibenden Argumente kommen paarweise: der Kriterienbereich und der Kriterienwert.

Es ist hilfreich, über die Funktion in diesen Begriffen nachzudenken: Addieren Sie diese Spalte (Argument 1) und schließen Sie nur die Zeilen ein, in denen diese Spalte enthalten ist (Argument 2) ist gleich diesem Wert (Argument 3), und wo diese Spalte (Argument 4) gleich diesem Wert ist (Argument 5), und wo … und so weiter, bis zu 127 Paare.

Um unseren Bericht zu füllen, rufen wir die Betragswerte aus dem Export ab und stimmen die Klassen- und Kontospalten wie unten gezeigt ab.

Wenn es zufällig mehrere Zeilen mit derselben Klasse und denselben Konten gibt, gibt die SUMIFS-Funktion die Summe aller übereinstimmenden Elemente zurück.

Wie Sie sehen können, liegt der Wert bei Ihnen Wenn Sie versuchen, eine Zahl zurückzugeben, können Sie mit der Funktion SUMIFS ganz einfach mehrspaltige Suchvorgänge durchführen. Was aber, wenn der Wert, den Sie zurückgeben möchten, keine Zahl ist? Nun, dann müssen Sie eine herkömmliche Suchfunktion verwenden, wie unten beschrieben.

Verwenden von VLOOKUP mit der SUMIFS-Methode

Eine Methode besteht darin, VLOOKUP und SUMIFS in einer einzigen Formel zu verwenden. Im Wesentlichen verwenden Sie SUMIFS als erstes Argument von VLOOKUP. Diese Methode wird in diesem Beitrag der Excel-Universität ausführlich behandelt:

Verwenden von VLOOKUP mit der CONCATENATE-Methode

Wenn Sie versuchen, eine Textzeichenfolge anstelle einer Zahl zurückzugeben, oder eine Version von verwenden Excel ohne SUMIFS, dann müssen Sie wahrscheinlich eine herkömmliche Suchfunktion wie VLOOKUP zusammen mit der CONCATENATE-Funktion verwenden, um eine einzelne eindeutige Suchspalte zu generieren. Dieser Ansatz ist ziemlich gut dokumentiert, aber die Grundidee lautet wie folgt: Erstellen Sie zuerst eine einzelne Suchspalte und verwenden Sie dann VLOOKUP.

Unser Beispiel ist eine Mitarbeiterliste, wie unten dargestellt:

Wir müssen den Status aus der Mitarbeiterliste für unseren kleinen Bericht abrufen, der unten gezeigt wird:

Da der Wert, den wir zurückgeben möchten, der Status eine Textzeichenfolge und keine Zahl ist, ist die Verwendung der SUMIFS-Funktion ausgeschlossen. Daher müssen wir mit VLOOKUP und CONCATENATE auf die alte Schule gehen.

Zunächst erstellen wir eine Hilfsspalte, in der im Grunde die kombinierten Suchwerte erstellt werden. Dies kann leicht mit der Funktion CONCATENATE oder dem Verkettungsoperator (&) erreicht werden. Diese neue Nachschlagespalte ist in der folgenden Spalte B dargestellt:

Jetzt haben wir eine einzelne Nachschlagespalte, die mit a verwendet werden kann traditionelle Suchfunktion wie VLOOKUP. Der Bericht kann ausgefüllt werden, indem die kombinierten Namen innerhalb des neuen Suchbereichs nachgeschlagen werden (siehe unten):

Derselbe Ansatz kann verwendet werden, wenn zwei, drei oder mehr Nachschlagespalten berücksichtigt werden müssen.

Fazit

Zusätzlich zur Möglichkeit, mehrspaltige Suchvorgänge durchzuführen, wenn der Rückgabewert numerisch ist, bietet die SUMIFS-Funktion im Vergleich zu herkömmlichen Suchfunktionen zusätzliche Vorteile. Beispielsweise wird Null zurückgegeben, wenn kein übereinstimmender Wert gefunden wird, die Summe aller Übereinstimmungen zurückgegeben, Vergleichsoperatoren unterstützt und es wird nicht unterbrochen, wenn eine neue Spalte zwischen den Such- und Rückgabespalten eingefügt wird.

Wenn Sie also die VLOOKUP-Funktion für eine Suchaufgabe ausschalten möchten, sollten Sie stattdessen SUMIFS verwenden. Ob Sie es glauben oder nicht, die SUMIFS-Funktion bietet eine wunderbare Suchfunktion.

Wenn Sie andere bevorzugte Ansätze für mehrspaltige Suchvorgänge haben, würden wir gerne mehr hören. Bitte schreiben Sie unten einen Kommentar.

Beispieldatei

Wenn Sie mit der Arbeitsmappe spielen möchten, mit der die obigen Screenshots erstellt wurden, können Sie die Beispieldatei herunterladen:

MultColumnLookup