Tipp:Verwenden Sie die neuen Funktionen XVERWEIS und XMATCH, verbesserte Versionen der in diesem Artikel beschriebenen Funktionen. Diese neuen Funktionen funktionieren in jede Richtung und geben standardmäßig genaue Übereinstimmungen zurück, wodurch sie einfacher und bequemer zu verwenden sind als ihre Vorgänger.
Angenommen, Sie haben eine Liste der Bürostandortnummern, und Sie müssen wissen, welche Mitarbeiter sich in den einzelnen Büros befinden. Die Kalkulationstabelle ist riesig, daher denken Sie vielleicht, dass es eine anspruchsvolle Aufgabe ist.Mit einer Nachschlagefunktion lässt sich dies ganz einfach machen.
Die Funktionen SVERWEIS und SVERWEIS sind zusammen mit INDEX und MATCHeinige der nützlichsten Funktionen in Excel.
Hinweis:Das Feature "Nachschlage-Assistent" ist in Excel nicht mehr verfügbar.
Hier ist ein Beispiel für die Verwendung von SVERWEIS.
=SVERWEIS(B2;C2:E7;3;WAHR)
In diesem Beispiel ist B2 das ersteArgument – ein Datenelement, das die Funktion zum Arbeiten benötigt. Für SVERWEIS ist dieses erste Argument der Wert, den Sie suchen möchten. Dieses Argument kann ein Zellbezug oder ein fester Wert wie "smith" oder 21.000 sein. Das zweite Argument ist der Zellbereich C2-:E7, in dem Nach dem wert gesucht werden soll. Das dritte Argument ist die Spalte in diesem Zellbereich, die den gesuchten Wert enthält.
Das vierte Argument ist optional. Geben Sie entweder WAHR oder FALSCH ein. Wenn Sie WAHR eingeben oder das Argument nicht angeben, gibt die Funktion eine ungefähre Übereinstimmung des Werts zurück, den Sie im ersten Argument angegeben haben. Wenn Sie FALSCH eingeben, wird die Funktion mit dem Wert übereinstimmen, der durch das erste Argument zur Verfügung stellt. Mit anderen Worten: Wenn Sie das vierte Argument leer lassen oder WAHR eingeben, erhalten Sie mehr Flexibilität.
In diesem Beispiel wird veranschaulicht, wie die Funktion ausgeführt wird. Wenn Sie einen Wert in Zelle B2 (das erste Argument) eingeben, durchsucht SVERWEIS die Zellen im Bereich C2:E7 (2nd-Argument) und gibt die nächste ungefähre Übereinstimmung aus der dritten Spalte im Bereich zurück, Spalte E (drittes Argument).
Das vierte Argument ist leer, daher gibt die Funktion eine ungefähre Übereinstimmung zurück. Wäre für dieses Argument der Wert FALSCH angegeben, müssten Sie einen der Werte aus der Spalte C oder D angeben, um überhaupt ein Ergebnis zu erhalten.
Wenn Sie sich mit SVERWEIS gut aus sind, ist die Funktion SVERWEIS ebenso einfach zu verwenden. Sie geben dieselben Argumente ein, suchen jedoch in Zeilen statt in Spalten.
Verwenden von INDEX und MATCH anstelle von SVERWEIS
Es gibt bestimmte Einschränkungen bei der Verwendung von SVERWEIS– die Funktion SVERWEIS kann nur einen Wert von links nach rechts nachschauen. Dies bedeutet, dass sich die Spalte, die den nach oben suchenden Wert enthält, immer links neben der Spalte befinden sollte, die den Rückgabewert enthält. Wenn Ihre Kalkulationstabelle nicht auf diese Weise erstellt wurde, verwenden Sie SVERWEIS nicht. Verwenden Sie stattdessen die Kombination aus index- und MATCH-Funktionen.
Dieses Beispiel zeigt eine kleine Liste, in der sich der Wert, nach dem wir suchen möchten, Chicago, nicht in der Spalte ganz links befindet. Daher können wir SVERWEIS nicht verwenden. Stattdessen verwenden wir die Funktion VERGLEICHEN, um Chicago im Bereich B1:B11 zu finden. Sie wird in Zeile 4 gefunden. Anschließend verwendet INDEX diesen Wert als Nachschlageargument und findet die Grundgesamtheit für Chicago in der 4. Spalte (Spalte D). Die verwendete Formel wird in Zelle A14 angezeigt.
Weitere Beispiele für die Verwendung von INDEX und MATCH anstelle von SVERWEIS finden Sie im Artikel https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ von Bill Jelen, Microsoft MVP.
Kleiner Test gefällig?
Wenn Sie mit Nachschlagefunktionen experimentieren möchten, bevor Sie sie mit Ihren eigenen Daten ausprobieren, finden Sie hier einige Beispieldaten.
SVERWEIS Beispiel bei der Arbeit
Kopieren Sie die folgenden Daten in eine leere Kalkulationstabelle.
Tipp:Legen Sie vor dem Einfügen der Daten in Excel die Spaltenbreiten für die Spalten A bis C auf 250 Pixel fest, und klicken Sie auf Textumbruch(RegisterkarteStart, Gruppe Ausrichtung).
| Dichte | Viskosität | Temperatur |
| 0,457 | 3,55 | 500 |
| 0,525 | 3,25 | 400 |
| 0,606 | 2,93 | 300 |
| 0,675 | 2,75 | 250 |
| 0,746 | 2,57 | 200 |
| 0,835 | 2,38 | 150 |
| 0,946 | 2,17 | 100 |
| 1,09 | 1,95 | 50 |
| 1,29 | 1,71 | |
| Formel | Beschreibung | Ergebnis |
| =SVERWEIS(1;A2:C10;2) | Bei Verwendung einer ungefähren Entsprechung wird bei einer Suche nach dem Wert 1 in SpalteA nach dem größten Wert in SpalteA, der kleiner oder gleich 1 ist, gesucht (0,946). Anschließend wird der Wert in derselben Zeile in SpalteB zurückgegeben. | 2,17 |
| =SVERWEIS(1;A2:C10;3;WAHR) | Bei Verwendung einer ungefähren Entsprechung wird bei einer Suche nach dem Wert 1 in SpalteA nach dem größten Wert in Spalte A, der kleiner oder gleich 1 ist, gesucht (0,946). Anschließend wird der Wert in derselben Zeile in SpalteC zurückgegeben. | 100 |
| =SVERWEIS(0,7;A2:C10;3;FALSCH) | Bei Verwendung einer genauen Entsprechung wird in Spalte A nach dem Wert 0,7 gesucht. Da es keine genaue Entsprechung in Spalte A gibt, wird ein Fehler zurückgegeben. | #NV |
| =SVERWEIS(0,1;A2:C10;2;WAHR) | Bei Verwendung einer ungefähren Entsprechung wird in Spalte A nach dem Wert 0,1 gesucht. Da 0,1 kleiner als der kleinste Wert in Spalte A ist, wird ein Fehler zurückgegeben. | #NV |
| =SVERWEIS(2;A2:C10;2;WAHR) | Bei Verwendung einer ungefähren Entsprechung wird bei einer Suche nach dem Wert 2 in SpalteA nach dem größten Wert in Spalte A, der kleiner oder gleich 2 ist, gesucht (1,29). Anschließend wird der Wert in derselben Zeile in SpalteB zurückgegeben. | 1,71 |
Beispiel für SVERWEIS
Kopieren Sie alle Zellen in dieser Tabelle, und fügen Sie sie in Zelle A1 eines leeren Arbeitsblatts in Excel ein.
Tipp:Legen Sie vor dem Einfügen der Daten in Excel die Spaltenbreiten für die Spalten A bis C auf 250 Pixel fest, und klicken Sie auf Textumbruch(RegisterkarteStart, Gruppe Ausrichtung).
| Achsen | Getriebe | Schrauben |
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| Formel | Beschreibung | Ergebnis |
| =WVERWEIS("Achsen";A1:C4;2;WAHR) | Sucht "Achsen" in Zeile 1 und gibt den Wert aus Zeile 2 zurück, der in derselben Spalte (Spalte A) steht. | 4 |
| =WVERWEIS("Getriebe";A1:C4;3;FALSCH) | Sucht "Getriebe" in Zeile 1 und gibt den Wert aus Zeile 3 zurück, der in derselben Spalte (Spalte B) steht. | 7 |
| =WVERWEIS("B";A1:C4;3;WAHR) | Sucht "B" in Zeile 1 und gibt den Wert aus Zeile 3 zurück, der in derselben Spalte steht. Da für "B" keine genaue Entsprechung gefunden wird, wird der höchste Wert in Zeile 1 verwendet, der kleiner ist als "B": "Achsen" in Spalte A. | 5 |
| =WVERWEIS("Schrauben";A1:C4;4) | Sucht "Schrauben" in Zeile 1 und gibt den Wert aus Zeile 4 zurück, der in derselben Spalte (Spalte C) steht. | 11 |
| =WVERWEIS(3;{1.2.3;"a"."b"."c";"d"."e"."f"};2;WAHR) | Sucht die Zahl 3 in der dreizeiligen Matrixkonstanten und gibt den Wert aus Zeile 2 in derselben Spalte (in diesem Fall der dritten Spalte) zurück. Die Matrixkonstante umfasst drei Zeilen, die jeweils durch ein Semikolon (;) getrennt sind. Da "c" in Zeile 2 und in derselben Spalte wie 3 gefunden wird, wird "c" zurückgegeben. | c |
INDEX- und MATCH-Beispiele
In diesem letzten Beispiel werden die Funktionen INDEX und ÜBEREINSTIMMUNG zusammen verwendet, um die früheste Rechnungsnummer und das entsprechende Datum für jede von fünf Städten zurück zu geben. Da das Datum als Zahl zurückgegeben wird, verwenden wir die Funktion TEXT, um es als Datum zu formatieren. Die Funktion INDEX verwendet das Ergebnis der Funktion VERGLEICH als Argument. Die Kombination der Funktionen INDEX und VERGLEICH wird zweimal in jeder Formel verwendet – zuerst, um die Rechnungsnummer zurückzugeben, und dann zum Zurückgeben des Datums.
Kopieren Sie alle Zellen in dieser Tabelle, und fügen Sie sie in Zelle A1 eines leeren Arbeitsblatts in Excel ein.
Tipp:Legen Sie vor dem Einfügen der Daten in Excel die Spaltenbreiten für die Spalten A bis D auf 250 Pixel fest, und klicken Sie auf Textumbruch(RegisterkarteStart, Gruppe Ausrichtung).
| Rechnung | Ort | Rechnungsdatum | Früheste Rechnung nach Ort mit Datum |
| 3115 | Aachen | 7.4.12 | ="Aachen = "&INDEX($A$2:$C$33;VERGLEICH("Aachen";$B$2:$B$33;0);1)& ", Rechnungsdatum: " & TEXT(INDEX($A$2:$C$33;VERGLEICH("Aachen";$B$2:$B$33;0);3);"t.m.jj") |
| 3137 | Aachen | 9.4.12 | ="Augsburg = "&INDEX($A$2:$C$33;VERGLEICH("Augsburg";$B$2:$B$33;0);1)& ", Rechnungsdatum: " & TEXT(INDEX($A$2:$C$33;VERGLEICH("Augsburg";$B$2:$B$33;0);3);"t.m.jj") |
| 3154 | Aachen | 11.4.12 | ="Darmstadt = "&INDEX($A$2:$C$33;VERGLEICH("Darmstadt";$B$2:$B$33;0);1)& ", Rechnungsdatum: " & TEXT(INDEX($A$2:$C$33;VERGLEICH("Darmstadt";$B$2:$B$33;0);3);"t.m.jj") |
| 3191 | Aachen | 21.4.12 | ="Nürnberg = "&INDEX($A$2:$C$33;VERGLEICH("Nürnberg";$B$2:$B$33;0);1)& ", Rechnungsdatum: " & TEXT(INDEX($A$2:$C$33;VERGLEICH("Nürnberg";$B$2:$B$33;0);3);"t.m.jj") |
| 3293 | Aachen | 25.4.12 | ="Essen = "&INDEX($A$2:$C$33;VERGLEICH("Essen";$B$2:$B$33;0);1)& ", Rechnungsdatum: " & TEXT(INDEX($A$2:$C$33;VERGLEICH("Essen";$B$2:$B$33;0);3);"t.m.jj") |
| 3331 | Aachen | 27.4.12 | |
| 3350 | Aachen | 28.4.12 | |
| 3390 | Aachen | 1.5.12 | |
| 3441 | Aachen | 2.5.12 | |
| 3517 | Aachen | 8.5.12 | |
| 3124 | Augsburg | 9.4.12 | |
| 3155 | Augsburg | 11.4.12 | |
| 3177 | Augsburg | 19.4.12 | |
| 3357 | Augsburg | 28.4.12 | |
| 3492 | Augsburg | 6.5.12 | |
| 3316 | Darmstadt | 25.4.12 | |
| 3346 | Darmstadt | 28.4.12 | |
| 3372 | Darmstadt | 1.5.12 | |
| 3414 | Darmstadt | 1.5.12 | |
| 3451 | Darmstadt | 2.5.12 | |
| 3467 | Darmstadt | 2.5.12 | |
| 3474 | Darmstadt | 4.5.12 | |
| 3490 | Darmstadt | 5.5.12 | |
| 3503 | Darmstadt | 8.5.12 | |
| 3151 | Nürnberg | 9.4.12 | |
| 3438 | Nürnberg | 2.5.12 | |
| 3471 | Nürnberg | 4.5.12 | |
| 3160 | Essen | 18.4.12 | |
| 3328 | Essen | 26.4.12 | |
| 3368 | Essen | 29.4.12 | |
| 3420 | Essen | 1.5.12 | |
| 3501 | Essen | 6.5.12 |
Kurzübersichtskarte: SVERWEIS-Aktualisierungs-
(Referenz)
table_array in einer SVERWEIS-Funktion.
FAQs
Nachschlagen von Werten mit SVERWEIS, INDEX oder VERGLEICH? ›
Mit der Kombination aus INDEX und VERGLEICH kannst du im Gegensatz zu SVERWEIS nicht nur nach rechts, sondern auch nach links suchen. Ein weiterer Vorteil ist, dass du auch ganz flexibel die Formel weiterziehen kannst. Nur die Spaltenbezeichnung muss übereinstimmen.
Was ist schneller Sverweis oder Index? ›Es gibt keinerlei Geschwindigkeitsunterschiede.
Warum wir Sverweis in Excel nicht verwenden sollten? ›SVERWEIS — Der Excel-Hammer
VLOOKUP), denn der exzessive Gebrauch von SVERWEIS ist (fast) immer ein Zeichen dafür, dass Excel für Dinge eingesetzt wird, für die es bessere Werkzeuge gibt, dass Excel als Datenbank missbraucht wird oder, dass der Nutzer immer noch in einer “alten Excel-Welt” gefangen ist.
Die INDEX-Funktion gibt einen Wert oder den Bezug auf einen Wert aus einer Tabelle oder einem Bereich zurück. Die INDEX-Funktion kann auf zwei Arten verwendet werden: Informationen zum Zurückgeben des Werts einer angegebenen Zelle oder Matrix von Zellen finden Sie unter Matrixversion.
Was ist der Unterschied zwischen Verweis und Sverweis? ›Der Unterschied besteht darin, dass WVERWEIS die erste Zeile, SVERWEIS die erste Spalte und VERWEIS diejenige Spalte oder Zeile der Matrix mit dem Wert von Suchkriterium abgleicht, die sich entsprechend den Dimensionen der Matrix ergibt.
Welche Formel statt Sverweis? ›Verwenden von INDEX und MATCH anstelle von SVERWEIS
Verwenden Sie stattdessen die Kombination aus index- und MATCH-Funktionen. Dieses Beispiel zeigt eine kleine Liste, in der sich der Wert, nach dem wir suchen möchten, Chicago, nicht in der Spalte ganz links befindet. Daher können wir SVERWEIS nicht verwenden.
Als Alternative zur SVERWEIS-Funktion können Sie die INDEX-Funktion und die VERLGEICHS-Funktion kombinieren. Mit SVERWEIS können Sie in Excel Listen nach bestimmten Daten durchsuchen. Allerdings müssen Sie in vielen Fällen Ihre Liste zunächst kopieren oder neu anordnen, um die Funktion zu nutzen.
Wann Sverweis und wann Xverweis? ›XVERWEIS und SVERWEIS im Vergleich
Im Gegensatz zum SVERWEIS können Sie mit der Funktion XVERWEIS den Suchbegriff in jeder Spalte der Tabelle suchen. Beim SVERWEIS wird nur die erste Spalte einer Tabelle durchsucht.
Der Spaltenindex gibt an welche Spalte des Suchbereiches ausgelesen werden soll. Hier wird einfach nur eine Zahl erwartet. Mit den Kennzeichen Sortierung kann angeben ob die erste Spalte des Suchbereiches, also die Spalte in der das Suchkriterium nachgeschlagen werden soll, sortiert ist oder nicht.
Wann falsch bei Sverweis? ›Wenn Bereich_Verweis als WAHR angegeben ist und der Wert im Suchkriterium kleiner als der kleinste Wert in der ersten Spalte der Matrix ist, wird der Fehlerwert "#NV" zurückgegeben. Wenn Bereich_Verweis FALSCH ist, zeigt der Fehlerwert "#N/A" an, dass kein exakter Wert gefunden wurde.
Was gibt der Index an? ›
Ein Index zeigt die Entwicklung einer Zahl über die Zeit hinweg. Er wird nicht ausgedrückt in Kilos, Euro oder einer ähnlich gängigen Einheit, denn er spiegelt lediglich die Veränderung einer Zahl von einem Zeitpunkt zu einem anderen wider.
Wie kann ich den Index berechnen? ›Der Gesamtwert des Index wird berechnet, indem die Börsenwerte jeder Aktie addiert werden und diese Summe durch die Gesamtzahl der Aktien geteilt wird.
Wie erstellt man einen Index Excel? ›Wie erstellt man einen INDEX Excel? Man schreibt in die Klammer von INDEX den Bereich und danach die Koordinaten der benötigten Zelle. Beispiel =INDEX(Bereich; Zeile; Spalte) und mit Zahlen =INDEX(A1:E5;3;4) oder =INDEX(Bezug; Zeile; Spalte; Bereich) und =INDEX((A1:E5;G1:K5);5;2;2).
Warum braucht man Sverweis? ›Die Funktion SVERWEIS wird immer dann verwendet, wenn Sie mehrere Excel-Tabellen miteinander vergleichen und/oder zusammenfassen wollen. Das S steht hierbei für Senkrecht, denn Excel durchsucht mit dieser Funktion die erste senkrechte Spalte beider Tabellen nach dem angegebenen Zielwert.
Ist der Sverweis schwer? ›Zu den Formeln SVERWEIS() und WVERWEIS() gibt es viele Anleitungen. Manche sind aber so kompliziert und abstrakt, dass sie für die Excel-Anwender oft eher abschreckend wirken. Das ist schade. Denn im Grunde sind beide Formeln simpel und Sie brauchen keine Angst vor ihnen zu haben!
Warum NV bei Sverweis? ›Die SVERWEIS-Funktion gibt den Fehler #NV zurück, wenn sie keine Übereinstimmung findet. Stattdessen möchten Sie vielleicht einen anderen Wert zurückgeben, wenn keine Übereinstimmung gefunden wird.
Für was ist der Sverweis gut? ›Die Funktion SVERWEIS wird immer dann verwendet, wenn Sie mehrere Excel-Tabellen miteinander vergleichen und/oder zusammenfassen wollen. Das S steht hierbei für Senkrecht, denn Excel durchsucht mit dieser Funktion die erste senkrechte Spalte beider Tabellen nach dem angegebenen Zielwert.
Wann wird der Sverweis verwendet? ›Verwenden Sie SVERWEIS, wenn Sie Elemente in einer Tabelle oder einem Bereich zeilenweise suchen müssen.
Wann Sverweis Wann wenn Funktion? ›Die Syntax der WENN-Funktion sieht so aus: „=WENN(Prüfung; [Dann_Wert]; [Sonst_Wert])“. Über das Argument „Prüfung“ legen Sie eine Bedingung fest, die erfüllt beziehungsweise nicht erfüllt werden soll. Ist die Bedingung erfüllt, soll Excel den „[Dann_Wert]“, in diesem Fall einen SVERWEIS, ausführen.
Warum funktioniert Sverweis nicht Bezug? ›Eine Einschränkung von SVERWEIS besteht darin, dass die Funktion nur in der äußerst linken Spalte der Matrix nach Werten suchen kann. Wenn Sie also einen Wert suchen, der sich nicht in der ersten Spalte der Matrix befindet, wird Ihnen ein #N/A-Fehler angezeigt.