Video ansehen – Verwendung der VLOOKUP-Funktion mit mehreren Kriterien

Die Excel VLOOKUP-Funktion kann in ihrer Grundform nach einem Suchwert suchen und den entsprechenden Wert aus der angegebenen Zeile zurückgeben.

Oft muss Excel VLOOKUP jedoch mit mehreren Kriterien verwendet werden.

Verwenden von VLOOKUP mit mehreren Kriterien

Angenommen, Sie haben Daten mit dem Namen des Schülers, dem Prüfungstyp und der Mathe-Punktzahl (wie unten gezeigt):

Die Verwendung der VLOOKUP-Funktion, um die Mathe-Punktzahl für jeden Schüler für die jeweiligen Prüfungsstufen zu erhalten, könnte eine Herausforderung sein.

Man kann argumentieren Eine bessere Option wäre die Umstrukturierung des Datensatzes oder die Verwendung einer Pivot-Tabelle. Wenn das für Sie funktioniert, nichts dergleichen. In vielen Fällen bleiben Sie jedoch bei den vorhandenen Daten hängen, und die Pivot-Tabelle ist möglicherweise keine Option.

In solchen Fällen ist dieses Lernprogramm für Sie bestimmt.

Jetzt gibt es sie Auf zwei Arten können Sie den Suchwert mithilfe von VLOOKUP mit mehreren Kriterien abrufen.

• Verwenden einer Hilfsspalte.
• Verwenden der CHOOSE-Funktion.

VLOOKUP mit mehreren Kriterien – Verwenden einer Hilfsspalte

Ich bin ein Fan von Hilfsspalten in Excel.

Ich finde zwei wesentliche Vorteile der Verwendung von Hilfsspalten gegenüber Arrayformeln:

• Es macht es einfach zu verstehen, was im Arbeitsblatt vor sich geht.
• Es macht es schneller als die Array-Funktionen (erkennbar an großen Datenmengen).

Versteh mich jetzt nicht falsch. Ich bin nicht gegen Array-Formeln. Ich liebe die erstaunlichen Dinge, die mit Array-Formeln gemacht werden können. Ich speichere sie nur für besondere Anlässe, bei denen alle anderen Optionen nicht hilfreich sind.

Um auf die betreffende Frage zurückzukommen, wird die Hilfsspalte benötigt, um ein eindeutiges Qualifikationsmerkmal zu erstellen. Dieses eindeutige Qualifikationsmerkmal kann dann verwendet werden, um den richtigen Wert zu suchen. Zum Beispiel sind drei Matt in den Daten, aber es gibt nur eine Kombination aus Matt und Unit Test oder Matt und Mid-Term.

Hier sind die Schritte:

Wie funktioniert das?

Wir erstellen eindeutige Qualifikationsmerkmale für jede Instanz eines Namens und der Prüfung. In der hier verwendeten VLOOKUP-Funktion wurde der Suchwert in $ F3 & „|“ & G $ 2 geändert, sodass beide Suchkriterien werden kombiniert und als einzelner Suchwert verwendet. Der Suchwert für die VLOOKUP-Funktion in G2 lautet beispielsweise Matt | Unit Test. Jetzt wird dieser Suchwert verwendet, um die Punktzahl von C2: D19 zu erhalten.

Erläuterungen:

Es gibt einige Fragen, die Ihnen wahrscheinlich in den Sinn kommen, also dachte ich mir Ich werde versuchen, es hier zu beantworten:

• Warum habe ich | verwendet? Symbol beim Verbinden der beiden Kriterien? – Unter einigen außergewöhnlich seltenen (aber möglichen) Bedingungen haben Sie möglicherweise zwei Kriterien, die unterschiedlich sind, aber in Kombination das gleiche Ergebnis liefern. Hier ist ein sehr einfaches Beispiel (verzeihen Sie mir meinen Mangel an Kreativität hier):

Beachten Sie, dass während A2 und A3 sind unterschiedlich und B2 und B3 sind unterschiedlich, wobei die Kombinationen am Ende gleich sind. Wenn Sie jedoch ein Trennzeichen verwenden, ist sogar die Kombination unterschiedlich (D2 und D3).

• Warum habe ich die Hilfsspalte zwischen Spalte B und C eingefügt und nicht ganz links? – Das Einsetzen der Hilfssäule ganz links schadet nicht. Wenn Sie sich nicht mit den Originaldaten abfinden möchten, sollte dies der richtige Weg sein. Ich habe es getan, weil ich dadurch weniger Zellen in der VLOOKUP-Funktion verwenden muss. Anstatt 4 Spalten im Tabellenarray zu haben, könnte ich mit nur 2 Spalten auskommen. Aber das bin nur ich.

Jetzt gibt es keine Einheitsgröße, die für alle passt. Einige Benutzer ziehen es möglicherweise vor, keine Hilfsspalte zu verwenden, während Sie VLOOKUP mit mehreren Kriterien verwenden.

Hier ist die Nicht-Hilfsspaltenmethode für Sie.

Laden Sie die Beispieldatei herunter

VLOOKUP mit mehreren Kriterien – Verwenden der CHOOSE-Funktion

Verwenden von Array-Formeln anstelle von Helfer Spalten spart Ihnen Arbeitsblattfläche, und die Leistung kann gleich gut sein, wenn sie weniger häufig in einer Arbeitsmappe verwendet wird.

In Anbetracht des oben verwendeten Datensatzes erhalten Sie hier die Formel, mit der Sie das Ergebnis erhalten :

= VLOOKUP ($ E3 & „|“ & F $ 2, WÄHLEN SIE ({1,2}) , $ A $ 2: $ A $ 19 & „|“ & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19 ), 2,0)

Da dies eine Array-Formel ist, verwenden Sie sie mit Strg + Umschalt + Eingabetaste anstatt nur mit Eingabetaste.

Wie funktioniert das?

Die Formel verwendet auch das Konzept einer Hilfsspalte. Der Unterschied besteht darin, dass die Hilfsspalte nicht in das Arbeitsblatt eingefügt, sondern als virtuelle Hilfsdaten betrachtet wird, die Teil der Formel sind.

Lassen Sie mich Ihnen zeigen, was ich unter virtuellen Hilfsdaten verstehe.

In der obigen Abbildung wird beim Auswählen des CHOOSE-Teils der Formel und Drücken von F9 das Ergebnis angezeigt, dass die Die Formel CHOOSE würde ergeben.

Das Ergebnis ist {„Matt | Unit Test“, 91; „Bob | Unit Test“, 52; ……}

Es ist ein Array, in dem ein Komma steht stellt die nächste Zelle in derselben Zeile dar und das Semikolon stellt dar, dass sich die folgenden Daten in der nächsten Spalte befinden. Daher werden mit dieser Formel zwei Datenspalten erstellt. Eine Spalte hat die eindeutige Kennung und eine die Bewertung.

Jetzt Wenn Sie die VLOOKUP-Funktion verwenden, sucht sie einfach nach dem Wert in der ersten Spalte (dieser virtuellen 2-Spalten-Daten) und gibt die entsprechende Punktzahl zurück.

Laden Sie die herunter Beispieldatei

Sie können auch andere Formeln verwenden, um eine Suche mit mehreren Kriterien durchzuführen (z. B. INDEX / MATCH oder SUMPRODUCT). P. >