Formulas and Functions
Durch das Ändern dieser Steuerung wird die Seite neu geladen

XVERWEIS

Die Funktion XVERWEIS sucht in einem Bereich nach einem bestimmten Wert und ermittelt den Wert aus der gleichen Zeile in einer anderen Spalte.

XVERWEIS(Suchwert; Suchbereich; Ausgabebereich; wenn_nicht_gefunden; Übereinstimmungstyp; Suchtyp)

Suchwert: Der Wert, nach dem in Suchbereich gesucht wird. Suchwert kann jeden beliebigen Wert oder eine REGEX-Zeichenfolge enthalten.

Suchbereich: Die Zellen, in denen gesucht werden soll.

Ausgabebereich: Die Zellen, die ausgegeben werden sollen.

wenn_nicht_gefunden: Ein optionales Argument, mit dem festgelegt wird, welche Nachricht angezeigt werden soll, wenn keine Übereinstimmung gefunden wird.

Übereinstimmungstyp: Ein optionales Argument, mit dem festgelegt wird, nach welcher Art von Übereinstimmung gesucht werden soll.

Exakt oder nächstkleinerer Wert (-1): Gibt es keine Übereinstimmung, wird ein Fehler ausgegeben.

Genaue Übereinstimmung (0 oder keine Angabe): Gibt es keine genaue Übereinstimmung, wird ein Fehler ausgegeben.

Exakt oder nächstgrößerer Wert (1): Gibt es keine Übereinstimmung, wird ein Fehler ausgegeben.

Platzhalter (2): *, ? und ~ haben eine spezielle Bedeutung. REGEX kann in der Funktion XVERWEIS nur verwendet werden, wenn du das Argument Platzhalter angibst.

Suchtyp: Ein optionales Argument, mit dem festgelegt wird, in welcher Reihenfolge der Bereich durchsucht wird.

Binär absteigend (-2): Bei der binären Suche muss der Bereich absteigend sortiert werden, da sonst ein Fehler ausgegeben wird.

Vom letzten zum ersten Wert (-1): Der Bereich wird vom letzten bis zum ersten Wert durchsucht.

Vom ersten zum letzten Wert (1 oder keine Angabe): Der Bereich wird vom ersten bis zum letzten Wert durchsucht.

Binär aufsteigend (2): Bei der binären Suche muss der Bereich aufsteigend sortiert werden, da sonst ein Fehler ausgegeben wird.

Hinweise

  • Wenn es sich entweder beim Argument Suchbereich oder beim Argument Ausgabebereich um eine übergreifende Referenz (wie „B“) handelt, werden Titel- und Abschlusszellen automatisch ignoriert.

Beispiel

Die folgende Tabelle mit dem Namen „Produkte“ enthält Produkte und deren Attribute wie „Größe“ und „Preis“:

A

B

C

D

E

1

Produkt

Länge (cm)

Breite (cm)

Gewicht (kg)

Preis

2

Produkt 1

16

17

10

82,00 €

3

Produkt 2

16

20

18

77,00 €

4

Produkt 3

11

11

15

88,00 €

5

Produkt 4

15

16

20

63,00 €

Suchen mit XVERWEIS

Mit der Funktion XVERWEIS kannst du eine Formel in deine Tabellenkalkulation einfügen, die zugehörige Werte ermittelt, indem zuerst der Produktname gesucht und anschließend die Spalte mit dem gewünschten Wert ermittelt wird. Wenn du beispielsweise die Breite von Produkt 1 in der obigen Tabelle ermitteln möchtest, könntest du die folgende Formel verwenden, die das Ergebnis 17 cm liefert:

Der Formel-Editor mit der Formel =XVERWEIS(Produkte::$A2;Produkte::A;Breite).

