10 SVERWEIS-Beispiele für Anfänger und Fortgeschrittene

SVERWEIS-Funktion - Einführung

Die SVERWEIS-Funktion ist DER Maßstab.

Sie wissen etwas in Excel, wenn Sie wissen, wie man die SVERWEIS-Funktion verwendet.

Wenn Sie dies nicht tun, sollten Sie Excel besser nicht als eine Ihrer Stärken in Ihrem Lebenslauf aufführen.

Ich war Teil der Panel-Interviews, bei denen, sobald der Kandidat Excel als sein Fachgebiet erwähnte, als erstes nach der SVERWEIS-Funktion gefragt wurde.

Jetzt, wo wir wissen, wie wichtig diese Excel-Funktion ist, macht es Sinn, es voll und ganz zu übertreffen, um stolz sagen zu können: „Ich weiß ein oder zwei Dinge in Excel“.

Dies wird ein massives SVERWEIS-Tutorial (nach meinen Maßstäben).

Ich werde alles Wissenswerte darüber abdecken und Ihnen dann nützliche und praktische SVERWEIS-Beispiele zeigen.

Also anschnallen.

Es ist Zeit für den Abflug.

Wann sollte die SVERWEIS-Funktion in Excel verwendet werden?

Die SVERWEIS-Funktion eignet sich am besten für Situationen, in denen Sie nach einem übereinstimmenden Datenpunkt in einer Spalte suchen, und wenn der übereinstimmende Datenpunkt gefunden wird, gehen Sie in dieser Zeile nach rechts und holen einen Wert aus einer Zelle mit einer angegebenen Anzahl von Spalten nach rechts.

Nehmen wir hier ein einfaches Beispiel, um zu verstehen, wann Vlookup in Excel verwendet wird.

Denken Sie daran, als die Liste mit den Prüfungsergebnissen draußen war und an die Pinnwand geklebt wurde und jeder verrückt wurde, seinen Namen und seine Punktzahl zu finden (zumindest war das früher so, als ich in der Schule war).

So hat es funktioniert:

• Sie gehen zum Schwarzen Brett und suchen nach Ihrem Namen oder Ihrer Matrikelnummer (mit dem Finger in der Liste von oben nach unten).
• Sobald Sie Ihren Namen erkennen, bewegen Sie Ihre Augen rechts neben dem Namen/der Einschreibenummer, um Ihre Punktzahl zu sehen.

Und genau das macht die Excel SVERWEIS-Funktion für Sie (dieses Beispiel können Sie gerne in Ihrem nächsten Interview verwenden).

Die SVERWEIS-Funktion sucht in einer Spalte nach einem angegebenen Wert (im obigen Beispiel war es Ihr Name) und wenn sie die angegebene Übereinstimmung findet, gibt sie einen Wert in derselben Zeile (die von Ihnen erhaltenen Markierungen) zurück.

Syntax

=SVERWEIS(lookup_value, table_array, col_index_num, [range_lookup])

Eingabeargumente

• Lookup-Wert - Dies ist der Suchwert, den Sie in der Spalte ganz links einer Tabelle suchen. Dies kann ein Wert, ein Zellbezug oder eine Textzeichenfolge sein. Im Beispiel des Punkteblatts wäre dies Ihr Name.
• table_array - Dies ist das Tabellenarray, in dem Sie nach dem Wert suchen. Dies kann ein Verweis auf einen Zellbereich oder einen benannten Bereich sein. Im Beispiel des Bewertungsbogens wäre dies die gesamte Tabelle, die die Punktzahl für alle für jedes Fach enthält
• spaltenindex - Dies ist die Spaltenindexnummer, aus der Sie den übereinstimmenden Wert abrufen möchten. Wenn Sie im Beispiel des Bewertungsbogens die Ergebnisse für Mathematik (die erste Spalte in einer Tabelle mit den Ergebnissen) haben möchten, sehen Sie in Spalte 1 nach. Wenn Sie die Ergebnisse für Physik benötigen, sehen Sie in Spalte . nach 2.
• [range_lookup] - hier geben Sie an, ob Sie eine genaue Übereinstimmung oder eine ungefähre Übereinstimmung wünschen. Wenn es weggelassen wird, ist es standardmäßig WAHR – ungefähre Übereinstimmung (siehe zusätzliche Hinweise unten).

