So verwenden Sie SVERWEIS mit mehreren Kriterien in Excel

Video ansehen - So verwenden Sie die SVERWEIS-Funktion mit mehreren Kriterien

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

Aber oft besteht die Notwendigkeit, den Excel SVERWEIS mit mehreren Kriterien zu verwenden.

So verwenden Sie SVERWEIS mit mehreren Kriterien

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

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

Man kann argumentieren, dass es besser wäre, den Datensatz umzustrukturieren oder eine Pivot-Tabelle zu verwenden. Wenn das für Sie funktioniert, nichts dergleichen. In vielen Fällen bleiben Sie jedoch bei den Daten, die Sie haben, und eine Pivot-Tabelle ist möglicherweise keine Option.

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

Jetzt gibt es zwei Möglichkeiten, den Lookup-Wert mithilfe von SVERWEIS mit mehreren Kriterien abzurufen.

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

SVERWEIS 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 auf dem Arbeitsblatt vor sich geht.
• Es macht es im Vergleich zu den Array-Funktionen schneller (merklich in großen Datensätzen).

Versteh mich jetzt nicht falsch. Ich bin nicht gegen Arrayformeln. Ich liebe die erstaunlichen Dinge, die mit Array-Formeln gemacht werden können. Ich hebe sie mir nur für besondere Anlässe auf, wenn alle anderen Optionen nicht helfen.

Um auf die Frage im Punkt zurückzukommen, wird die Hilfsspalte benötigt, um einen eindeutigen Qualifizierer zu erstellen. Dieser eindeutige Qualifizierer kann dann verwendet werden, um den richtigen Wert zu suchen. Zum Beispiel sind drei Matt in den Daten enthalten, aber es gibt nur eine Kombination aus Matt und Unit Test oder Matt und Mid-Term.

Hier sind die Schritte:

• Fügen Sie eine Hilfsspalte zwischen Spalte B und C ein.
• Verwenden Sie die folgende Formel in der Hilfsspalte:=A2&”|”&B2
  • Dadurch würden für jede Instanz eindeutige Qualifizierer erstellt, wie unten gezeigt.
• Verwenden Sie die folgende Formel in G3 =SVERWEIS($F3&”|”&G$2,$C$2:$D$19,2,0)
• Kopieren Sie für alle Zellen.

Wie funktioniert das?

Wir erstellen eindeutige Qualifizierer für jede Instanz eines Namens und der Prüfung. In der hier verwendeten SVERWEIS-Funktion wurde der Nachschlagewert auf $F3&”|”&G$2 geändert, damit beide Nachschlagekriterien kombiniert und als ein einziger Nachschlagewert verwendet werden. Der Nachschlagewert für die SVERWEIS-Funktion in G2 ist beispielsweise Matt|Unit Test. Jetzt wird dieser Nachschlagewert verwendet, um die Punktzahl von C2:D19 zu erhalten.

Erläuterungen:

Es gibt ein paar Fragen, die Ihnen wahrscheinlich in den Sinn kommen, also dachte ich, ich werde versuchen, sie hier zu beantworten:

• Warum habe ich | . verwendet Symbol beim Verbinden der beiden Kriterien? - In einigen außergewöhnlich seltenen (aber möglichen) Bedingungen haben Sie möglicherweise zwei verschiedene Kriterien, die jedoch in Kombination das gleiche Ergebnis liefern. Hier ist ein ganz einfaches Beispiel (verzeihen Sie mir hier meine mangelnde Kreativität):

Beachten Sie, dass, während A2 und A3 unterschiedlich sind und B2 und B3 unterschiedlich sind, die Kombinationen am Ende gleich sind. Aber wenn Sie ein Trennzeichen verwenden, dann wäre sogar die Kombination anders (D2 und D3).

• Warum habe ich die Hilfsspalte zwischen Spalte B und C eingefügt und nicht ganz links? - Es schadet nicht, die Hilfsspalte ganz links einzufügen. In der Tat, wenn Sie nicht mit den Originaldaten harmonieren möchten, sollte dies der richtige Weg sein. Ich habe es getan, da ich dadurch weniger Zellen in der SVERWEIS-Funktion verwende. 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 allen passt. Einige Leute ziehen es vielleicht vor, keine Hilfsspalte zu verwenden, während sie SVERWEIS mit mehreren Kriterien verwenden.

Hier ist also die Nicht-Hilfsspaltenmethode für Sie.

Laden Sie die Beispieldatei herunter

SVERWEIS mit mehreren Kriterien - Verwenden der CHOOSE-Funktion

Die Verwendung von Arrayformeln anstelle von Hilfsspalten spart Platz im Arbeitsblatt, und die Leistung kann ebenso gut sein, wenn sie weniger oft in einer Arbeitsmappe verwendet wird.

Unter Berücksichtigung des gleichen Datensatzes wie oben verwendet, ist hier die Formel, die Ihnen das Ergebnis liefert:

=SVERWEIS($E3&”|”&F$2,CHOOSE({1,2},$A$2:$A$19&”|”&$B$2:$B$19,$C$2:$C$19),2, 0)

Da es sich um eine Matrixformel handelt, verwenden Sie sie mit Strg + Umschalt + Eingabetaste, anstatt nur Eingabetaste.

Wie funktioniert das?

Die Formel verwendet auch das Konzept einer Hilfsspalte. Der Unterschied besteht darin, dass Sie die Hilfsspalte nicht in das Arbeitsblatt einfügen, sondern sie als virtuelle Hilfsdaten betrachten, die Teil der Formel sind.

Lassen Sie mich Ihnen zeigen, was ich mit virtuellen Helferdaten meine.

Wenn ich in der obigen Abbildung den CHOOSE-Teil der Formel auswähle und F9 drücke, wird das Ergebnis angezeigt, das die CHOOSE-Formel ergeben würde.

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

Es ist ein Array, bei dem ein Komma die nächste Zelle in derselben Zeile darstellt und ein Semikolon darstellt, dass sich die folgenden Daten in der nächsten Spalte befinden. Daher erstellt diese Formel 2 Datenspalten – eine Spalte hat die eindeutige Kennung und eine hat die Punktzahl.

Wenn Sie nun die SVERWEIS-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 Beispieldatei herunter

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

Gibt es eine andere Möglichkeit, dies zu tun? Wenn ja, teilen Sie es mir im Kommentarbereich mit.

Die folgenden LOOKUP-Tutorials könnten Ihnen auch gefallen:

• SVERWEIS vs. INDEX/SPIEL
• Erhalten Sie mehrere Lookup-Werte ohne Wiederholung in einer einzelnen Zelle.
• So wird die Groß-/Kleinschreibung von SVERWEIS beachtet.
• Verwenden Sie IFERROR mit SVERWEIS, um #N/A-Fehler zu beseitigen.