SVERWEIS und INDEX für mehrere Treffer in Excel
Schritt-für-Schritt-Anleitung
Um in Excel mit SVERWEIS oder INDEX mehrere Treffer zu erhalten, kannst du die folgende Formel verwenden. Diese Methode nutzt die Funktionen WENNFEHLER und AGGREGAT, um alle Ergebnisse untereinander anzuzeigen.
-
Daten vorbereiten: Erstelle eine Tabelle mit den Daten, die du durchsuchen möchtest.
-
Formel eingeben: In der Zelle, in der du das erste Ergebnis sehen möchtest, gib folgende Formel ein:
=WENNFEHLER(INDEX($H:$H; AGGREGAT(15; 6; ZEILE($A$1:$A$99)/($G$1:$G$19=$A2)/($A2>0); SPALTE(A1))); "")
Hierbei ist $H:$H der Bereich, aus dem die Daten stammen und $G$1:$G$19 der Suchbereich.
-
Formel ausfüllen: Ziehe die Formel nach unten und nach rechts, um die weiteren Treffer anzuzeigen.
Häufige Fehler und Lösungen
-
Fehler: #NV: Dies bedeutet, dass es keinen weiteren Treffer gibt. Stelle sicher, dass die Daten korrekt eingegeben sind.
-
Fehler: #DIV/0!: Dieser Fehler tritt auf, wenn die Division durch Null erfolgt. Überprüfe die Bedingungen in der Formel.
-
Formel nicht aktualisiert: Stelle sicher, dass die Berechnung auf "automatisch" eingestellt ist. Gehe zu Formeln > Berechnungsoptionen > Automatisch.
Alternative Methoden
Falls du Excel 365 oder eine neuere Version verwendest, kannst du die Funktion FILTER nutzen, um mehrere Treffer zu erhalten:
=FILTER($H$1:$H$19; $G$1:$G$19=$A2)
Diese Funktion gibt alle zutreffenden Ergebnisse in einer Liste zurück.
Eine weitere Alternative ist der XVERWEIS, der flexibler ist und mehrere Treffer ohne komplizierte Formeln zurückgeben kann.
Praktische Beispiele
Angenommen, in deiner Tabelle hast du folgende Daten:
| Kenner |
Zuordnung |
| 195/55 R16 91V |
439560 |
| 195/55 R16 91V |
694824 |
| 175/70 R14 84T |
346024 |
| 205/65 R15 94H |
630673 |
Wenn du den Kenner 195/55 R16 91V suchst, wird die Formel alle zugehörigen Zuordnungen zurückgeben.
Tipps für Profis
- Verwende
WENNFEHLER, um unerwünschte Fehler in deinen Ergebnissen zu vermeiden.
- Experimentiere mit
AGGREGAT, um die Leistung deiner Formeln zu verbessern, insbesondere bei großen Datenmengen.
- Nutze die
XVERWEIS-Funktion, wenn du Excel 365 nutzt, um die Benutzerfreundlichkeit zu erhöhen und den Code zu vereinfachen.
FAQ: Häufige Fragen
1. Wie kann ich den zweiten Treffer mit SVERWEIS finden?
Du kannst die Formel anpassen, indem du den AGGREGAT-Teil änderst, um den zweiten Treffer zu suchen.
2. Gibt es eine Möglichkeit, alle Treffer auf einmal anzuzeigen?
Ja, mit der FILTER-Funktion in Excel 365 kannst du alle Treffer auf einmal anzeigen lassen.
3. Was ist der Unterschied zwischen SVERWEIS und INDEX/VERGLEICH?
SVERWEIS sucht nur nach dem ersten Treffer, während INDEX/VERGLEICH flexibler ist und mehrere Treffer zurückgeben kann, wenn du die Formel entsprechend anpasst.