Zusätzliche Anmerkungen (langweilig, aber wichtig zu wissen)

• Die Übereinstimmung kann genau (FALSE oder 0 in range_lookup) oder ungefähr (TRUE oder 1) sein.
• Stellen Sie bei der ungefähren Suche sicher, dass die Liste in aufsteigender Reihenfolge (von oben nach unten) sortiert ist, da sonst das Ergebnis ungenau sein könnte.
• Wenn range_lookup TRUE ist (ungefährer Lookup) und die Daten in aufsteigender Reihenfolge sortiert sind:
  • Wenn die SVERWEIS-Funktion den Wert nicht finden kann, gibt sie den größten Wert zurück, der kleiner als der lookup_value ist.
  • Es gibt einen #N/A-Fehler zurück, wenn der lookup_value kleiner als der kleinste Wert ist.
  • Wenn lookup_value Text ist, können Platzhalterzeichen verwendet werden (siehe Beispiel unten).

In der Hoffnung, dass Sie nun ein grundlegendes Verständnis dafür haben, was die SVERWEIS-Funktion kann, schälen wir diese Zwiebel und sehen uns einige praktische Beispiele für die SVERWEIS-Funktion an.

10 Excel SVERWEIS-Beispiele (Basic & Advanced)

Hier sind 10 nützliche Beispiele für die Verwendung von Excel Vlookup, die Ihnen zeigen, wie Sie es in Ihrer täglichen Arbeit verwenden können.

Beispiel 1 – Ermitteln von Brads mathematischem Ergebnis

Im folgenden SVERWEIS-Beispiel habe ich eine Liste mit Schülernamen in der Spalte ganz links und Noten in verschiedenen Fächern in den Spalten B bis E.

Jetzt machen wir uns an die Arbeit und verwenden die SVERWEIS-Funktion für das, was sie am besten kann. Aus den obigen Daten muss ich wissen, wie viel Brad in Mathe erzielt hat.

Aus den obigen Daten muss ich wissen, wie viel Brad in Mathe erzielt hat.

Hier ist die SVERWEIS-Formel, die Brads Mathe-Punktzahl zurückgibt:

=SVERWEIS("Brad",$A$3:$E$10,2,0)

Die obige Formel hat vier Argumente:

• „Brad: - Dies ist der Nachschlagewert.
• $A$3:$E$10 - Dies ist der Zellbereich, in dem wir suchen. Denken Sie daran, dass Excel in der Spalte ganz links nach dem Nachschlagewert sucht. In diesem Beispiel würde es nach dem Namen Brad in A3:A10 suchen (das ist die ganz linke Spalte des angegebenen Arrays).
• 2 - Sobald die Funktion Brads Namen erkennt, geht sie in die zweite Spalte des Arrays und gibt den Wert in derselben Zeile wie den von Brad zurück. Der Wert 2 zeigt hier an, dass wir nach dem Score aus der zweiten Spalte des angegebenen Arrays suchen.
• 0 - Dies weist die SVERWEIS-Funktion an, nur nach genauen Übereinstimmungen zu suchen.

So funktioniert die SVERWEIS-Formel im obigen Beispiel.

Zuerst wird in der Spalte ganz links nach dem Wert Brad gesucht. Es geht von oben nach unten und findet den Wert in Zelle A6.

Sobald es den Wert gefunden hat, geht es nach rechts in die zweite Spalte und holt sich den Wert darin.

Sie können dasselbe Formelkonstrukt verwenden, um die Noten von jedem in einem der Fächer zu erhalten.

Um beispielsweise Marias Noten in Chemie zu finden, verwenden Sie die folgende SVERWEIS-Formel:

=SVERWEIS("Maria",$A$3:$E$10,4,0)

Im obigen Beispiel wird der Nachschlagewert (der Name des Schülers) in doppelten Anführungszeichen eingegeben. Sie können auch einen Zellbezug verwenden, der den Nachschlagewert enthält.

Der Vorteil der Verwendung eines Zellbezugs besteht darin, dass die Formel dynamisch wird.

Wenn Sie beispielsweise eine Zelle mit dem Namen eines Schülers haben und die Punktzahl für Mathematik abrufen, wird das Ergebnis automatisch aktualisiert, wenn Sie den Namen des Schülers ändern (wie unten gezeigt):