In dieser Formel werden die folgenden Argumente verwendet:

  • Suchwert: Produkte::$A2 – eine absolute Referenz auf die Zelle in der Tabelle „Produkte“, die den Wert „Produkt 1“ enthält.

  • Suchbereich: Produkte::A – die Spalte, in der nach „Produkt 1“ gesucht werden soll.

  • Ausgabebereich: Breite – die Spalte, die den Wert enthält, der zu Produkt 1 gehört und ermittelt werden soll.

  • Übereinstimmungstyp: Keine Angabe. Wird das Argument Übereinstimmungstyp nicht angegeben, sucht die Funktion XVERWEIS standardmäßig nach einer genauen Übereinstimmung.

Zeichenfolge für wenn_nicht_gefunden festlegen

Du kannst die folgende Formel verwenden, wenn du nach einer bestimmten Produktlänge suchen und die zugehörige Breite ermitteln möchtest. Außerdem soll der Text „Keine Übereinstimmung“ angezeigt werden, wenn keine Übereinstimmung gefunden wird:

Der Formel-Editor mit der Formel =XVERWEIS(13;Länge;Breite;"Keine Übereinstimmung";0)

In dieser Formel wird das Argument wenn_nicht_gefunden verwendet, um eine spezifischere Suche durchzuführen:

  • Suchwert: 13 – der Wert, nach dem in dem im Argument Suchbereich angegebenen Bereich gesucht werden soll.

  • Suchbereich: Länge – die Spalte, in der gesucht werden soll.

  • Ausgabebereich: Breite – die Spalte, die den Wert enthält, der geliefert werden soll, wenn für den Suchwert eine Übereinstimmung gefunden wird.

  • wenn_nicht_gefunden: Keine Übereinstimmung – die Zeichenfolge, die angezeigt werden soll, wenn kein Produkt mit der Länge „13 cm“ gefunden wird.

  • Übereinstimmungstyp: genaue Übereinstimmung (0). Es wird nur nach der Länge „13 cm“ gesucht.

Nächstbesten Wert finden

XVERWEIS ermöglicht auch eine breitere Suche auf der Basis eines bestimmten Werts und ähnlichen Werten. Wenn du den Übereinstimmungstyp in der obigen Formel änderst, kannst du die Breite ermitteln, die einer Länge von 13 cm bzw. dem nächstkleineren Wert entspricht. Die Formel unten würde eine Breite von 11 cm ermitteln:

Der Formel-Editor mit der Formel =XVERWEIS(13;Länge;Breite;"Keine Übereinstimmung";1;-1)

Abgesehen davon, dass für Übereinstimmungstyp ein anderer Wert verwendet wird, um die Art der Suche in der Tabelle zu ändern, sind die Argumente in dieser Formel gleich wie in der vorigen Formel.

  • Übereinstimmungstyp: Exakt oder nächstkleinerer Wert (-1) – es wird entweder nach einer Länge von 13 cm gesucht. Falls dieser Wert nicht gefunden wird, wird nach dem nächstkleineren Wert in der Spalte „Länge“ gesucht.

Suchreihenfolge ändern

In einigen Fällen kann es nützlich sein, die Reihenfolge zu ändern, in der eine Tabelle mit der Funktion XVERWEIS durchsucht wird. In der obigen Tabelle gibt es beispielsweise zwei Produkte mit einer Länge von 16 cm, sodass es zwei potenzielle Übereinstimmungen gibt, wenn du in der Spalte „Länge“ nach „16 cm“ suchst und dabei die Argumente Suchwert und Suchbereich verwendest. Du kannst die Suchreihenfolge mit einer ähnlichen Formel wie der folgenden festlegen, die den Ergebniswert „20 cm“ liefert:

Der Formel-Editor mit der Formel =XVERWEIS(16;Länge;Breite;"Keine Übereinstimmung";1;-1)

