Kapitel 17 · 17.4
Suchen nach zwei Bedingungen gleichzeitig
SVERWEIS und XVERWEIS suchen nach einem einzigen Wert (siehe SVERWEIS gibt #NV zurück: so lösen Sie das Problem und XVERWEIS: nach links suchen und mit einer eigenen Meldung). In der Praxis suchen Sie aber oft nach zwei Dingen gleichzeitig: dem Preis eines Straußes in Größe L, dem Tarif eines Kunden in einem bestimmten Jahr oder dem Lagerbestand eines Produkts in einer bestimmten Farbe. Dafür gibt es drei gute Methoden, die alle mit einem normalen Enter funktionieren. Eine vierte Methode, die Sie im Internet häufig finden, funktioniert in Euro-Office nicht ohne Weiteres.
Als Beispiel nehmen wir die Preisliste der Bloemisterij Het Zonnetje. Dasselbe Produkt steht darin mehrmals, in verschiedenen Größen:
| A | B | C | |
|---|---|---|---|
| 1 | Produkt | Größe | Preis |
| 2 | Strauß | S | 17,50 |
| 3 | Strauß | M | 24,50 |
| 4 | Strauß | L | 34,50 |
| 5 | Trauergesteck | M | 65,00 |
| 6 | Trauergesteck | L | 89,00 |
| 7 | Brautstrauß | M | 79,50 |
Das gesuchte Produkt steht in F2 (Strauß), die Größe in G2 (L). Wir suchen 34,50.
Methode 1: XVERWEIS mit verketteten Werten
=XVERWEIS(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"nicht gefunden")
Das &-Zeichen fügt Text zusammen. Aus F2&"|"&G2 wird "Strauß|L". A2:A7&"|"&B2:B7 bildet aus jeder Zeile der Liste ebenfalls eine solche Kombination ("Strauß|S", "Strauß|M" und so weiter), und XVERWEIS sucht darin. Der Strich | dazwischen verhindert, dass zwei unterschiedliche Kombinationen zufällig denselben Text ergeben. Gibt es die Kombination nicht, zum Beispiel ein Trauergesteck in Größe S, erhalten Sie "nicht gefunden".

Wenn Sie nach der Eingabe in die Bearbeitungsleiste schauen, steht dort ein @ vor A2:A7 und B2:B7. Das fügt Euro-Office selbst ein; das Ergebnis stimmt trotzdem.
Methode 2: FILTER für alle Treffer
=FILTER(C2:C7;(A2:A7=F2)*(B2:B7=G2);"keine")
FILTER liefert nicht nur die erste, sondern wirklich alle Zeilen, die beide Bedingungen erfüllen. Kommt eine Kombination zweimal vor, sehen Sie beide Preise untereinander. Sorgen Sie also dafür, dass unter der Formel Platz ist (siehe Listen, die sich selbst aktualisieren, mit FILTER, EINDEUTIG und SORTIEREN). Für "oder" statt "und" verwenden Sie ein Pluszeichen: (A2:A7="Strauß")+(A2:A7="Trauergesteck").
Methode 3: SUMMEWENNS, wenn die Kombination eindeutig ist
=SUMMEWENNS(C2:C7;A2:A7;F2;B2:B7;G2)
Das klingt seltsam, denn Sie möchten ja nichts addieren. Kommt jede Kombination aber nur einmal vor, ist die Summe einfach dieser eine Preis. Der Vorteil: SUMMEWENNS ist einfach, schnell und in jeder Excel-Version bekannt. Der Nachteil: Gibt es die Kombination nicht, erhalten Sie 0 (€ 0,00) statt einer Meldung. Und steht die Kombination versehentlich zweimal in der Liste, werden beide Preise addiert.
Was Sie im Internet finden: INDEX und VERGLEICH mit einer Multiplikation
Viele Excel-Seiten empfehlen =INDEX(C2:C7;VERGLEICH(1;(A2:A7=F2)*(B2:B7=G2);0)). In Euro-Office liefert diese Formel mit einem normalen Enter #NV, auch wenn die Kombination existiert. Schließen Sie sie mit Strg+Umschalt+Enter ab, erhält die Formel geschweifte Klammern {…} und funktioniert. Das vergisst man leicht, verwenden Sie daher lieber XVERWEIS oder FILTER von oben.
Suchen nach Zeile und Spalte
Ist Ihre Preisliste als Tabelle mit den Produkten untereinander und den Größen nebeneinander aufgebaut (eine Matrix), verwenden Sie INDEX mit zweimal VERGLEICH. Mit den Produkten in A11:A13, den Größen S, M und L in B10:D10 und den Preisen in B11:D13:
=INDEX(B11:D13;VERGLEICH(F2;A11:A13;0);VERGLEICH(G2;B10:D10;0))
Das erste VERGLEICH sucht die Zeile des Produkts, das zweite die Spalte der Größe. INDEX nimmt die Zelle am Schnittpunkt.
Welche Methode wählen Sie?
| Situation | Beste Wahl |
|---|---|
| Eine Antwort, mit einer sauberen Meldung, wenn sie nicht existiert | XVERWEIS mit verketteten Werten |
| Die Datei muss auch in einer alten Excel-Version funktionieren | SUMMEWENNS |
| Es kann mehrere Treffer geben | FILTER |
| Preistabelle mit Größen als Spaltenüberschriften | INDEX mit zweimal VERGLEICH |
Stolperfallen
- #NV, obwohl die Kombination wirklich vorhanden ist: oft ein Leerzeichen hinter einem Wert ("Strauß ") oder eine Zahl, die als Text gespeichert ist. Siehe SVERWEIS gibt #NV zurück: so lösen Sie das Problem.
- Bereiche unterschiedlicher Länge: =XVERWEIS(F2;A2:A7;C2:C50) liefert #WERT!. Lassen Sie alle Bereiche in derselben Zeile beginnen und enden.
- Ganze Spalten (A:A) machen in diesen Formeln eine große Datei langsam, weil Euro-Office dann eine Million Zeilen verkettet. Verwenden Sie einen festen Bereich wie A2:A500. Siehe Eine langsame Tabellenkalkulation schneller machen.
- Ein Kollege mit Excel 2019 oder älter sieht bei XVERWEIS und FILTER #NAME?.