Wenn Sie einen Nachschlagewert eingeben, der in der Spalte ganz links nicht gefunden wird, wird ein #N/A-Fehler zurückgegeben.

Beispiel 2 – Zwei-Wege-Suche

Im obigen Beispiel 1 haben wir den Spaltenwert hartcodiert. Daher würde die Formel immer die Punktzahl für Math zurückgeben, da wir 2 als Spaltenindexnummer verwendet haben.

Aber was ist, wenn Sie sowohl den SVERWEIS-Wert als auch die Spaltenindexnummer dynamisch machen möchten. Wie unten gezeigt, können Sie beispielsweise entweder den Schülernamen oder den Fachnamen ändern, und die SVERWEIS-Formel ruft die richtige Punktzahl ab. Dies ist ein Beispiel für eine bidirektionale SVERWEIS-Formel.

Dies ist ein Beispiel für eine bidirektionale SVERWEIS-Funktion.

Um diese bidirektionale Nachschlageformel zu erstellen, müssen Sie auch die Spalte dynamisch machen. Wenn ein Benutzer also das Fach wechselt, wählt die Formel automatisch die richtige Spalte aus (2 bei Mathe, 3 bei Physik, usw.).

Dazu müssen Sie die MATCH-Funktion als Spaltenargument verwenden.

Hier ist die SVERWEIS-Formel, die dies tut:

