SVERWEIS mit zwei Suchkriterien: vier Wege im Vergleich

SVERWEIS sucht nach einem einzigen Wert. In vielen Tabellen reicht das nicht: Ein Artikel hat je nach Kundengruppe einen anderen Preis, ein Mitarbeiter je nach Monat eine andere Stundenzahl. Erst zwei Angaben zusammen ergeben einen eindeutigen Treffer.

Dieser Beitrag zeigt vier Wege, die alle zum selben Ergebnis führen. Welchen Sie einsetzen, hängt von Ihrer Excel-Version ab und davon, ob Sie die Nachschlagetabelle verändern dürfen.

Die Ausgangslage

Auf dem Blatt Kundenpreise steht jeder Artikel dreimal, einmal je Kundengruppe. Rechts liegt ein Angebot, in dem Artikelnummer und Kundengruppe in getrennten Spalten stehen.

Eine Suche allein nach BB-3001 träfe drei Zeilen. SVERWEIS nähme davon die erste und lieferte damit den Standardpreis, auch für einen Partner.

Weg 1: Hilfsspalte mit SVERWEIS

Der klassische Weg. Er läuft in jeder Excel-Version und kommt ohne Matrixformel aus. Sie fassen beide Merkmale zu einem Suchbegriff zusammen.

Die Hilfsspalte muss links stehen, denn SVERWEIS sucht immer in der ersten Spalte der Matrix. Geben Sie in A4 ein und kopieren Sie die Formel bis Zeile 15:

=B4&C4

Das kaufmännische Und verbindet beide Inhalte zu einem Text. Aus BB-3001 und Partner wird BB-3001Partner. Dieser Schlüssel kommt nur einmal vor.

Im Angebot verbinden Sie die beiden Spalten auf dieselbe Weise:

=SVERWEIS(F4&G4;$A$4:$D$15;4;FALSCH)

Setzen Sie zwischen beide Teile ein Trennzeichen, das in den Daten nicht vorkommt: =B4&"|"&C4. Ohne Trennzeichen können zwei verschiedene Kombinationen denselben Schlüssel ergeben, aus 12 und 345 wird dasselbe wie aus 123 und 45, aus AB und C dasselbe wie aus A und BC.

Das Suchkriterium muss dann ebenfalls F4&"|"&G4 lauten. Dasselbe gilt für die beiden folgenden Wege, die ebenfalls mit einer Verkettung arbeiten.

Weg 2: XVERWEIS ohne Hilfsspalte

XVERWEIS kann die Verkettung selbst übernehmen. Die Suchmatrix ist dann keine einzelne Spalte, sondern zwei verbundene Spalten. Eine Hilfsspalte im Blatt brauchen Sie nicht.

=XVERWEIS(F4&G4;$B$4:$B$15&$C$4:$C$15;$D$4:$D$15;"kein Preis")

Die Spalte A bleibt leer. Das ist der Vorteil dieses Wegs: Sie fassen die Nachschlagetabelle nicht an. Das zählt vor allem dann, wenn die Tabelle aus einem anderen System stammt und regelmäßig überschrieben wird.

Diese Schreibweise setzt Microsoft 365, Excel 2021 oder Excel 2024 voraus.

Weg 3: INDEX und VERGLEICH

Diese Kombination läuft in jeder Excel-Version und braucht ebenfalls keine Hilfsspalte.

=INDEX($D$4:$D$15;VERGLEICH(F4&G4;$B$4:$B$15&$C$4:$C$15;0))

VERGLEICH sucht den zusammengesetzten Wert und liefert dessen Position in der Liste. INDEX holt den Wert an dieser Position aus der Preisspalte.

Wichtig in älteren Versionen: Bis einschließlich Excel 2019 ist das eine Matrixformel. Sie müssen die Eingabe mit Strg + Umschalt + Enter abschließen, sonst rechnet Excel sie nicht zuverlässig und meldet meist #NV. Excel setzt die Formel danach in geschweifte Klammern. In Microsoft 365, Excel 2021 und Excel 2024 genügt Enter. Wie die beiden Funktionen einzeln arbeiten, steht unter INDEX und VERGLEICH.

Weg 4: SUMMEWENNS bei Zahlen

Wenn das Ergebnis eine Zahl ist, geht es noch einfacher. SUMMEWENNS addiert alle Werte, auf die sämtliche Bedingungen zutreffen. Bei genau einem Treffer ist die Summe dieser eine Wert.

=SUMMEWENNS($D$4:$D$15;$B$4:$B$15;F4;$C$4:$C$15;G4)

Die Funktion gibt es ab Excel 2007, sie braucht keine Matrixformel und keine Hilfsspalte. Drei Einschränkungen sollten Sie kennen. Texte kann SUMMEWENNS nicht zurückgeben, nur Zahlen. Wenn mehrere Zeilen passen, addiert die Funktion sie stillschweigend, doppelte Einträge fallen also nicht auf.

Und wenn gar keine Zeile passt, liefert SUMMEWENNS eine 0 statt einer Fehlermeldung. Ein fehlender Preis ist damit nicht von einem echten Nullpreis zu unterscheiden. Wenn das in Ihrer Tabelle vorkommen kann, prüfen Sie zusätzlich mit ZÄHLENWENNS, ob es überhaupt einen Treffer gibt:

=WENN(ZÄHLENWENNS($B$4:$B$15;F4;$C$4:$C$15;G4)=0;"kein Preis";SUMMEWENNS($D$4:$D$15;$B$4:$B$15;F4;$C$4:$C$15;G4))

Welcher Weg für welchen Fall

Empfehlung: Für Dateien, die im Haus bleiben und mit einer aktuellen Version bearbeitet werden, ist XVERWEIS der kürzeste Weg. Für Dateien, die Sie weitergeben, ist die Hilfsspalte die verlässlichste Lösung, weil sie in jeder Version läuft. Ihr zusammengesetzter Schlüssel steht außerdem sichtbar im Blatt, während er bei den anderen Wegen nur in der Formel entsteht.

Häufige Stolpersteine

• Unterschiedliche Reihenfolge. B4&C4 verlangt auf der anderen Seite F4&G4. Vertauscht ergibt es einen anderen Schlüssel.
• Leerzeichen am Ende eines der beiden Werte. Der Schlüssel stimmt dann nicht mehr überein. Hier hilft GLÄTTEN um jede Zelle.
• Zahl gegen Text. Wird eine Zahl verkettet, entsteht immer Text. Das ist unproblematisch, solange beide Seiten gleich aufgebaut sind.
• Die Hilfsspalte steht rechts. Dann findet SVERWEIS sie nicht. Sie muss links von der Ergebnisspalte liegen und die erste Spalte der Matrix sein.

Bleibt das Ergebnis #NV, hilft die Liste unter SVERWEIS funktioniert nicht weiter.

Übungsdatei zum Herunterladen

Das Blatt Kundenpreise in der Übungsmappe enthält die Tabelle mit der leeren Hilfsspalte.

SVERWEIS Übungsdatei herunterladen

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: die Grundlagen der Funktion, auf der die Hilfsspalte aufsetzt.
• SVERWEIS funktioniert nicht: was zu prüfen ist, wenn der zusammengesetzte Schlüssel nicht findet.
• INDEX und VERGLEICH: die beiden Funktionen aus Weg 3 einzeln erklärt.
• Alle Treffer ausgeben: wenn mehrere Zeilen passen und Sie alle sehen möchten.