Office: Nachschlagen von Werten mit SVERWEIS, INDEX oder VERGLEICH

Nachschlagen von Werten mit SVERWEIS, INDEX oder VERGLEICH
Excel für Microsoft 365 Excel für das Web Excel 2019 Excel 2016 Excel 2013 Mehr... Weniger Tipp: Verwenden Sie die neuen Funktionen XLOOKUP und XMATCH , verbesserte Versionen der in diesem Artikel beschriebenen Funktionen. Diese neuen Funktionen funktionieren in beliebiger Richtung und geben standardmäßig exakte Übereinstimmungen zurück, wodurch Sie einfacher und bequemer zu verwenden sind als ihre Vorgänger.

Angenommen, Sie verfügen über eine Liste mit Office-Standort Nummern, und Sie müssen wissen, welche Mitarbeiter sich in den einzelnen Niederlassungen befinden. Das Arbeitsblatt ist riesig, daher ist es vielleichteine herausfordernde Aufgabe. Es ist wirklich ganz einfach, mit einer Nachschlagefunktion zu tun.

Die Funktionen SVERWEIS und WVERWEIS sowie Index und Vergleichsind einige der nützlichsten Funktionen in Excel.

Hinweis: Das Feature des Nachschlage-Assistenten steht in Excel nicht mehr zur Verfügung.

Im folgenden finden Sie 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 von der Funktion verwendet werden muss. Bei 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, den Sie suchen möchten. Das dritte Argument ist die Spalte in diesem Zellbereich, die den Wert enthält, den Sie suchen.

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 "false" eingeben, entspricht die Funktion dem Wert, der vom ersten Argument bereitgestellt 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 (zweites Argument) und gibt die nächstgelegene ungefähre Übereinstimmung aus der dritten Spalte im Bereich, Spalte E (drittes Argument) zurück.





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 mit SVERWEIS vertraut sind, können Sie die WVERWEIS-Funktion genauso einfach verwenden. Sie geben dieselben Argumente ein, suchen aber in Zeilen anstelle von Spalten.

Verwenden von Index und Vergleich anstelle von SVERWEIS
Bei der Verwendung von SVERWEIS gibt es bestimmte Einschränkungen: die SVERWEIS-Funktion kann nur einen Wert von links nach rechts nachschlagen. Dies bedeutet, dass sich die Spalte, die den gesuchten Wert enthält, immer links neben der Spalte befinden sollte, die den Rückgabewert enthält. Wenn Ihre Kalkulationstabelle nun nicht auf diese Weise erstellt wird, verwenden Sie SVERWEIS nicht. Verwenden Sie stattdessen die Kombination aus den Funktionen Index und Vergleich.

Dieses Beispiel zeigt eine kleine Liste, in der der Wert, nach dem gesucht werden soll, Chicago, nicht in der Spalte ganz links steht. Daher können wir SVERWEIS nicht verwenden. Stattdessen verwenden wir die Funktion vergleichen, um Chicago im Bereich B1: B11 zu finden. Sie ist in Zeile 4 zu finden. Dann verwendet Index diesen Wert als Nachschlage Argument und findet die Grundgesamtheit für Chicago in der vierten Spalte (Spalte D). Die verwendete Formel wird in Zelle A14 angezeigt.



Weitere Beispiele für die Verwendung von Index und Vergleich 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: 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 (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	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

WVERWEIS-Beispiel
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 (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

Beispiele für Index und Vergleich
In diesem letzten Beispiel werden die Funktionen Index und Vergleich zusammen verwendet, um die früheste Rechnungsnummer und das entsprechende Datum für jede der fünf Städte 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 Zeilenumbruch (RegisterkarteStart , Gruppe Ausrichtung ).

Rechnung	Ort	Rechnungsdatum	Früheste Rechnung nach Ort mit Datum
3115	Aachen	7.4.12	="Aachen = "&INDEX($A:$C;VERGLEICH("Aachen";$B:$B;0);1)& ", Rechnungsdatum: " & TEXT(INDEX($A:$C;VERGLEICH("Aachen";$B:$B;0);3);"t.m.jj")
3137	Aachen	9.4.12	="Augsburg = "&INDEX($A:$C;VERGLEICH("Augsburg";$B:$B;0);1)& ", Rechnungsdatum: " & TEXT(INDEX($A:$C;VERGLEICH("Augsburg";$B:$B;0);3);"t.m.jj")
3154	Aachen	11.4.12	="Darmstadt = "&INDEX($A:$C;VERGLEICH("Darmstadt";$B:$B;0);1)& ", Rechnungsdatum: " & TEXT(INDEX($A:$C;VERGLEICH("Darmstadt";$B:$B;0);3);"t.m.jj")
3191	Aachen	21.4.12	="Nürnberg = "&INDEX($A:$C;VERGLEICH("Nürnberg";$B:$B;0);1)& ", Rechnungsdatum: " & TEXT(INDEX($A:$C;VERGLEICH("Nürnberg";$B:$B;0);3);"t.m.jj")
3293	Aachen	25.4.12	="Essen = "&INDEX($A:$C;VERGLEICH("Essen";$B:$B;0);1)& ", Rechnungsdatum: " & TEXT(INDEX($A:$C;VERGLEICH("Essen";$B:$B;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

-Nachschlage-und-Verweisfunktionen (Referenz)

verwenden Sie das Argument table_array in einer SVERWEIS-Funktion

:)