=SVERWEIS(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

Die obige Formel verwendet MATCH(H3,$A$2:$E$2,0) als Spaltennummer. Die MATCH-Funktion verwendet den Subjektnamen als Nachschlagewert (in H3) und gibt seine Position in A2:E2 zurück. Wenn Sie also Math verwenden, würde es 2 zurückgeben, da Math in B2 gefunden wird (das ist die zweite Zelle im angegebenen Array-Bereich).

Beispiel 3 – Verwenden von Dropdown-Listen als Nachschlagewerte

Im obigen Beispiel müssen wir die Daten manuell eingeben. Das kann zeitaufwändig und fehleranfällig sein, insbesondere wenn Sie eine riesige Liste von Nachschlagewerten haben.

In solchen Fällen empfiehlt es sich, eine Dropdown-Liste mit den Nachschlagewerten zu erstellen (in diesem Fall könnten es Schülernamen und Fächer sein) und dann einfach aus der Liste auswählen.

Basierend auf der Auswahl aktualisiert die Formel das Ergebnis automatisch.

Etwas wie unten gezeigt:

Dies macht eine gute Dashboard-Komponente aus, da Sie einen riesigen Datensatz mit Hunderten von Schülern im Backend haben können, aber der Endbenutzer (sagen wir ein Lehrer) kann schnell die Noten eines Schülers in einem Fach erhalten, indem er einfach die Auswahl trifft das Herunterfallen.

So machen Sie das:

Die in diesem Fall verwendete SVERWEIS-Formel ist dieselbe wie in Beispiel 2.

=SVERWEIS(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

Die Lookup-Werte wurden in Dropdown-Listen umgewandelt.

Hier sind die Schritte zum Erstellen der Dropdown-Liste:

• Wählen Sie die Zelle aus, in der die Dropdown-Liste angezeigt werden soll. In diesem Beispiel in G4 wollen wir die Schülernamen.
• Gehen Sie zu Daten -> Datentools -> Datenvalidierung.
• Wählen Sie im Dialogfeld Datenvalidierung auf der Registerkarte Einstellungen die Option Liste aus der Dropdown-Liste Zulassen aus.
• Wählen Sie in der Quelle $A$3:$A$10
• OK klicken.

Jetzt haben Sie die Dropdown-Liste in Zelle G4. Ebenso können Sie in H3 eine für die Fächer erstellen.

Beispiel 4 – Drei-Wege-Suche

Was ist ein Drei-Wege-Lookup?

In Beispiel 2 haben wir eine Nachschlagetabelle mit Punktzahlen für Schüler in verschiedenen Fächern verwendet. Dies ist ein Beispiel für eine bidirektionale Suche, bei der wir zwei Variablen verwenden, um die Punktzahl abzurufen (Schülername und Name des Probanden).

Angenommen, in einem Jahr hat ein Student drei verschiedene Prüfungsstufen, Unit Test, Midterm und Final Examination (das hatte ich als Student).

Eine Drei-Wege-Suche wäre die Möglichkeit, die Noten eines Schülers für ein bestimmtes Fach aus dem angegebenen Prüfungsniveau zu erhalten.

Etwas wie unten gezeigt:

Im obigen Beispiel kann die SVERWEIS-Funktion in drei verschiedenen Tabellen nachschlagen (Einheitstest, Zwischenprüfung und Abschlussprüfung) und gibt die Punktzahl für den angegebenen Schüler im angegebenen Fach zurück.

Hier ist die Formel, die in Zelle H4 verwendet wird:

=SVERWEIS(G4,CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A $19:$E$23),MATCH(H3,$A$2:$E$2,0),0) 

Diese Formel verwendet die CHOOSE-Funktion, um sicherzustellen, dass auf die richtige Tabelle verwiesen wird. Lassen Sie uns den CHOOSE-Teil der Formel analysieren:

WÄHLEN(WENN(H2=”Einheitstest”,1,IF(H2=”Halbzeit”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23 )

Das erste Argument der Formel ist IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)), das die Zelle H2 überprüft und sieht, auf welche Prüfungsstufe Bezug genommen wird. Wenn es sich um einen Unit-Test handelt, wird $A$3:$E$7 zurückgegeben, was die Punktzahlen für den Unit-Test enthält. Wenn es sich um Midterm handelt, gibt es $A$11:$E$15 zurück, andernfalls gibt es $A$19:$E$23 zurück.

Dadurch wird das SVERWEIS-Tabellen-Array dynamisch und somit eine Drei-Wege-Suche.

Beispiel 5 – Abrufen des letzten Werts aus einer Liste

Sie können eine SVERWEIS-Formel erstellen, um den letzten numerischen Wert aus einer Liste zu erhalten.

Die größte positive Zahl, die Sie in Excel verwenden können, ist 9.99999999999999E+307. Dies bedeutet auch, dass die größte Nachschlagenummer in der SVERWEIS-Nummer gleich ist.

Ich glaube nicht, dass Sie jemals eine Berechnung mit einer so großen Zahl benötigen würden. Und genau das können wir verwenden, um die letzte Zahl in einer Liste zu erhalten.

Angenommen, Sie haben einen Datensatz (in A1:A14) wie unten gezeigt und Sie möchten die letzte Nummer in der Liste abrufen.

Hier ist die Formel, die Sie verwenden können:

=SVERWEIS(9.99999999999999E+307,$A$1:$A$14,STIMMT)

Beachten Sie, dass die obige Formel eine ungefähre Übereinstimmung verwendet SVERWEIS (beachten Sie TRUE am Ende der Formel anstelle von FALSE oder 0). Beachten Sie auch, dass die Liste nicht sortiert werden muss, damit diese SVERWEIS-Formel funktioniert.

So funktioniert die ungefähre SVERWEIS-Funktion. Es scannt die Spalte ganz links von oben nach unten.

• Wenn es eine genaue Übereinstimmung findet, gibt es diesen Wert zurück.
• Wenn ein Wert gefunden wird, der höher als der Nachschlagewert ist, wird der Wert in der darüber liegenden Zelle zurückgegeben.
• Wenn der Nachschlagewert größer als alle Werte in der Liste ist, wird der letzte Wert zurückgegeben.

Im obigen Beispiel ist das dritte Szenario am Werk.

Seit 9.99999999999999E+307 ist die größte Zahl, die in Excel verwendet werden kann. Wenn dies als Suchwert verwendet wird, wird die letzte Zahl aus der Liste zurückgegeben.

Auf die gleiche Weise können Sie es auch verwenden, um das letzte Textelement aus der Liste zurückzugeben. Hier ist die Formel, die das tun kann:

=SVERWEIS("zzz",$A$1:$A$8,1,STIMMT)

Es folgt die gleiche Logik. Excel durchsucht alle Namen, und da zzz als größer angesehen wird als jeder Name/Text, der mit Alphabeten vor zzz beginnt, wird das letzte Element aus der Liste zurückgegeben.

Beispiel 6 – Partielle Suche mit Platzhalterzeichen und SVERWEIS

Excel-Platzhalterzeichen können in vielen Situationen sehr hilfreich sein.

Es ist dieser Zaubertrank, der Ihren Formeln Superkräfte verleiht.

Eine teilweise Suche ist erforderlich, wenn Sie nach einem Wert in einer Liste suchen müssen und es keine genaue Übereinstimmung gibt.

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten in einer Liste nach der Firma ABC suchen, aber die Liste enthält ABC Ltd anstelle von ABC.

Sie können ABC nicht als Suchwert verwenden, da es in Spalte A keine genaue Übereinstimmung gibt. Eine ungefähre Übereinstimmung führt ebenfalls zu fehlerhaften Ergebnissen und erfordert eine aufsteigende Sortierung der Liste.

Sie können jedoch innerhalb der SVERWEIS-Funktion ein Platzhalterzeichen verwenden, um die Übereinstimmung abzurufen.

Geben Sie in Zelle D2 die folgende Formel ein und ziehen Sie sie in die anderen Zellen:

=SVERWEIS("*"&C2&"*",$A$2:$A$8,1,FALSE)

Wie funktioniert diese Formel?

In der obigen Formel wird der Lookup-Wert nicht unverändert verwendet, sondern auf beiden Seiten mit dem Platzhalterzeichen Stern (*) flankiert - „*“&C2&“*“

Ein Sternchen ist ein Platzhalterzeichen in Excel und kann eine beliebige Anzahl von Zeichen darstellen.

Die Verwendung des Sternchens auf beiden Seiten des Nachschlagewerts teilt Excel mit, dass es nach Texten suchen muss, die das Wort in C2 enthalten. Es kann eine beliebige Anzahl von Zeichen vor oder nach dem Text in C2 enthalten.

Zelle C2 hat beispielsweise ABC, sodass die SVERWEIS-Funktion die Namen in A2:A8 durchsucht und nach ABC sucht. Es findet eine Übereinstimmung in Zelle A2, da es ABC in ABC Ltd. enthält. Es spielt keine Rolle, ob sich links oder rechts von ABC Zeichen befinden. Bis ABC in einer Textzeichenfolge vorhanden ist, wird sie als Übereinstimmung betrachtet.

Hinweis: Die SVERWEIS-Funktion gibt immer den ersten übereinstimmenden Wert zurück und hört auf, weiter zu suchen. Also wenn du ABC hast GmbH., und ABC Corporation in einer Liste, gibt es die erste zurück und ignoriert den Rest.

Beispiel 7 – SVERWEIS gibt trotz Übereinstimmung im Suchwert einen Fehler zurück

Es kann Sie in den Wahnsinn treiben, wenn Sie sehen, dass es einen passenden Lookup-Wert gibt und die SVERWEIS-Funktion einen Fehler zurückgibt.

Im folgenden Fall gibt es beispielsweise eine Übereinstimmung (Matt), aber die SVERWEIS-Funktion gibt immer noch einen Fehler zurück.

Während wir jetzt sehen können, dass eine Übereinstimmung vorliegt, können wir mit bloßem Auge nicht sehen, dass es führende oder nachfolgende Leerzeichen geben könnte. Wenn Sie diese zusätzlichen Leerzeichen vor, nach oder zwischen den Nachschlagewerten haben, ist dies KEINE genaue Übereinstimmung.

Dies ist häufig der Fall, wenn Sie Daten aus einer Datenbank importieren oder von einer anderen Person beziehen. Diese führenden/nachfolgenden Leerzeichen neigen dazu, sich einzuschleichen.

Die Lösung ist hier die TRIM-Funktion. Es entfernt alle führenden oder nachgestellten Leerzeichen oder zusätzliche Leerzeichen zwischen Wörtern.

Hier ist die Formel, die Ihnen das richtige Ergebnis liefert.

=SVERWEIS("Matt",TRIM($A$2:$A$9),1,0)

Da es sich um eine Matrixformel handelt, verwenden Sie Control + Shift + Enter anstelle von nur Enter.

Eine andere Möglichkeit könnte darin bestehen, Ihr Lookup-Array zuerst mit der TRIM-Funktion zu behandeln, um sicherzustellen, dass alle zusätzlichen Leerzeichen verschwunden sind, und dann die SVERWEIS-Funktion wie gewohnt zu verwenden.

Beispiel 8 – Durchführen einer Suche nach Groß-/Kleinschreibung

Standardmäßig unterscheidet der Nachschlagewert in der SVERWEIS-Funktion die Groß-/Kleinschreibung nicht. Wenn Ihr Lookup-Wert beispielsweise MATT, matt oder Matt ist, ist dies für die SVERWEIS-Funktion gleich. Es wird unabhängig von der Groß-/Kleinschreibung der erste übereinstimmende Wert zurückgegeben.

Wenn Sie jedoch eine Groß-/Kleinschreibung beachten möchten, müssen Sie die EXACT-Funktion zusammen mit der SVERWEIS-Funktion verwenden.

Hier ist ein Beispiel:

Wie Sie sehen können, gibt es drei Zellen mit demselben Namen (in A2, A4 und A5), aber mit einer anderen Groß-/Kleinschreibung. Auf der rechten Seite haben wir die drei Namen (Matt, MATT und matt) zusammen mit ihren Ergebnissen in Math.

Jetzt ist die SVERWEIS-Funktion nicht für die Verarbeitung von Nachschlagewerten mit Berücksichtigung der Groß-/Kleinschreibung ausgelegt. In diesem obigen Beispiel würde es immer 38 zurückgeben, was die Punktzahl für Matt in A2 ist.

Um die Groß-/Kleinschreibung zu beachten, müssen wir eine Hilfsspalte verwenden (wie unten gezeigt):

Um die Werte in der Hilfsspalte abzurufen, verwenden Sie die Funktion =ROW(). Es wird einfach die Zeilennummer in der Zelle erhalten.

Sobald Sie die Hilfsspalte haben, ist hier die Formel, die das Suchergebnis mit Berücksichtigung der Groß-/Kleinschreibung liefert.

=SVERWEIS(MAX(GENAU(E2,$A$2:$A$9)*(ZEILE($A$2:$A$9))),$B$2:$C$9,2,0)

Lassen Sie uns nun aufschlüsseln und verstehen, was dies bewirkt:

• EXACT(E2,$A$2:$A$9) - Dieser Teil vergleicht den Nachschlagewert in E2 mit allen Werten in A2:A9. Es gibt ein Array von TRUEs/FALSEs zurück, wobei TRUE zurückgegeben wird, wenn eine genaue Übereinstimmung vorliegt. In diesem Fall würde es das folgende Array zurückgeben: {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
• EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9) - Dieser Teil multipliziert das Array von WAHR/FALSCH mit der Zeilennummer. Wo immer ein WAHR steht, ergibt es die Zeilennummer , sonst gibt es 0. In diesem Fall würde es {2;0;0;0;0;0;0;0} zurückgeben.
• MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) - Dieser Teil gibt den Maximalwert aus dem Zahlenarray zurück. In diesem Fall würde es 2 zurückgeben (das ist die Zeilennummer, bei der es eine genaue Übereinstimmung gibt).
• Jetzt verwenden wir einfach diese Zahl als Lookup-Wert und verwenden das Lookup-Array als B2:C9

