Komplexe Suchformeln (Microsoft Excel)

Eddie hat eine Reihe von Teilenummern im Format 123/45678 oder 011/00345.

Er muss den Namen der entsprechenden Teilenummer aus einer anderen Tabelle nachschlagen. Diese Tabelle besteht aus drei Spalten. Die erste Spalte enthält den Teil der Teilenummer vor dem Schrägstrich (z. B. 123 oder 011) und die zweite Spalte enthält den Teil nach dem Schrägstrich (z. B. 45678 oder 00345). Die dritte Spalte enthält den Namen, den Eddie nachschlagen möchte.

Ein Teil des Problems, das Eddie hat, besteht darin, dass in der Tabelle die beiden Spalten, die jeweils einen Teil der Teilenummer enthalten, numerische Werte sind. Somit ist die vollständige Teilenummer nicht 011 und 00345, sondern 11 und 345.

Eddie fragt sich, wie er eine Suchformel für die Teilenummern (123/45678 oder 011/00345) zusammenstellen kann, um den richtigen Teilenamen aus der Tabelle zurückzugeben.

Zunächst müssen Sie einige Änderungen an der Tabelle vornehmen, die die Teilenummern enthält. Die ersten beiden Spalten sollten als Text und nicht als Zahlen formatiert sein. Dadurch werden die Werte in diesen Spalten als Text behandelt, sodass bei Eingabe von „011“ in der ersten Spalte die führende Null beibehalten wird.

Wenn Sie den Wert bereits als 011 eingegeben haben, bevor Sie die Spalte als Text formatieren, wird er weiterhin als 11 angezeigt (ohne die führende Null). Sie müssen nicht nur das Format der Spalte ändern, sondern auch alle Teilenummernfragmente, die führende Nullen enthalten, erneut eingeben.

Als nächstes müssen Sie sicherstellen, dass Ihre Teilenummerntabelle Spaltenüberschriften enthält. Stellen Sie in diesem Beispiel sicher, dass die drei Spaltenüberschriften Links, Rechts und Name sind. (Sie können diese Überschriften fett und unterstrichen machen, um sie von den Werten in jeder Spalte abzuheben.) Erstellen Sie dann Namen für die einzelnen Spalten, indem Sie die folgenden Schritte ausführen:

Nachdem Sie Ihre Teilenummerntabelle vorbereitet haben, können Sie jetzt nach Teilenummern suchen. In den Zellen rechts neben Ihren Suchwerten (123/45678 und 011/00345) geben Sie eine Array-Formel ein. Angenommen, die erste Teilenummer befindet sich in Zelle A1, würden Sie in Zelle B1 die folgende Arrayformel eingeben:

Denken Sie daran, dass Sie dies durch Drücken von Umschalt + Strg + Eingabetaste eingeben, um dies als Array-Formel zu kennzeichnen. Die Formel wird dann in der Formelleiste mit \ {geschweiften Klammern} angezeigt. Sie können die Formel nach Bedarf in die anderen Zellen in Spalte B kopieren.

Bei der Formel wird der Wert aus der Spalte Name der Tabelle abgerufen, wenn der entsprechende Wert in der linken Spalte mit einem Schrägstrich verknüpft ist und der Wert in der rechten Spalte mit dem Wert in Zelle A1 übereinstimmt. Wenn keine Übereinstimmung vorliegt, gibt die Formel einen # N / A-Fehlerwert zurück, andernfalls wird die gewünschte Teilenummer zurückgegeben.

Beachten Sie, dass dieser Ansatz nur funktioniert, wenn Sie die ersten beiden Spalten Ihrer Teilenummerntabelle als Text formatieren und sicherstellen, dass die verschiedenen Zellen führende Nullen enthalten. Wenn Sie aus irgendeinem Grund die ersten beiden Spalten der Tabelle nicht auf diese Weise formatieren können (möglicherweise gibt es zu viele davon), müssen Sie die von Ihnen verwendete Suchformel ändern:

Beachten Sie, dass diese Version der Formel (die weiterhin als Array-Formel eingegeben werden sollte) die TEXT-Funktion an zwei Stellen verwendet, um die Werte aus der linken und rechten Spalte so zu konvertieren, dass sie führende Nullen haben.

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

Dieser Tipp (2787) gilt für Microsoft Excel 97, 2000, 2002 und 2003.