Alan kann die Datenüberprüfung verwenden, um eine Dropdown-Liste gültiger Auswahlmöglichkeiten für eine Zelle zu erstellen. Was er jedoch tatsächlich braucht, ist komplexer. Er hat eine große Anzahl von Artikelnamen mit zugehörigen Artikelcodes. In Zelle B2 kann er eine Datenvalidierungsliste erstellen, in der alle Artikelnamen (Rührwerk, Motor, Pumpe, Tank usw.) aufgeführt sind. Der Benutzer kann dann eine davon auswählen. Wenn er jedoch an anderer Stelle auf Zelle B2 verweist, möchte er, dass der Artikelcode – nicht der Artikelname – von der Referenz zurückgegeben wird. Somit würde die Referenz A, M, P, TK usw. anstelle von Rührwerk, Motor, Pumpe, Tank usw. zurückgeben.

In Excel gibt es keine direkte Möglichkeit, dies zu tun. Der Grund dafür ist, dass Datenvalidierungslisten so eingerichtet sind, dass sie nur eine eindimensionale Liste von Elementen enthalten. Dies erleichtert es der Liste, Ihre Artikelnamen zu enthalten.

Sie können jedoch die Verwendung der Datenüberprüfungsliste ein wenig erweitern, um das zu erhalten, was Sie möchten. Befolgen Sie diese Schritte:

  1. Richten Sie irgendwo rechts neben Ihren Arbeitsblattdaten eine Datentabelle ein.

Diese Tabelle enthält Ihre Artikelnamen und rechts neben jedem Artikelnamen den mit diesem Namen verknüpften Artikelcode.

  1. Wählen Sie die Zellen aus, die Ihre Artikelnamen enthalten. (Wählen Sie nicht die Artikelcodes, sondern nur die Namen aus.)

  2. Zeigen Sie die Registerkarte Formeln des Menübands an.

  3. Klicken Sie in der Gruppe Definierte Namen auf das Werkzeug Name definieren. Excel zeigt das Dialogfeld Neuer Name an. (Siehe Abbildung 1.)

  4. Geben Sie im Feld Name einen beschreibenden Namen ein, z. B. ItemNames. . Klicken Sie auf OK, um den Namen hinzuzufügen und das Dialogfeld zu schließen.

  5. Wählen Sie Zelle B2 (die Zelle, in der Sie Ihre Validierungsliste haben möchten).

  6. Zeigen Sie die Registerkarte Daten des Menübands an.

  7. Klicken Sie in der Gruppe Datentools auf das Datenüberprüfungstool. Excel zeigt das Dialogfeld Datenüberprüfung an. (Siehe Abbildung 2.)

  8. Wählen Sie in der Dropdown-Liste Zulassen die Option Liste.

  9. Geben Sie im Feld Quelle ein Gleichheitszeichen gefolgt von dem in Schritt 5 definierten Namen ein (z. B. = ItemNames).

  10. OK klicken.

Wenn diese Schritte ausgeführt wurden, können Benutzer weiterhin die Dropdown-Liste zur Datenüberprüfung verwenden, um gültige Elementnamen auszuwählen. Was Sie jetzt tun müssen, ist auf den Artikelcode aus der Datentabelle zu verweisen, die Sie in Schritt 1 eingerichtet haben. Sie können dies mit einer Formel wie der folgenden tun:

=VLOOKUP(B2,OFFSET(Itemlist,0,0,,2),2,FALSE)

Diese Formel kann einzeln verwendet werden (um den gewünschten Artikelcode in eine Zelle einzufügen) oder in einer größeren Formel, an einer Stelle, an der Sie ursprünglich auf B2 verwiesen hätten.

Wenn Sie aus irgendeinem Grund keine Datentabelle für Ihre Elementnamen und Codes erstellen können, können Sie das Problem lösen, indem Sie eine Array-Formel erstellen:

=INDEX({"A","M","P","TK"},MATCH(B2,{"agitator","motor","pump","tank"},0))

Wie bei allen Array-Formeln geben Sie diese durch Drücken von Strg + Umschalt + Eingabetaste ein. Der größte Nachteil dabei ist, dass es schnell unhandlich werden kann, die Formel auf dem neuesten Stand zu halten, und es gibt eine „Lebensfähigkeitsgrenze“ für die Anzahl der Paare von Codes und Elementen, die Sie in die Formel aufnehmen können. (Das Limit wird durch die Formellänge definiert, hängt also von der Länge Ihrer Artikelnamen ab.) Außerdem eignet sich dieser Ansatz dazu, den Artikelcode nur in einer anderen Zelle zurückzugeben, anstatt ihn als Teil einer größeren Formel einzuschließen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (12078) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.

Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Rückgabe von Artikelcodes anstelle von Artikelnamen.