Hinweis: Da es sich um eine Matrixformel handelt, verwenden Sie Strg + Umschalt + Eingabetaste, anstatt nur Eingabe zu machen.

Beispiel 9 – Verwendung von SVERWEIS 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, SVERWEIS in Excel mit mehreren Kriterien zu verwenden.

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.

Wenn Sie beispielsweise versuchen, SVERWEIS mit Matt als Nachschlagewert zu verwenden, wird immer 91 zurückgegeben, was die Punktzahl für das erste Vorkommen von Matt in der Liste ist. Um die Punktzahl für Matt für jeden Prüfungstyp (Einheitstest, Zwischenprüfung und Abschluss) zu erhalten, müssen Sie einen eindeutigen Nachschlagewert erstellen.

Dies kann über die Hilfsspalte erfolgen. Der erste Schritt besteht darin, links neben den Partituren eine Hilfsspalte einzufügen.

Um nun für jede Instanz des Namens einen eindeutigen Qualifizierer zu erstellen, verwenden Sie die folgende Formel in C2: =A2&”|”&B2

Kopieren Sie diese Formel in alle Zellen in der Hilfsspalte. Dadurch werden eindeutige Lookup-Werte für jede Instanz eines Namens erstellt (wie unten gezeigt):

Nun, während es Wiederholungen der Namen gab, gibt es keine Wiederholung, wenn der Name mit der Prüfungsstufe kombiniert wird.