In dieser Formel wird das Argument Suchtyp verwendet, um die Reihenfolge festzulegen, in der die Funktion XVERWEIS die Tabelle nach einer Übereinstimmung durchsucht:

  • Suchwert: 16 – der Wert, nach dem in dem im Argument Suchbereich angegebenen Bereich gesucht werden soll.

  • Suchbereich: Länge – die Spalte, in der gesucht werden soll.

  • Ausgabebereich: Breite – die Spalte, die den Wert enthält, der geliefert werden soll, wenn für den Suchwert eine Übereinstimmung gefunden wird.

  • wenn_nicht_gefunden: Keine Übereinstimmung – die Zeichenfolge, die angezeigt werden soll, wenn kein Produkt mit der Länge „16 cm“ gefunden wird.

  • Übereinstimmungstyp: Exakt oder nächstgrößter Wert (1). Es wird entweder nach einer Länge von 16 cm gesucht oder nach dem nächstgrößeren Wert in der Spalte „Länge“, falls dieser Wert nicht gefunden wird.

  • Suchtyp: Vom letzten zum ersten Wert (-1). Die Spalte wird vom letzten bis zum ersten Wert durchsucht.

XVERWEIS in Verbindung mit anderen Funktionen verwenden

XVERWEIS kann auch in Verbindung mit anderen Funktionen wie SUMME verwendet werden. Du kannst beispielsweise eine Formel wie die unten stehende verwenden, um „247,00 €“ zu ermitteln. Bei diesem Wert handelt es sich um die Summe der Preise von Produkt 1, 2 und 3:

Der Formel-Editor mit der Formel =SUMME(XVERWEIS(Produkte::$A2;Produkte::A;Preis):XVERWEIS(Produkte::$A4;Produkte::A;Preis))

In diesem Beispiel wird mit der ersten Funktion XVERWEIS nach dem Preis von Produkt 1 gesucht. Mit der zweiten Funktion XVERWEIS wird nach dem Preis von Produkt 3 gesucht. Der Doppelpunkt (:) zwischen den beiden XVERWEIS-Funktionen gibt an, dass die Funktion SUMME nicht nur den Gesamtpreis von Produkt 1 und Produkt 2, sondern auch von allen dazwischenliegenden Werten ermitteln soll.

In der unten stehenden Formel wird XVERWEIS in Verbindung mit REGEX verwendet, um Produkt 2 zu ermitteln, das erste Produkt, dessen Breite mit der Ziffer 2 beginnt.

Der Formel-Editor mit der Formel =XVERWEIS(REGEX("^2.*"); Produkte::C2:C5; Produkte::A2:A5; FALSCH;2)

In diesem Beispiel wird der Platzhalter (2) für Übereinstimmungstyp verwendet, um Platzhalter in der Funktion REGEX zu nutzen.

Zusätzliche Beispiele

Beispieltabelle:

A

B

C

1

Name

Alter

Gehalt

2

Alina

35

71000

3

Matthias

27

81000

4

Chloe

42

86000

5

Sophia

51

66000

6

Ken

28

52000

7

Tom

49

62000

8

Aaron

63

89000

9

Maria

22

34000

10

Alicia

29

52000

11

Ben

35

52500

=XVERWEIS(49;B2:B11;C2:C11) liefert den Ergebniswert 62.000. Hierbei handelt es sich um das Gehalt des ersten Mitarbeiters im Zellenbereich, der 49 Jahre alt ist.

=XVERWEIS(60000;C2:C11;B2:B11; "Keine Übereinstimmung") liefert den Ergebniswert „Keine Übereinstimmung“, da es keinen Mitarbeiter gibt, dessen Gehalt genau 60.000 Euro beträgt.

=XVERWEIS(REGEX("^C.*"); A2:A11; B2:B11; FALSCH; 2) liefert den Ergebniswert 42. Hierbei handelt es sich um das Alter von Chloe, der ersten Mitarbeiterin im Zellenbereich, deren Name mit dem Buchstaben C beginnt.

Siehe auchXVERGLEICH
Morty Proxy This is a proxified and sanitized view of the page, visit original site.
Hilfreich?
Zeichenbeschränkung: 250
Die maximal zulässige Anzahl von Zeichen beträgt 250.
Vielen Dank für Ihre Rückmeldung.
Morty Proxy This is a proxified and sanitized view of the page, visit original site.