Kurs zum Hintergrund der Funktion: Die XVERWEIS-Funktion in Excel sucht nach einem Wert in einem definierten Bereich und gibt dir das entsprechende Element aus einem anderen Bereich zurück. Der XVERWEIS ist eine echte Alternative zu älteren Funktionen wie dem bekannten SVERWEIS und kann in alle Richtungen suchen. Das macht sie vielseitiger in der Anwendung.

Die folgende Abbildung zeigt das Ergebnis. Du wählst im Listenfeld in der Zelle A2 einen Kontinent aus und sofort wird rechts daneben die Länderliste angezeigt.

Früher gab es in den alten Excel-Versionen eine recht komplexe Lösung mithilfe von mehreren Bereichsnamen. Heute ist es nur eine kleine Formel mit der Funktion XVERWEIS().

Bonus: Im Bonusteil erfährst du, wie du die Ausgabe der 0, die leere Felder kennzeichnet, unterdrücken kannst.

Wenn du weitere Infos zur Funktion XVERWEIS() haben möchtest, klicke hier.

In diesem Beispiel werden zur Übersichtlichkeit das Listenfeld und die Ergebnisliste gleich auf dem Tabellenblatt Listenfelder nebeneinander angezeigt. In deinem Beispiel können diese beiden Bereiche auch auf verschiedenen Tabellenblättern stehen.

Die Liste mit den Kontinenten und den dazugehörenden Ländern ist auf dem Tabellenblatt Länderliste. Die Überschrift mit den Kontinenten wird für das Listenfeld in der Zelle A2 benötigt und für den zweiten Parameter in der Funktion XVERWEIS().

Wenn du deine Liste fertig hast, kann es direkt losgehen:

1. Klicke in die Zelle, in der du den Kontinent auswählen möchtest.

2. Starte die Befehlsfolge Datei / Datenüberprüfung. Es öffnet sich das gleichnamige Dialogfeld.

3. Wähle im Feld Zulassen den Eintrag Liste aus.

4. Klicke ins Feld Quelle, aktiviere das Tabellenblatt Länderliste und markiere die Zellen mit den Kontinenten. Diese sollen gleich in der Auswahl zu sehen sein.

5. Klicke auf OK.

   

6. Prüfe jetzt, ob die Liste richtig arbeitet. Klicke auf den Listenpfeil an der Zelle und wähle einen Kontinent aus.

Diese Formel zeigt die zum gewählten Kontinent passenden Länder an:

1. Klicke in die Zelle, ab der du die Ergebnisliste der Länder sehen möchtest.

2. Gib die folgende Formel ein und bestätige durch Drücken der Taste [¢].
   =XVERWEIS(D2;Länderliste!$A$1:$E$1;Länderliste!$A$2:$E$7)

   Der erste Parameter ist die Zelladresse, in der der gewählte Kontinent steht. In diesem Beispiel A2.

   Im zweiten Parameter markierst du die Zellen mit den Überschriften. In diesem Beispiel die Zellen mit den Kontinenten in A1 bis E1.

   Der dritte Parameter sind die Zellen mit den Ländernamen in diesem Beispiel A2 bis E7. Denke daran, die Zellen mit den Überschriften (Kontinente) und die Zellen mit den Ländern durch Drücken der Taste [F4] festzusetzen.

3. Wähle einen Kontinent aus, um die Länderliste zu testen.

Wenn du die Länder in der Ergebnisliste alphabetisch sortiert sehen möchtest, dann erweitere die Formel in der Zelle B2 um die Sortieren-Funktion:

=SORTIEREN(XVERWEIS(A2;Länderliste!$A$1:$E$1;Länderliste!$A$2:$E$7))

Die folgende Abbildung zeigt, dass der XVERWEIS eine 0 liefert, wenn nicht genügend Länder vorhanden sind.

In Excel gibt es mehrere Möglichkeiten, die Anzeige der 0 zu unterdrücken. Hier werden jetzt zwei Varianten vorgestellt.

Erzeuge ein Zahlenformat, das die Anzeige der 0 verhindert.

1. Markiere die Zellen, die du gestalten möchtest.

2. Klicke mit der rechten Maustaste hinein und wähle den Befehl Zellen formatieren.

3. Aktiviere im gleichnamigen Dialogfeld den Reiter Zahlen und wähle die Kategorie Benutzerdefiniert aus.

4. Leere das Feld Typ und gib folgendes Muster ein:
   0;-0;““;@

5. Bestätige mit OK.

   
   

   Ab jetzt wird in den markierten Zellen die Zahl 0 nicht angezeigt.

Erweitere die Formel mit der WENN-Funktion.

1. Klicke in die Zelle mit der Formel.

2. Erweitere die Formel und bestätige mit [¢]:

=WENN(XVERWEIS(A2;A10:E10;A11:E16)=0;"";XVERWEIS(A2;A10:E10;A11:E16))

Wenn der XVERWEIS() eine 0 liefert, wird nichts ausgegeben, ansonsten wird das Ergebnis angezeigt.