Dies macht es einfach, da Sie jetzt die Werte der Hilfsspalte als Nachschlagewerte verwenden können.

Hier ist die Formel, die Ihnen das Ergebnis in G3:I8 liefert.

=SVERWEIS($F3&"|"&G$2,$C$2:$D$19,2,0)

Hier haben wir den Studentennamen und die Prüfungsstufe kombiniert, um den Nachschlagewert zu erhalten, und wir verwenden diesen Nachschlagewert und überprüfen ihn in der Hilfsspalte, um den passenden Datensatz zu erhalten.

Hinweis: Im obigen Beispiel haben wir | . verwendet als Trennzeichen beim Zusammenfügen von Text in der Hilfsspalte. In einigen außergewöhnlich seltenen (aber möglichen) Bedingungen haben Sie möglicherweise zwei Kriterien, die sich unterscheiden, aber am Ende das gleiche Ergebnis liefern, wenn sie kombiniert werden. Hier ist ein Beispiel:

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).

Hier ist ein Tutorial zur Verwendung von SVERWEIS mit mehreren Kriterien, ohne Hilfsspalten zu verwenden. Hier können Sie sich auch mein Video-Tutorial ansehen.

Beispiel 10 – Behandlung von Fehlern während der Verwendung der SVERWEIS-Funktion

