Die Excel-Funktion „VERWIRK“
Die SVERWEIS-Funktion (was für „vertikale Suche“ steht) ist eine der nützlichsten Funktionen in Excel und bietet eine schnelle Möglichkeit, einen Wert nachzuschlagen, wenn deine Daten in einer Tabelle mit Zeilen und Spalten angeordnet sind. Die SVERWEIS-Funktion hat vier Argumente (die Werte in der Funktion werden durch Semikolons getrennt), weshalb viele Leute oft denken, SVERWEIS sei kompliziert. Sobald du ein paar Übungsaufgaben mit SVERWEIS ausprobierst, wirst du sehen, wie einfach es eigentlich ist.
Syntax der SVERWEIS-Funktion
Wenn deine Daten in einer Tabelle mit Zeilen und Spalten angeordnet sind, gibt dir die SVERWEIS-Funktion einen Wert in einer Zeile an, sofern du einen anderen, entsprechenden Wert weiter links in derselben Zeile kennst (die SVERWEIS-Funktion sucht nur von links nach rechts). Nehmen wir für das folgende SVERWEIS-Beispiel an, du hast in deiner Excel-Tabelle eine Tabelle mit Spalten für Mitarbeiter-ID, Einstellungsdatum und Mitarbeiternamen, wie in der Abbildung unten gezeigt. Jede Zeile steht für einen anderen Mitarbeiter. Angenommen, du möchtest mit SVERWEIS den Namen des Mitarbeiters mit der ID 1234 abrufen.
=SVERWEIS(lookup_value; lookup_range; column_number; [approximate_match])
- Suchwert: Das ist der Wert, den du kennst und mit dem du den unbekannten Wert finden wirst. In diesem Beispiel ist es die Mitarbeiter-ID, die 1234 lautet.
- Lookup\_range: Das ist die gesamte Tabelle, auch als Nachschlagetabelle oder Tabellenarray bezeichnet, in der wir suchen. Man könnte zum Beispiel A1:C6 angeben. Bei einer langen Liste könnten wir die gesamten Spalten A:C verwenden.
- Spaltennummer: Die Spalten in deiner Suchbereichstabelle sind von links nach rechts nummeriert, beginnend mit 1. Die SVERWEIS-Funktion sucht den Suchwert in Spalte 1 (ganz links) und gibt den Wert in derselben Zeile zurück, jedoch in der Spalte, die durch die Spaltennummer angegeben wird. Die Spalte mit der Mitarbeiter-ID ist 1, die Spalte mit dem Einstellungsdatum ist 2 und die Spalte mit dem Mitarbeiternamen ist 3. Beachte, dass SVERWEIS nur von links (beginnend in Spalte 1) nach rechts sucht. Da wir möchten, dass SVERWEIS den Namen des Mitarbeiters zurückgibt, geben wir 3 als Spaltennummer an.
- Approximate\_match: Dies ist das einzige optionale Argument, allerdings wird empfohlen, es auf „false“ zu setzen (die Standardeinstellung ist „true“), wenn du den Suchwert kennst. In unserem Fall können wir „false“ oder 0 angeben; wir wollen eine exakte Übereinstimmung mit 1234. Der Standardwert ist „true“. Wenn du dieses Feld also leer lässt, sucht SVERWEIS nach ungefähren Übereinstimmungen - was wir in der Regel nicht wollen, außer in Sonderfällen.
Beachte, dass der dir bekannte Wert (das erste Argument) immer in der ersten Spalte (ganz links) des von dir ausgewählten Bereichs stehen muss.
Übungsaufgaben zu SVERWEIS
Angenommen, wir erfassen Mitarbeiternamen und -daten in Excel, aber unsere Spalten sind jetzt in einer anderen Reihenfolge angeordnet: Mitarbeitername, Mitarbeiter-ID und Einstellungsdatum stehen jeweils in der ersten, zweiten und dritten Spalte. Im Folgenden findest du Beispiele für die SVERWEIS-Funktion mit der neuen, neu angeordneten Nachschlagetabelle.
| A | B | C | |
| 1 | Name des Mitarbeiters | Mitarbeiter-ID | Einstellungsdatum |
| 2 | Will Ferrell | 2323 | 1/1/2019 |
| 3 | John C. Reilly | 1020 | 10/8/1992 |
| 4 | Sacha Baron Cohen | 1234 | 12/10/2005 |
| 5 | Amy Adams | 5555 | 5/10/2000 |
| 6 | David Koechner | 5816 | 1/20/2011 |
Verwende SVERWEIS, um die Mitarbeiter-ID von Will zu finden
Der Wert, den wir in der ersten Spalte kennen, ist „Will Ferrell“, und unser Suchbereich kann A:B, A:C oder A1:B6 usw. sein. Der Name des Mitarbeiters steht in Spalte 1 unseres Suchbereichs, und da wir die Mitarbeiter-ID suchen, geben wir als Spaltennummer 2 an. Da wir schließlich genau wissen, wie unser Suchwert (Will Ferrell) geschrieben wird, geben wir für „approximate_match“ den Wert „false“ an.
=SVERWEIS("Will Ferrell"; A:B; 2; falsch)
Diese SVERWEIS-Formel liefert den Wert 2323, der sich in Spalte 2 unseres Tabellenbereichs in derselben Zeile wie der Suchwert befindet.
Verwende SVERWEIS, um das Einstellungsdatum des Mitarbeiters mit der ID 1234 zu ermitteln
Dieses Beispiel ist etwas kniffliger, da die Mitarbeiter-ID statt des Mitarbeiternamens als Suchwert dient. Denk daran, dass der Suchwert in der ersten Spalte unserer Nachschlagetabelle stehen muss, daher müssen wir B:C oder B1:C6 als Suchbereich angeben.
=SVERWEIS(1234; B:C; 2; falsch)
Diese SVERWEIS-Formel durchsucht Spalte B von oben nach unten, bis sie 1234 findet, und sucht dann in Spalte 2 (Spalte C) der Nachschlagetabelle in derselben Zeile nach dem Wert 12.10.2005.
SVERWEIS mit Platzhalter-Suche
In Excel kannst du die SVERWEIS-Funktion mit Platzhaltern verwenden. Beachte, dass dies nur im Modus für exakte Übereinstimmungen funktioniert, daher muss dein viertes Argument (approximate_match) 0 oder FALSE sein. Denk daran, dass du dieses Argument nicht weglassen kannst, da Excel „approximate_match“ standardmäßig auf „true“ setzt. Beachte außerdem, dass dies nur beim Abgleich von Textzeichenfolgen funktioniert.
Was ist die Platzhalter-Suche in Excel?
Die Wildcard-Suche in Excel ermöglicht es, Zeichen zu verwenden, die für beliebige Zeichen in einer Textzeichenfolge stehen. Angenommen, du kennst den Vornamen einer Person, aber nicht ihren Nachnamen. Du könntest diese Person in einer Excel-Tabelle finden, indem du ihren Vornamen mit einem Platzhalterzeichen verknüpfst.
Ein Fragezeichen (?) ersetzt genau ein Zeichen in einer Textzeichenfolge in Excel.
Ein Sternchen (\*) ersetzt eine beliebige Anzahl von Zeichen in einer Textzeichenfolge in Excel.
Um in Excel einen Platzhalter zu verwenden, fügst du ein Sternchen in Anführungszeichen am Anfang oder am Ende (oder an beiden Stellen) deiner Textzeichenfolge ein. Das sieht so aus:
=SVERWEIS("My text"&"*"; A:C; 2; FALSCH)
Die SVERWEIS-Funktion gibt dann den ersten Wert zurück, den sie findet, bei dem der Suchwert ein Text ist, der mit „Mein Text“ beginnt. Wäre unser Suchwert stattdessen "*"&"my text", würde die Funktion nach jedem Text suchen, der mit „mein Text“ endet, da das Sternchen an erster Stelle steht.
| A | B | C | |
| 1 | Name des Mitarbeiters | Mitarbeiter-ID | Einstellungsdatum |
| 2 | Will Ferrell | 2323 | 1/1/2019 |
| 3 | John C. Reilly | 1020 | 10/8/1992 |
| 4 | Sacha Baron Cohen | 1234 | 12/10/2005 |
| 5 | Amy Adams | 5555 | 5/10/2000 |
| 6 | David Koechner | 5816 | 1/20/2011 |
=SVERWEIS("Amy"&"*"; A:B; 2; FALSCH)
Das gibt die ID-Nummer des ersten Mitarbeiters in der Tabelle zurück, dessen Name mit dem Text „Amy“ beginnt.
Nehmen wir nun an, du möchtest die ID des Mitarbeiters finden, dessen Initiale im Mittelnamen „C“ lautet. Du kannst am Anfang und am Ende der Suchzeichenfolge wie folgt Platzhalter verwenden:
=SVERWEIS(""&"C."&""; A:B; 2; FALSCH)
Das gibt die ID des ersten Mitarbeiters zurück, dessen Name „C.“ enthält.
Exakte Übereinstimmung vs. ungefähre Übereinstimmung
Mit Excel kannst du mithilfe von SVERWEIS nach einer exakten Übereinstimmung oder einer ungefähren Übereinstimmung suchen, je nachdem, was das vierte Argument festlegt, das optional ist. SVERWEIS verwendet standardmäßig eine ungefähre Übereinstimmung. Wenn du also das vierte Argument weglässt oder es als TRUE oder 1 angibst, verwendet SVERWEIS eine ungefähre Übereinstimmung.
Wenn du das vierte Argument als 0 oder FALSE angibst, führt SVERWEIS eine exakte Suche durch.
Eine exakte Übereinstimmung ist sinnvoll, wenn du nach einem bestimmten Wert suchst, wie zum Beispiel der Mitarbeiter-ID in den obigen Beispielen. Wenn du nicht nach der exakten ID suchst, bekommst du möglicherweise den falschen Mitarbeiter!
Wenn du dir nicht sicher bist, ob der genaue Wert in der Liste vorkommt, kannst du die „ungefähre Übereinstimmung“ verwenden, um den Wert zu finden, der am besten passt. Wenn Excel eine exakte Übereinstimmung findet, wird dieser Wert verwendet. Wenn keine exakte Übereinstimmung gefunden wird, wird der Wert verwendet, der am nächsten passt.
Wenn die SVERWEIS-Funktion eine ungefähre Übereinstimmung verwendet, greift sie auf eine exakte Übereinstimmung zurück, sobald der exakte Wert verfügbar ist. Ist dies nicht der Fall, wird der nächstkleinere Wert verwendet. Wenn du also in Excel eine ungefähre Übereinstimmung verwendest, musst du sicherstellen, dass die erste Spalte von klein nach groß oder alphabetisch sortiert ist.
SVERWEIS-Fehler
Die SVERWEIS-Funktion in Excel ist fehleranfällig, da sie so viele erforderliche Argumente hat - und wenn auch nur ein Teil der Formel falsch geschrieben ist, führt das zu einem Fehler. Leider sind die Fehlermeldungen, die Excel anzeigt, bei der Fehlerbehebung nicht besonders hilfreich. Lies weiter, um mehr über die verschiedenen Fehlermeldungen zu erfahren, die beim Erstellen einer SVERWEIS-Formel in Excel auftreten können, und wie du sie behebst.
Die SVERWEIS-Funktion gibt den Fehler „#N/A!“ zurück
Die SVERWEIS-Funktion gibt den Fehler #N/A! zurück, wenn sie nach einer exakten Übereinstimmung sucht (approximate_match ist auf „false“ gesetzt) und den lookup_value nicht finden kann. Um diesen Fehler zu beheben, stelle sicher, dass der Suchwert sowohl in der Formel als auch in der Suchtabelle korrekt geschrieben ist, und überprüfe noch einmal den definierten Suchbereich. Stelle sicher, dass sich der Suchwert in der Spalte ganz links im Suchbereich befindet. Stell außerdem sicher, dass der Suchwert sowohl in der Formel als auch in der Suchtabelle das gleiche Format hat; wenn eine Zahl an einer dieser Stellen als Text formatiert ist, erkennt die SVERWEIS-Funktion keine Übereinstimmung.
Die SVERWEIS-Funktion gibt den Fehler \#REF! zurück
Die SVERWEIS-Funktion in Excel gibt den Fehler #REF! zurück, wenn die Formel auf eine Spalte verweist, die nicht im Suchbereich enthalten ist. Um dieses Problem zu beheben, überprüfe den Suchbereich noch einmal und zähle die Anzahl der Spalten, die in der Suchtabelle enthalten sind. Überprüfe anschließend das Argument „column\_number“ (das dritte Argument einer SVERWEIS-Formel) und stelle sicher, dass die Zahl nicht größer ist als die Gesamtzahl der Spalten in der Suchtabelle. Das Argument „column\_number“ kann eine beliebige Zahl zwischen 1 und der Gesamtzahl der Spalten in der Suchtabelle sein.
Die SVERWEIS-Funktion gibt den Fehler „#WERT!“ zurück
Die SVERWEIS-Funktion gibt den Fehler #WERT! zurück, wenn die Argumente „column\_number“ oder „approximate\_match“ ungültige Werte sind. Um dieses Problem zu beheben, überprüfe zunächst das Argument „column\_number“ und stelle sicher, dass es sich um eine Zahl (größer als 0) handelt und kein Text, keine negative Zahl oder etwas anderes ist. Überprüfe anschließend das Argument „approximate_match“ und stelle sicher, dass es entweder weggelassen wird oder einen gültigen booleschen Wert (wahr oder falsch) hat. Es muss entweder das Wort „wahr“ oder „falsch“ sein (ohne Anführungszeichen). Alternativ können in Excel boolesche Werte 1 für „true“ und 0 für „false“ sein. Alle anderen Werte werden als ungültig angesehen.
Die SVERWEIS-Funktion gibt einen Wert zurück, den ich nicht erwartet habe
Gibt die SVERWEIS-Funktion einen Wert zurück, den du nicht erwartest, und du weißt nicht, warum? Das kennen wir alle. Als Erstes solltest du überprüfen, ob das vierte und letzte Argument auf „false“ gesetzt ist - vorausgesetzt, du kennst den genauen Wert von „lookup\_value“. Wenn das Argument „approximate_match“ auf „true“ gesetzt ist oder in der Formel weggelassen wird, wird es standardmäßig auf „true“ gesetzt, und „SVERWEIS“ sucht nach einer ungefähren Übereinstimmung, was zu allerlei unerwarteten Problemen führen kann.
Als Nächstes solltest du prüfen, ob das erste Argument (der Suchwert) in der ersten Spalte der Nachschlagetabelle mehrmals vorkommt. Wenn der „lookup\_value“ mehr als einmal aufgeführt ist, wählt die SVERWEIS-Funktion einfach den ersten Treffer aus, den sie findet - also die Zeile, die in der Suchtabelle am höchsten steht, da SVERWEIS in Excel von oben nach unten sucht. Wenn „SVERWEIS“ also ein unerwartetes Ergebnis liefert, dieses aber aus der richtigen Spalte stammt, stell sicher, dass der „lookup\_value“ nicht weiter oben in der Nachschlagetabelle erneut vorkommt.
Während du nach einer zweiten Instanz des „lookup\_value“ suchst, beachte bitte auch, dass die SVERWEIS-Funktion bei der Übereinstimmung mit dem „lookup\_value“ NICHT zwischen Groß- und Kleinschreibung unterscheidet. Das bedeutet, dass für die SVERWEIS-Funktion in Excel „robert de niro“ dasselbe ist wie „Robert De Niro“ und beide gleich behandelt werden. Wenn du also überprüfst, ob der „lookup\_value“ nur einmal in der Tabelle vorkommt, achte auch auf denselben Wert mit unterschiedlicher Groß- und Kleinschreibung.
Weiter zu den SVERWEIS-Übungsaufgaben! →Verwendete Bilder
- https://excelexercises.com/logo2.png
- https://excelexercises.com/excel-functions/images/vlookupInfographic.png
- https://excelexercises.com/excel-functions/excelImages/logoGreenWhite.png