SVERWEIS/ VLOOKUP für mehrere Treffer

Forumthread: SVERWEIS/ VLOOKUP für mehrere Treffer

20.09.2015 19:11:32

Makaveli

Hallo,
vorabgesagt ich verwende die englische Version von Excel, aber ihr könnt die Lösung gerne als eine deutsche Excel-Formel verfassen.
Ich würde gerne über eine Formel folgendes realisieren:
In Spalte A stehen Länderkürzel für alle Länder der Welt...wie zum Beispiel "DE" für Deutschland. In Spalte B stehen die dazugehörigen Sprachen wie zum Beispiel "deutsch".
Nun gibt es aber auch Länder, in denen mehrere Sprachen gesprochen werden wie zum Beispiel in der Schweiz: deutsch, französisch und italienisch. Somit gibt es drei Zeilen in Spalte A für das Länderkürzel "CH".
Ich möchte nun in Spalte E x-beliebige Länderkürzel einfügen und mit einer entsprechenden Formel die Länderkürzel und die dazugehörige Sprache in den Spalten F und G ausgeben, hierbei ist die Datenquelle Spalte A und B.
Beispiel für Einträge in Spalte E:
CA
CH
DE
Ergebnis in Spalte F und G soll sein:
CA englisch
CA französisch
CH deutsch
CH französisch
CH italienisch
DE deutsch
Mit VLOOKUP bzw. SVERWEIS würde Excel immer nur den ersten Treffer ausspucken. Ich benötige aber alle Einträge. Praktisch wie wenn ich in Spalte A nach dem Länderkürzel filtern würde und die Daten aus Spalte A und B in Spalte F und G rüberkopieren würde.
Ich hoffe ich konnte mein Problem gut genug beschreiben.
Vielen Dank schonmal für die Vorschläge.
Grüße,
Makaveli

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: SVERWEIS/ VLOOKUP für mehrere Treffer

20.09.2015 19:26:50

Makaveli

Danke für die sehr schnelle Antwort, aber leider ist das nicht die Lösung meines Problems bzw. nicht besonders komfortabel.
Es wird zwar erwähnt wie die Formel aussehen muss, wenn es mehrere Suchkriterien gibt, aber wenn ich 50 Länder habe, dann muss ich die Formel an 50 Stellen anpassen, damit ich das Ergebnis bekomme...da bin ich mit Filtern schneller fertig :)

lade eine Beispieldatei hoch! o.T.

20.09.2015 19:59:19

Sepp

Gruß Sepp

AW: lade eine Beispieldatei hoch! o.T.

20.09.2015 22:20:59

Makaveli

Hier die Beispieldatei
https://www.herber.de/bbs/user/100288.xlsx

AW: lade eine Beispieldatei hoch! o.T.

20.09.2015 23:08:16

Sepp

Hallo Makaveli,
so?
Sheet1

	E	F	G
1	Zu suchende Länder aus Spalte A (Vorgabe)	Ergebnis für Länderkürzel aus Spalte A	Ergebnis für Sprache aus Spalte C
2	CA	CA	english
3	CH	CA	french
4	DE	CH	german
5		CH	french
6		CH	italian
7		DE	german
8
9
10
11
12
13

Formeln der Tabelle

Zelle	Formel
F2	=WENNFEHLER(INDEX($A$2:$A$310;AGGREGAT(15;6;ZEILE($A$1:$A$309)/(ISTZAHL(VERGLEICH($A$2:$A$310;$E$2:$E$7;0))); ZEILE(A1))); "")
G2	=WENN(F2="";"";INDEX($C$2:$C$310;AGGREGAT(15;6;ZEILE($A$1:$A$309)/($A$2:$A$310=F2); ZÄHLENWENN(F$2:$F2;F2))))

Bedingte Formatierungen der Tabelle

Zelle

Nr.: / Bedingung

Format

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Sepp

AW: lade eine Beispieldatei hoch! o.T.

21.09.2015 13:33:07

Makaveli

Hallo Sepp,
der Wahnsinn! Genau das ist es!
Wenn ich dich jetzt persönlich sehen würde, würd ich was ausgeben, aber da das bekanntlich schlecht geht übers Internet bedanke ich mich recht herzlich für die Mühe!
Kann man das in die entsprechende Hilfe aufnehmen, weil es ja doch sehr nützlich ist.
Grüße,
Makaveli

AW: lade eine Beispieldatei hoch! o.T.

21.09.2015 13:37:49

Makaveli

ach ja, wäre es möglich die Formel dann in der Hilfe "schrittweise" erklären?

AW: lade eine Beispieldatei hoch! o.T.

21.09.2015 19:01:16

