Excel Werte aus mehreren Spalten in eine Spalte

Manchmal steht man in Excel vor einer Aufgabe und denkt sich: Das ist doch ganze einfach! Aber auf den zweiten Blick stellt man fest: Mist, doch nicht so einfach…

Hast du zum Beispiel schon einmal versucht, nur mit Hilfe von Formeln einen bestimmten Wert in einer Tabelle zu finden? Also eine Suche, die sich sowohl über mehrere Spalten als auch über mehrere Zeilen erstreckt? Klingt eigentlich ganz einfach, oder?

Dann lass dich mal überraschen. Denn die Lösung führt über eine Funktion, die völlig zu Unrecht ein ziemliches Schattendasein fristet: AGGREGAT

Und so geht’s:

Die Aufgabe

Ich habe einen kleinen Schichtplan, in dem verschiedene Mitarbeiter entsprechend ihrer Funktion und der zugeteilten Schicht eingetragen sind. Die Beispieltabelle kannst du dir hier herunterladen.

Nun möchte ich in einem Suchfeld einen Mitarbeiter auswählen und mir dann anzeigen lassen, welcher Schicht er zugeordnet ist und welche Funktion er hat:

Finde zu einem Mitarbeiter Schicht und Funktion

Hieße die Aufgabenstellung beispielsweise: Wer ist der Brezensalzer von Schicht C, wäre eine Lösung mit Hilfe von INDEX und VERGLEICH relative schnell gefunden:

Anders herum wäre es einfach

Unsere Aufgabe ist aber genau anders herum: Wir kennen zwar den Mitarbeiter, aber wir wissen weder die Zeile noch die Spalte, in der er zu finden ist. Nun ist guter Rat teuer…

Lösung Teil 1: Finden der Schicht

Die INDEX-Funktion ist grundsätzlich schon ein sehr guter Ausgangspunkt, liefert sie doch einen Wert zurück, der sich in einer angegebenen Zeile und/oder Spalte befindet:
=INDEX(Matrix;Zeile;Spalte)

Für die Ausgabe der können wir uns auf die Kopfzeile beschränken, bezogen auf mein Beispiel wäre das also
=INDEX(A7:E7;1;Spalte)

Fehlt also nur noch das Spalten-Argument. Dazu könnten wir die Funktion SPALTE nutzen. Sie soll aber nur dann eine Spaltennummer zurückliefern, wenn der Wert der Zeile auch dem gesuchten Mitarbeiter entspricht. Daher können wir folgende Formel verwenden, die ich zur Verdeutlichung in einer kleinen Hilfstabelle eingetragen habe:

Eine Hilfstabelle sorgt für mehr Transparenz

Für die erste Zelle prüfen wir =SPALTE(A8)/(A8=$B$3)
Für die Zellen daneben sieht es so aus:
=SPALTE(B8)/(B8=$B$3)
=SPALTE(C8)/(C8=$B$3)
und so weiter.

Wenn der Wert der Zelle nicht dem gesuchten Mitarbeiter entspricht, wird der Divisor FALSCH bzw. 0. Daher erhalten wir fast überall den Fehlerwert #DIV/0! Bis auf die einzige Zelle D17, die den Wert 4 für die vierte Spalte liefert:

Der gesuchte Mitarbeiter steht in Spalte 4

Nun brauchen wir also nur noch den einzigen Wert aus unserer Hilfstabelle – als 4 – der auch gleichzeitig der kleinste Wert in der Tabelle ist. Dafür wäre grundsätzlich die KKLEINSTE-Funktion geeignet, wenn in der Tabelle keine Fehlerwerte enthalten wären. Denn damit kann diese Funktion nicht umgehen und liefert selbst einen Fehler:

KKLEINSTE liefert nur einen Fehler

Und genau an dieser Stelle kommt unsere Geheimwaffe zum Einsatz: Die AGGREGAT-Funktion.

Lösung Teil 1

Wie schon in diesem Artikel beschrieben, ist AGGREGAT ein echtes Multitalent, das viele Funktionen in sich vereinigt. Neunzehn Stück, um genau zu sein. Und dazu gehört auch die KKLEINSTE-Funktion. Wenn wir AGGREGAT jetzt auf unsere Hilfstabelle loslassen, kommt der gewünschte Wert 4 heraus:
=AGGREGAT(15;6;A16:E20;1)

AGGREGAT liefert das Ergebnis

Die Nummer 15 im ersten Argument steht für die KKLEINSTE-Funktion:

Funktion 15 = KKLEINSTE

Option 6 ignoriert Fehlerwerte

AGGREGAT klappt auch ohne Hilfstabelle

Jetzt haben wir alles, um in Kombination mit der Index-Funktion auf die richtige Spalte 4 zugreifen zu können und somit für den Mitarbeiter 12 als Ergebnis die Schicht C erhalten:

Die komplette Formel für die Schicht

Lösung Teil 2

Jetzt wo wir die Schicht kennen, wollen wir noch die Funktion des Mitarbeiters bestimmen. Auch dafür greifen wir auf die Kombination aus INDEX und AGGREGAT zurück, nur dass wir jetzt eben nicht die erste Zeile der Tabelle, sondern die erste Spalte durchsuchen.

Folglich ersetzen wir die SPALTE-Funktion durch die ZEILE-Funktion:

Die komplette Formel für die Funktion

Aber warum ziehe ich am Ende der Formel den Wert 7 ab? Ohne diese Subtraktion würde die AGGREGAT-Funktion für den Mitarbeiter 12 die Zeile 9 zurückliefern, was grundsätzlich richtig ist.

Ohne Subtraktion wird die absolute Zeilennummer geliefert

Für die INDEX-Funktion brauchen wir aber nicht die absolute Zeilennummer, sondern die relative Zeile innerhalb unserer Schichttabelle. Da unsere erst Tabelle in Zeile 7 beginnt, muss ich den Wert 7 wieder abziehen, um auf die richtige Zeilennummer zu kommen. Hier nochmal die beiden Formeln im direkten Vergleich:

Beide Formeln im direkten Vergleich

Ich gebe zu, die Lösung war etwas kniffelig. Wenn man sich aber mal eine Weile damit beschäftigt hat, wird sie sicherlich nach und nach besser verständlich. Und sie zeigt, wie mächtig die vernachlässigte AGGREGAT-Funktion eigentlich ist.

Wie kann man in Excel Spalten zusammenfassen?

Wie mache ich aus 2 Zellen eine?

Wie vergleiche ich zwei Spalten und gebe Werte aus der dritten Spalte in Excel zurück?

Wie kann man Spalten verbinden?