Tipp
Versuchen Sie, die neuen XVERWEIS - und XVERGLEICH-Funktionen zu verwenden, verbesserte Versionen der in diesem Artikel beschriebenen Funktionen. Diese neuen Funktionen funktionieren in beliebiger Richtung und geben standardmäßig genaue Übereinstimmungen zurück, wodurch sie einfacher und bequemer zu verwenden sind als ihre Vorgänger.
Angenommen, Sie verfügen über eine Liste mit Bürostandortnummern und müssen wissen, welche Mitarbeiter in den einzelnen Büros beschäftigt sind. Die Tabellenkalkulation ist riesig, daher könnten Sie denken, dass es sich um eine herausfordernde Aufgabe handelt. Mit einer Nachschlagefunktion ist das eigentlich recht einfach.
Die Funktionen SVERWEIS und WVERWEIS gehören zusammen mit INDEX und VERGLEICH zu den 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 erste Argument – ein Datenelement, das die Funktion benötigt, um zu funktionieren. Für SVERWEIS ist dieses erste Argument der Wert, den Sie finden 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 gesuchten Wert gesucht wird. 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, entspricht die Funktion dem Wert, der durch das erste Argument angegeben wird. 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 (2. Argument) und gibt die beste ungefähre Übereinstimmung aus der dritten Spalte im Bereich, Spalte E (3. Argument), zurück.
Da das vierte Argument leer ist, gibt die Funktion eine ungefähre Entsprechung 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 mit SVERWEIS vertraut sind, ist die WVERWEIS-Funktion ebenso einfach zu verwenden. Sie geben die gleichen Argumente ein, aber es wird in Zeilen statt in Spalten durchsucht.
Verwenden von INDEX und VERGLEICH anstelle von SVERWEIS
Es gibt bestimmte Einschränkungen bei der Verwendung von SVERWEIS: Die SVERWEIS-Funktion kann nur einen Wert von links nach rechts nachschlagen. Dies bedeutet, dass sich die Spalte mit dem Nachschlagewert 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 den Funktionen INDEX und VERGLEICH.
Dieses Beispiel zeigt eine kleine Liste, in der sich der Wert, nach dem gesucht werden soll, Chicago, nicht in der Spalte ganz links befindet. Daher können wir SVERWEIS nicht verwenden. Stattdessen verwenden wir die Funktion VERGLEICH, um Chicago im Bereich B1:B11 zu finden. Es befindet sich in Zeile 4. Anschließend verwendet INDEX diesen Wert als Suchargument und sucht die Grundgesamtheit für Chicago in der 4. Spalte (Spalte D). Die verwendete Formel wird in Zelle A14 angezeigt.
Probieren Sie es aus
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 Tabelle.
Tipp
Bevor Sie die Daten in Excel einfügen, legen Sie die Spaltenbreiten für die Spalten A bis C auf 250 Pixel fest, und klicken Sie auf "Zeilenumbruch " (Registerkarte "Start ", 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 | 0 |
| Formel | Beschreibung | Ergebnis |
| =SVERWEIS(1;A2:C10;2) | Bei Verwendung einer ungefähren Entsprechung wird bei einer Suche nach dem Wert 1 in Spalte A 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 Spalte B 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 Spalte A 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 Spalte C 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 Spalte A 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 Spalte B zurückgegeben. | 1,71 |
Beispiel für WVERWEIS
Kopieren Sie alle Zellen in dieser Tabelle, und fügen Sie sie in Zelle A1 eines leeren Arbeitsblatts in Excel ein.
Tipp
Bevor Sie die Daten in Excel einfügen, legen Sie die Spaltenbreiten für die Spalten A bis C auf 250 Pixel fest, und klicken Sie auf "Zeilenumbruch " (Registerkarte "Start ", 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 |
Beispiele für INDEX und VERGLEICH
Im letzten Beispiel werden die Funktionen INDEX und VERGLEICH zusammen verwendet, um die früheste Rechnungsnummer und das entsprechende Datum für jeden der fünf Orte zurückzugeben. Da das Datum als Zahl zurückgegeben wird, verwenden wir die TEXT-Funktion, 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
Bevor Sie die Daten in Excel einfügen, legen Sie die Spaltenbreiten für die Spalten A bis D auf 250 Pixel fest, und klicken Sie auf "Textumbruch " (Registerkarte "Start ", 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 |
Siehe auch
Kurzübersichtskarte: SVERWEIS – Auffrischung
Nachschlage- und Verweisfunktionen (Referenz)