Sepp

Hallo Makiaveli,
welche Hilfe meinst du?
In der Excel-Hilfe sind die Funktionen doch ausreichend dokumentiert.

Gruß Sepp

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Um mit dem SVERWEIS in Excel mehrere Ergebnisse für die gleiche Suchanfrage zu erhalten, kannst du folgende Schritte befolgen:

Daten vorbereiten:

Erstelle eine Tabelle mit den Länderkürzeln in Spalte A und den dazugehörigen Sprachen in Spalte B.

Beispiel:

| A  | B        |
|----|----------|
| DE | Deutsch  |
| CH | Deutsch  |
| CH | Französisch |
| CH | Italienisch |
| CA | Englisch |

Formel eingeben:

In Spalte E kannst du das Länderkürzel einfügen, für das du die Sprachen abrufen möchtest.

In Spalte F und G gibst du die Formeln ein, um die Sprachen auszugeben:

In F2:

=WENNFEHLER(INDEX($A$2:$A$310;AGGREGAT(15;6;ZEILE($A$1:$A$309)/(ISTZAHL(VERGLEICH($A$2:$A$310;$E$2:$E$7;0)));ZEILE(A1)));"")

In G2:

=WENN(F2="";"";INDEX($B$2:$B$310;AGGREGAT(15;6;ZEILE($A$1:$A$309)/($A$2:$A$310=F2);ZÄHLENWENN(F$2:$F2;F2))))

Formeln nach unten ziehen:
- Ziehe die Formeln in F2 und G2 nach unten, um alle möglichen Ergebnisse für das eingegebene Länderkürzel anzuzeigen.

Häufige Fehler und Lösungen

Fehler: #NV oder #WERT!
- Mögliche Ursache: Das angegebene Länderkürzel existiert nicht in der Datenbank.
- Lösung: Überprüfe die Eingabe in Spalte E.
Fehler: Nur ein Ergebnis wird angezeigt
- Mögliche Ursache: Die Formeln sind nicht korrekt angewendet.
- Lösung: Stelle sicher, dass die Formeln in F2 und G2 korrekt eingegeben und nach unten gezogen wurden.

Alternative Methoden

Wenn du mit SVERWEIS nicht die gewünschten Ergebnisse erzielst, kannst du alternativ die FILTER-Funktion verwenden (verfügbar in Excel 365):

=FILTER($B$2:$B$310; $A$2:$A$310=$E$2)

Diese Formel gibt alle Sprachen für das in E2 eingegebene Länderkürzel zurück und ist einfacher zu handhaben.

Praktische Beispiele

Beispiel 1:

Eingabe in E2: CH

Ergebnis in F2, G2:

| F  | G          |
|----|------------|
| CH | Deutsch    |
| CH | Französisch |
| CH | Italienisch |

Beispiel 2:

Eingabe in E3: CA

Ergebnis in F3, G3:

| F  | G       |
|----|---------|
| CA | Englisch |

Tipps für Profis

Nutze die AGGREGAT-Funktion, um mehrere Ergebnisse zu filtern, ohne dass Fehler auftreten.
Überlege, ob eine Pivot-Tabelle für deine Analyse hilfreich sein könnte, besonders bei großen Datenmengen.
Die Kombination von SVERWEIS und WENNFEHLER hilft, die Benutzerfreundlichkeit zu erhöhen.

FAQ: Häufige Fragen

1. Kann ich den SVERWEIS auch mit mehreren Bedingungen verwenden? Ja, du kannst die SVERWEIS-Formel anpassen, um mehrere Bedingungen zu berücksichtigen, indem du weitere Spalten in die Analyse einbeziehst.

2. Wie gebe ich mehrere Ergebnisse in einer Zelle aus? Du kannst die Ergebnisse mit der TEXTVERKETTEN-Funktion oder &-Operator in einer Zelle zusammenfassen:

=TEXTVERKETTEN(", "; WAHR; G2:G10)

3. Ist XLOOKUP (XVERWEIS) eine bessere Alternative? Ja, die XLOOKUP-Funktion ist flexibler als SVERWEIS und erlaubt es dir, mehrere Werte direkt zu extrahieren, ohne dass du separate Formeln für jede Spalte benötigst.

Beliebteste Forumthreads (12 Monate)

SVERWEIS/ VLOOKUP für mehrere Treffer

Forumthread: SVERWEIS/ VLOOKUP für mehrere Treffer

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

SVERWEIS für mehrere Treffer in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

SVERWEIS/ VLOOKUP für mehrere Treffer

Forumthread: SVERWEIS/ VLOOKUP für mehrere Treffer

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

SVERWEIS für mehrere Treffer in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread