SVERWEIS gibt immer nur einen Wert zurück, nämlich den ersten Treffer von oben. Wenn Sie alle passenden Zeilen brauchen, etwa sämtliche Artikel einer Kategorie oder alle Positionen einer Bestellung, kommen Sie damit nicht weiter.
Dieser Beitrag zeigt den kurzen Weg mit der Funktion FILTER und einen Weg für ältere Excel-Versionen, in denen es FILTER noch nicht gibt.
Warum SVERWEIS hier nicht reicht
In der Preisliste steht eine Artikelnummer versehentlich doppelt. SVERWEIS nimmt den ersten Eintrag und übergeht den zweiten, ohne einen Hinweis zu geben.
Bei doppelten Einträgen ist das ein Fehler, nachzulesen im Beitrag zur SVERWEIS-Funktion. Bei einer Kategorie mit mehreren Artikeln ist es dagegen die Aufgabe: Sie wollen alle Treffer sehen, nicht nur einen.
Alle Treffer mit FILTER
FILTER gibt alle Zeilen zurück, auf die eine Bedingung zutrifft. Die Funktion braucht drei Angaben: den Bereich, der ausgegeben wird, die Bedingung und optional einen Text für den Fall ohne Treffer.
=FILTER($A$4:$B$13;$C$4:$C$13=G4;"keine Treffer")
In G4 steht die gesuchte Kategorie. Die Formel steht nur in einer einzigen Zelle, Excel füllt die übrigen selbst. Dieses Verhalten heißt Überlauf, erkennbar am dünnen blauen Rahmen um das Ergebnis.
Ändern Sie den Wert in G4, passt sich das Ergebnis sofort an. Werden es mehr Zeilen, wächst der Bereich nach unten.
Dafür muss der Platz frei sein. Steht unterhalb oder rechts der Formel schon etwas, meldet Excel #ÜBERLAUF! und gibt gar nichts aus. Verbundene Zellen im Zielbereich führen zur selben Meldung. Räumen Sie den Bereich, dann erscheint das Ergebnis.
Voraussetzung: FILTER gibt es in Microsoft 365, Excel 2021 und Excel 2024. In älteren Versionen erscheint #NAME?.
Wenn nichts gefunden wird
Ohne das dritte Argument meldet FILTER #CALC!, wenn keine Zeile passt. Mit dem dritten Argument erscheint stattdessen Ihr eigener Text.
Zwei Bedingungen verbinden
Mehrere Bedingungen verbinden Sie mit einem Malzeichen. Das entspricht einem Und: Beide Bedingungen müssen zutreffen.
=FILTER($A$4:$B$13;($C$4:$C$13=G4)*($D$4:$D$13<=H4);"keine Treffer")
Von den drei Artikeln der Kategorie Ablage bleiben zwei übrig, die höchstens 5 Euro kosten. Jede Bedingung gehört in eigene Klammern.
Soll nur eine von mehreren Bedingungen zutreffen, verwenden Sie statt des Malzeichens ein Pluszeichen. Das entspricht einem Oder.
Nützliche Ergänzungen
FILTER lässt sich mit anderen Funktionen kombinieren. Drei Varianten davon sind im Alltag nützlich:
- Sortiert ausgeben:
=SORTIEREN(FILTER(...))gibt die Treffer gleich in der gewünschten Reihenfolge aus. - Anzahl der Treffer:
=ZÄHLENWENN($C$4:$C$13;G4)sagt Ihnen vorab, wie viele Zeilen zu erwarten sind. - Doppelte entfernen:
=EINDEUTIG(FILTER(...))gibt jeden Wert nur einmal aus. Bei mehreren Ausgabespalten bezieht sich das auf ganze Zeilen: Zwei Zeilen müssen in allen Spalten übereinstimmen, damit eine davon entfällt.
Ohne FILTER: der Weg für ältere Versionen
In Excel 2016 und 2019 gibt es FILTER nicht. Zwei Wege führen dort zum Ziel.
Der einfache Weg: Autofilter
Für eine einmalige Auswertung genügt der eingebaute Filter. Markieren Sie eine Zelle in der Liste und wählen Sie Daten → Filter. In der Kopfzeile erscheinen Pfeile, über die Sie die Kategorie auswählen. Wie das im Einzelnen geht, steht unter Sortieren und Filtern von Daten.
Der Nachteil: Das Ergebnis aktualisiert sich nicht von selbst, wenn sich die Daten ändern.
Der Formelweg: KKLEINSTE mit WENN
Wenn das Ergebnis mitwachsen soll, brauchen Sie eine Matrixformel. Sie ermittelt für den ersten, zweiten und dritten Treffer jeweils die Zeilennummer:
=WENNFEHLER(INDEX($A$4:$A$13;KKLEINSTE(WENN($C$4:$C$13=$G$4;ZEILE($C$4:$C$13)-3);ZEILEN($A$1:A1)));"")Geben Sie die Formel in die erste Ergebniszelle ein und schließen Sie mit Strg + Umschalt + Enter ab. Kopieren Sie sie anschließend so weit nach unten, wie Treffer möglich sind.
Zwei Teile der Formel verdienen eine Erklärung. Der Abzug von 3 ergibt sich aus der Lage der Tabelle: Die Daten beginnen in Zeile 4, gezählt werden soll ab 1. Steht Ihre Liste an anderer Stelle, passen Sie diese Zahl an.
ZEILEN($A$1:A1) zählt beim Kopieren nach unten mit: 1, 2, 3 und so weiter. Damit holt jede Zeile den nächsten Treffer. Diese Schreibweise funktioniert unabhängig davon, in welcher Zeile Ihre Ergebnisliste beginnt. WENNFEHLER sorgt dafür, dass die überzähligen Zellen leer bleiben statt #ZAHL! zu zeigen.
Diese Formel ist sperrig und schwer zu pflegen. Wenn Sie regelmäßig mit solchen Auswertungen arbeiten und nicht auf eine neuere Excel-Version wechseln können, ist eine Pivot-Tabelle meist die bessere Lösung.
Auf Englisch
FILTER heißt in der englischen Version ebenfalls FILTER. Die verwandten Funktionen lauten dort SORT und UNIQUE.
Übungsdatei zum Herunterladen
Die Preisliste mit den Kategorien steckt in der Übungsmappe zum Thema Verweisfunktionen.
Was in der Übungsdatei steckt
- Bestellungen und Preisliste für die Grundlagen
- Zahlungen für den Abgleich zweier Listen
- Kundenpreise für die Suche mit zwei Kriterien
- Rabatte für die Umsatzstaffel
- Fehlersuche mit dem Klassiker Zahl gegen Text
Die gelb hinterlegten Spalten sind leer. Dort geben Sie die Formeln selbst ein.
Weitere Anleitungen zu Verweisfunktionen
- SVERWEIS Schritt für Schritt: SVERWEIS für den Fall, dass genau ein Treffer gesucht ist.
- XVERWEIS: der Nachfolger von SVERWEIS, ebenfalls für einen Treffer.
- SVERWEIS mit zwei Suchkriterien: einen Treffer über zwei Bedingungen eindeutig bestimmen.
- SVERWEIS funktioniert nicht: was hinter #NV und stillen Fehltreffern steckt.