Die Excel SVERWEIS-Funktion gibt einen Fehler zurück, wenn der angegebene Nachschlagewert nicht gefunden werden kann. Möglicherweise möchten Sie nicht, dass der hässliche Fehlerwert die Ästhetik Ihrer Daten stört, falls SVERWEIS keinen Wert finden kann.

Sie können die Fehlerwerte mit einem bedeutungsvollen Volltext wie „Nicht verfügbar“ oder „Nicht gefunden“ leicht entfernen.

Wenn Sie beispielsweise im Beispiel unten versuchen, die Punktzahl von Brad in der Liste zu finden, wird ein Fehler zurückgegeben, da Brads Name nicht in der Liste enthalten ist.

Um diesen Fehler zu entfernen und durch etwas Sinnvolles zu ersetzen, wickeln Sie Ihre SVERWEIS-Funktion in die IFERROR-Funktion ein.

Hier ist die Formel:

=IFERROR(SVERWEIS(D2,$A$2:$B$7,2,0),"Nicht gefunden")

Die IFERROR-Funktion prüft, ob der vom ersten Argument zurückgegebene Wert (in diesem Fall die SVERWEIS-Funktion) ein Fehler ist oder nicht. Wenn es sich nicht um einen Fehler handelt, wird der Wert von der SVERWEIS-Funktion zurückgegeben, andernfalls wird nicht gefunden zurückgegeben.

Die IFERROR-Funktion ist ab Excel 2007 verfügbar. Wenn Sie ältere Versionen verwenden, verwenden Sie die folgende Funktion:

=IF(ISERROR(SVERWEIS(D2,$A$2:$B$7,2,0)),"Nicht gefunden",SVERWEIS(D2,$A$2:$B$7,2,0))

Siehe auch: Umgang mit SVERWEIS-Fehlern in Excel.

Das ist es in diesem SVERWEIS-Tutorial.

Ich habe versucht, wichtige Beispiele für die Verwendung der Vlookup-Funktion in Excel zu behandeln. Wenn Sie weitere Beispiele zu dieser Liste hinzufügen möchten, lassen Sie es mich im Kommentarbereich wissen.

Hinweis: Ich habe mein Bestes versucht, dieses Tutorial Korrektur zu lesen, aber falls Sie Fehler oder Rechtschreibfehler finden, lassen Sie es mich bitte wissen 🙂

Verwenden von SVERWEIS-Funktion in Excel - Video

• Excel HLOOKUP-Funktion.
• Excel XLOOKUP-Funktion
• Excel-INDEX-Funktion.
• Excel INDIRECT-Funktion.
• Excel MATCH-Funktion.
• Excel OFFSET-Funktion.