Supportnet / Forum / Tabellenkalkulation
mit "sverweis" 2-3 Trefferanzeigen
Frage
Hallo zusammen,
ist es möglich mit dem Befehl sverweis mehr als ein Treffer anzuzeigen?
Meine Tabelle sieht vereinfacht so aus:
ArtNr Bezeichnung Preis Lieferant
21 Artkel a 15 xy
22 Artikel b 17,50 rt
23 Artikel c 15,50 xy
23 Artikel c 15,90 zt
23 Artikel c 16,10 ghj
Jetzt möchte ich, dass bei eingabe der Artikelnr. 23
alle drei Datensätze angezeigt werden? Ist das möglich
Antwort 1 von Saarbauer
Hallo,
nei geht nicht, der Zugriff erfolgt immer auf den 1. Datemsatz.
Ich vermute, du barauchst von den 3 den Günstigsten.
Gruß
Helmut
nei geht nicht, der Zugriff erfolgt immer auf den 1. Datemsatz.
Ich vermute, du barauchst von den 3 den Günstigsten.
Gruß
Helmut
Antwort 2 von rainberg
Hallo Osito,
schreibe in eine Zelle Deiner Wahl folgende Matrixformel und schließe die Formeleingabe mit der Tastenkombination Strg+Shift+Enter ab.
Nun kopierst Du die Formel 3 Zellen nach rechts und nach Bedarf nach unten.
=WENN(ISTFEHLER(KKLEINSTE(WENN($A$2:$A$1000=$F$1;ZEILE($2:$1000));ZEILE(A1)));"";INDEX($A$1:$L$1000;KKLEINSTE(WENN($A$2:$A$1000=$F$1;ZEILE($2:$1000));ZEILE(A1));SPALTE(A:A)))
Gruß
Rainer
schreibe in eine Zelle Deiner Wahl folgende Matrixformel und schließe die Formeleingabe mit der Tastenkombination Strg+Shift+Enter ab.
Nun kopierst Du die Formel 3 Zellen nach rechts und nach Bedarf nach unten.
=WENN(ISTFEHLER(KKLEINSTE(WENN($A$2:$A$1000=$F$1;ZEILE($2:$1000));ZEILE(A1)));"";INDEX($A$1:$L$1000;KKLEINSTE(WENN($A$2:$A$1000=$F$1;ZEILE($2:$1000));ZEILE(A1));SPALTE(A:A)))
Gruß
Rainer
Antwort 3 von rainberg
Hallo Osito,
habe vergessen zu erwähnen, dass das Suchkriteruim in F1 steht.
Kannst es ja wo anders hinschreiben und die Formel entsprechend korrigieren.
Gruß
Rainer
habe vergessen zu erwähnen, dass das Suchkriteruim in F1 steht.
Kannst es ja wo anders hinschreiben und die Formel entsprechend korrigieren.
Gruß
Rainer
Antwort 4 von fedjo
Hallo Osito,
vielleicht währe ein Autofilter die Lösung:
http://www.netupload.de/detail.php?img=c162fba7ed4d50244bacab720240...
Gruß
fedjo
vielleicht währe ein Autofilter die Lösung:
http://www.netupload.de/detail.php?img=c162fba7ed4d50244bacab720240...
Gruß
fedjo
Antwort 5 von Osito
Hallo Rainer,
klappt wunderbar! Vielen Dank!!!!!!!!!
Gruß Osito
klappt wunderbar! Vielen Dank!!!!!!!!!
Gruß Osito
Antwort 6 von Osito
Hallo Rainer,
habe mein Suchfeld in Tabelle2 verlegt. Ich schaffe es nicht die Funktion so zu ergänzen, dass sie noch funkioniert. Kannst Du mir da vielleicht nochmal weiter helfen?
Daten in Tabelle1
Suchfeld F1 in Tabelle2
Datenanzeige auch in Tabelle2
Gruß Osito
habe mein Suchfeld in Tabelle2 verlegt. Ich schaffe es nicht die Funktion so zu ergänzen, dass sie noch funkioniert. Kannst Du mir da vielleicht nochmal weiter helfen?
Daten in Tabelle1
Suchfeld F1 in Tabelle2
Datenanzeige auch in Tabelle2
Gruß Osito
Antwort 7 von rainberg
Hallo Osito,
einfach den Tabellennamen vor die Zelladresse mit dem Suchkriterium setzen.
=WENN(ISTFEHLER(KKLEINSTE(WENN($A$2:$A$1000=Tabelle2!$F$1;ZEILE($2:$1000));ZEILE(A1)));"";INDEX($A$1:$L$1000;KKLEINSTE(WENN($A$2:$A$1000=Tabelle2!$F$1;ZEILE($2:$1000));ZEILE(A1));SPALTE(A:A)))
Gruß
Rainer
einfach den Tabellennamen vor die Zelladresse mit dem Suchkriterium setzen.
=WENN(ISTFEHLER(KKLEINSTE(WENN($A$2:$A$1000=Tabelle2!$F$1;ZEILE($2:$1000));ZEILE(A1)));"";INDEX($A$1:$L$1000;KKLEINSTE(WENN($A$2:$A$1000=Tabelle2!$F$1;ZEILE($2:$1000));ZEILE(A1));SPALTE(A:A)))
Gruß
Rainer
Antwort 8 von Osito
Hallo zusammen,
meine Tabelle besteht aus 18000 Artikel. Die Suche bricht nach dem 1000. Artikel ab. Wenn ich jetzt aber in der Formel die 1000 durch 18000 ersetze, funkioniert das Ganze nicht mehr. Es wird egal welche Artikelnummer ich eingebe immer der erste Artikel mit Mehrfachlieferanten angezeigt. Kann mir da jemand weiterhelfen?
meine Tabelle besteht aus 18000 Artikel. Die Suche bricht nach dem 1000. Artikel ab. Wenn ich jetzt aber in der Formel die 1000 durch 18000 ersetze, funkioniert das Ganze nicht mehr. Es wird egal welche Artikelnummer ich eingebe immer der erste Artikel mit Mehrfachlieferanten angezeigt. Kann mir da jemand weiterhelfen?
Antwort 9 von Saarbauer
Hallo,
die Formel von @rainberg ist nur für 1000 artikel ausgelegt.
in der Formel ist
=WENN(ISTFEHLER(KKLEINSTE(WENN($A$2:$A$1000=Tabelle2!$F$1;ZEILE($2:$1000));ZEILE(A1)));"";INDEX($A$1:$L$1000;KKLEINSTE(WENN($A$2:$A$1000=Tabelle2!$F$1;ZEILE($2:$1000));ZEILE(A1));SPALTE(A:A)))
durch den entsprechenden Wert (würde hier 2000 wählen, dann ist noch etwas Luft) zu ersetzen
Gruß
Helmut
die Formel von @rainberg ist nur für 1000 artikel ausgelegt.
in der Formel ist
=WENN(ISTFEHLER(KKLEINSTE(WENN($A$2:$A$1000=Tabelle2!$F$1;ZEILE($2:$1000));ZEILE(A1)));"";INDEX($A$1:$L$1000;KKLEINSTE(WENN($A$2:$A$1000=Tabelle2!$F$1;ZEILE($2:$1000));ZEILE(A1));SPALTE(A:A)))
durch den entsprechenden Wert (würde hier 2000 wählen, dann ist noch etwas Luft) zu ersetzen
Gruß
Helmut
Antwort 10 von Osito
wenn ich die 1000 durch 2000 oder 18.000 ersetzte funkioniert die Formel nicht mehr. Und ich verstehe einfach nicht warum. Es wird dann nicht der Wert angezeigt den ich in F1 eingebe, sondern die Artikelnummer, die in der Tabelle zuerst mehrfach erscheint.
Antwort 11 von rainberg
Hallo Osito,
sorry, die Formel kann nicht funktionieren, da ich die Tabellen verwechselt habe.
So ist sie richtig:
=WENN(ISTFEHLER(KKLEINSTE(WENN(Tabelle1!$A$2:$A$18000=$F$1;ZEILE($2:$18000));ZEILE(A1)));"";INDEX(Tabelle1!$A$1:$L$18000;KKLEINSTE(WENN(Tabelle1!$A$2:$A$18000=$F$1;ZEILE($2:$18000));ZEILE(A1));SPALTE(A:A)))
Wundere Dich aber nicht, wenn sie bei 18000 Zeilen merklich langsamer wird, das kann sogar unerträglich langsam werden.
Gruß
Rainer
sorry, die Formel kann nicht funktionieren, da ich die Tabellen verwechselt habe.
So ist sie richtig:
=WENN(ISTFEHLER(KKLEINSTE(WENN(Tabelle1!$A$2:$A$18000=$F$1;ZEILE($2:$18000));ZEILE(A1)));"";INDEX(Tabelle1!$A$1:$L$18000;KKLEINSTE(WENN(Tabelle1!$A$2:$A$18000=$F$1;ZEILE($2:$18000));ZEILE(A1));SPALTE(A:A)))
Wundere Dich aber nicht, wenn sie bei 18000 Zeilen merklich langsamer wird, das kann sogar unerträglich langsam werden.
Gruß
Rainer
Antwort 12 von rainberg
Hallo Osito,
Du kannst auch auf obige Matrixformel verzichten und wie folgt verfahren:
Füge in Tabelle1 vor Deiner 18000-zeiligen Liste eine Hilfsspalte ein, schreibe in A2 folgende Formel und kopiere sie bis in Zeile 18000.
=WENN(B2=Tabelle2!F$1;MAX(A$1:A1)+1;"")
In Tabelle2 verwendest Du nun folgende Formel und verfährst damit wie gehabt.
=WENN(ISTNV(SVERWEIS(ZEILE(1:1);Tabelle1!$A:$E;SPALTE(B:B);0));"";SVERWEIS(ZEILE(1:1);Tabelle1!$A:$E;SPALTE(B:B);0))
(Beide Formeln nicht mit Strg+Shift+Enter abschließen)
Gruß
Rainer
Du kannst auch auf obige Matrixformel verzichten und wie folgt verfahren:
Füge in Tabelle1 vor Deiner 18000-zeiligen Liste eine Hilfsspalte ein, schreibe in A2 folgende Formel und kopiere sie bis in Zeile 18000.
=WENN(B2=Tabelle2!F$1;MAX(A$1:A1)+1;"")
In Tabelle2 verwendest Du nun folgende Formel und verfährst damit wie gehabt.
=WENN(ISTNV(SVERWEIS(ZEILE(1:1);Tabelle1!$A:$E;SPALTE(B:B);0));"";SVERWEIS(ZEILE(1:1);Tabelle1!$A:$E;SPALTE(B:B);0))
(Beide Formeln nicht mit Strg+Shift+Enter abschließen)
Gruß
Rainer
Antwort 13 von Osito
Hallo Rainer,
perfekt! !!
Jetzt funktioniert alles so wie ich es mir vorgestellt habe.
Würde trotzdem gerne wissen, warum die Funktion nicht mehr funktioniert hat, nachdem ich die 1000 durch 18000 ersetzt habe. Ich hatte mit der ersten Funktion in einem Tabellenblatt weiter gearbeitet, weil ja die zweite auf Grund der Tabellenverwechslung nicht funktionierte.
Vielen Dank nochmal!!!
Gruß
Osito
=WENN(ISTFEHLER(KKLEINSTE(WENN($A$2:$A$18000=$K$1;ZEILE($2:$18000));ZEILE(A1)));"";INDEX($A$1:$L$18000;KKLEINSTE(WENN($A$2:$A$18000=$K$1;ZEILE($2:$18000));ZEILE(A1));SPALTE(A:A)))
perfekt! !!
Jetzt funktioniert alles so wie ich es mir vorgestellt habe.
Würde trotzdem gerne wissen, warum die Funktion nicht mehr funktioniert hat, nachdem ich die 1000 durch 18000 ersetzt habe. Ich hatte mit der ersten Funktion in einem Tabellenblatt weiter gearbeitet, weil ja die zweite auf Grund der Tabellenverwechslung nicht funktionierte.
Vielen Dank nochmal!!!
Gruß
Osito
=WENN(ISTFEHLER(KKLEINSTE(WENN($A$2:$A$18000=$K$1;ZEILE($2:$18000));ZEILE(A1)));"";INDEX($A$1:$L$18000;KKLEINSTE(WENN($A$2:$A$18000=$K$1;ZEILE($2:$18000));ZEILE(A1));SPALTE(A:A)))
Antwort 14 von rainberg
Hallo Osito,
kann an der Formel keinen Fehler entdecken.
Hast Du etwa vergessen diese als Matrixformel einzugeben?
Gruß
Rainer
kann an der Formel keinen Fehler entdecken.
Hast Du etwa vergessen diese als Matrixformel einzugeben?
Gruß
Rainer