Nachschlagen von Werten mit SVERWEIS, INDEX oder VERGLEICH (2024)

Tipp:Versuchen Sie, die neuen Funktionen XLOOKUP und XMATCH zu verwenden, verbesserte Versionen der in diesem Artikel beschriebenen Funktionen. Diese neuen Funktionen funktionieren in beliebige Richtungen und geben standardmäßig genaue Übereinstimmungen zurück, sodass sie einfacher und bequemer zu verwenden sind als ihre Vorgänger.

Angenommen, Sie verfügen über eine Liste von Bürostandortnummern und müssen wissen, welche Mitarbeiter sich in den einzelnen Büros befinden. Das Arbeitsblatt ist riesig, sodass Sie vielleicht denken, dass es eine herausfordernde Aufgabe ist.Dies ist eigentlich ganz einfach mit einer Nachschlagefunktion zu tun.

Die Funktionen SVERWEIS und SVERWEIS sind zusammen mit INDEX und MATCH einige der nützlichsten Funktionen in Excel.

Hinweis:Der Nachschlage-Assistent ist in Excel nicht mehr verfügbar.

Hier sehen 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 die Funktion verwenden muss. 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, den Sie suchen möchten. Das dritte Argument ist die Spalte in diesem Zellbereich, die den gesuchten Wert enthält.

Das vierte Argument ist optional. Geben Sie entweder TRUE oder FALSE 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 angegeben wird. Anders ausgedrückt: Wenn Sie das vierte Argument leer lassen oder TRUE 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 SLOOKUP die Zellen im Bereich C2:E7 (2. Argument) und gibt die nächstgelegene ungefähre Übereinstimmung aus der dritten Spalte im Bereich, Spalte E (3. Argument) zurück.

Nachschlagen von Werten mit SVERWEIS, INDEX oder VERGLEICH (1)

Das vierte Argument ist leer, sodass die Funktion eine ungefähre Übereinstimmung zurückgibt. 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 HLOOKUP-Funktion ebenso einfach zu verwenden. Sie geben die gleichen Argumente ein, suchen aber in Zeilen anstelle von Spalten.

Verwenden von INDEX und MATCH anstelle von SVERWEIS

Es gibt bestimmte Einschränkungen bei der Verwendung von SVERWEIS. Die SVERWEIS-Funktion kann nur einen Wert von links nach rechts suchen. Dies bedeutet, dass sich die Spalte, die den nachschlagenden Wert enthält, immer links neben der Spalte befinden sollte, die den Rückgabewert enthält. Wenn Ihr Arbeitsblatt nicht auf diese Weise erstellt wurde, verwenden Sie nicht SVERWEIS. Verwenden Sie stattdessen die Kombination der Funktionen INDEX und MATCH.

Dieses Beispiel zeigt eine kleine Liste, in der sich der Wert, nach dem wir durchsuchen möchten, Chicago, nicht in der spalte ganz links befindet. Daher können wir SVERWEIS nicht verwenden. Stattdessen verwenden wir die MATCH-Funktion, um Chicago im Bereich B1:B11 zu finden. Sie befindet sich in Zeile 4. Index verwendet dann diesen Wert als Nachschlageargument und sucht die Grundfüllung für Chicago in der 4. Spalte (Spalte D). Die verwendete Formel wird in Zelle A14 angezeigt.

Nachschlagen von Werten mit SVERWEIS, INDEX oder VERGLEICH (2)

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.

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 ein leeres Arbeitsblatt.

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 Text umbrechen (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

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: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 Text umbrechen (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

Index- und Vergleichsbeispiele

In diesem letzten Beispiel werden die Funktionen INDEX und MATCH 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 Text umbrechen (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

Kurzübersichtskarte: SVERWEIS-Auffrischungs

- und Verweisfunktionen (Referenz)

Verwenden des arguments table_array in einer SVERWEIS-Funktion

Nachschlagen von Werten mit SVERWEIS, INDEX oder VERGLEICH (2024)
Top Articles
Latest Posts
Recommended Articles
Article information

Author: Dong Thiel

Last Updated:

Views: 6658

Rating: 4.9 / 5 (59 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Dong Thiel

Birthday: 2001-07-14

Address: 2865 Kasha Unions, West Corrinne, AK 05708-1071

Phone: +3512198379449

Job: Design Planner

Hobby: Graffiti, Foreign language learning, Gambling, Metalworking, Rowing, Sculling, Sewing

Introduction: My name is Dong Thiel, I am a brainy, happy, tasty, lively, splendid, talented, cooperative person who loves writing and wants to share my knowledge and understanding with you.