Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
25.04.2024 21:21:16
Zellkommentar in UserForm anzeigen und bearbeiten
25.04.2024 21:13:13
Kalenderwochen addieren
25.04.2024 21:06:05
Duplikate entfernen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1344to1348
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1344to1348
1344to1348
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

INDEX/VERGLEICH soll nicht die erste Pos. liefern

INDEX/VERGLEICH soll nicht die erste Pos. liefern
21.01.2014 11:53:22
Giesarazu
Hallo liebes Forum,
der SVERWEIS oder (besser) INDEX/VERGLEICH liefern ja standardmäßig immer den 1. Eintrag, der dem Suchkriterium entspricht. Ich würde aber gerne, da das Suchkriterium öfter vorkommt, je nach Wunsch-Definition den 2., 3. usw. Wert mir liefern lassen. Ich muss noch erwähnen, dass es sich bei den Rückgabewerten nicht um Werte, sondern Text handelt und ich möchte auch nicht alle Texte auf einmal, sondern wie gesagt, nur nach Definition den 2., 3. oder x.ten Text zurückliefern.
Ist so etwas auch möglich ?
Danke sehr für einen Tipp
Stefan

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
ist mit einer MATRIXFormel möglich ...
21.01.2014 11:55:00
neopa
Hallo Stefan,
... genaueres nach näheren Angaben.
Gruß Werner
.. , - ...

AW: ist mit einer MATRIXFormel möglich ...
21.01.2014 11:58:22
Giesarazu
also Suchkriterium in Tabelle 1: 10395007200157
in der Suchtabelle (Tabelle2) siehts so aus:
10395007200157...M604
10395007200157...M687
10395007200157...M705
nun möchte ich z.B. den zweiten Wert M687 geliefert bekommen.

dann z.B so ...
21.01.2014 12:10:43
neopa
Hallo Stefan,
...
 BCDE
1   10395007200157
290395007200111M603 M687
310395007200157M604  
490395007200111M605  
510395007200157M687  
610395007200157M705  
7    

Formeln der Tabelle
ZelleFormel
E2{=INDEX(C:C;KKLEINSTE(WENN(B1:B99=E1;ZEILE(1:99)); 2))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
gelöst... AW: dann z.B so ...
21.01.2014 12:16:36
Giesarazu
phantastisch !!!!!!!!
herzlichen Dank, Klasseleistung :)))

AW: INDEX/VERGLEICH soll nicht die erste Pos. liefern
21.01.2014 11:56:50
Klaus
Hi Stefan,
sehr einfach mit einer Hilfsspalte!
Ab B2 stehen die Suchbegriffe (meinetwegen Hund, Katze, Hund, Hund), ab A2 die Formel =B2&ZÄHLENWENN($B$2:B2;B2) herunterziehen. Jetzt kannst du in Spalte A nach Hund1, Hund2 oder Hund3 suchen.
Ohne Hilfsspalte wird es sehr aufwendig ...
Grüße,
Klaus M.vdT.

AW: INDEX/VERGLEICH soll nicht die erste Pos. liefern
21.01.2014 12:18:17
Giesarazu
Hi Klaus,
die Variante ist interessant und hab sie eben getestet, wäre ich nie drauf gekommen. Danach kam auch gleich die komplette Lösung von neopa :)
Euch beiden also herzlichen Dank
Stefan

Anzeige
Danke für die Rückmeldung! mit Text.
21.01.2014 12:41:46
Klaus
Hallo Stefan,
ich benutze lieber Hilfsspalten als Matrixformeln, aber erlaubt ist was hilft und was dir besser taugt musst du entscheiden :-)
Grüße,
Klaus M.vdT.

AW: INDEX/VERGLEICH soll nicht die erste Pos. liefern
24.01.2014 10:14:33
Giesarazu
Ähm, lieber Werner, wäre es unverschämt, Dich zu bitten, mir deine tolle Array-Formel zu erklären ? Nicht die komplette, so halb check ich die, aber insbesondere frage ich mich, wozu braucht man die Funktion KKLEINSTE und auch ZEILE hier, das verwirrt mich alles und beschäftigt mich schon im Schlaf...

AW: INDEX/VERGLEICH soll nicht die erste Pos. liefern
24.01.2014 10:45:21
Klaus
Hallo Stefan,
ich spring mal ein :-)
{=INDEX(C:C;KKLEINSTE(WENN(B1:B99=E1;ZEILE(1:99)); 2))}
=INDEX(C:C
ist klar, in C:C steht ja der Suchbegriff.
WENN(B1:B99=E1;ZEILE(1:99))
ZEILE 1:99 generiert einfach die Nummer 1:99, je nachdem in welcher Zeile du bist ... aber nur, wenn in Spalte B der Zeile der Suchbegriff aus E1 steht!
sagen wir mal, in Spalte B steht ab B1 "Hund / Hund / Katze / Hund" und in E1 steht "Hund" als Suchbegriff.
Dank {MATRIX} läuft WENN(B1:B99 jede Zelle von B1 bis B99 durch. In B1 findet es Hund, geht also zum DANN-Teil der Formel. ZEILE(1:99) ergibt in B1 die 1, also ist das Formelergebnis erstmal {1}.
In B2 findet es auch Hund. DANN-Teil, ZEILE(1:99) ist 2, Formelergebnis {1;2}.
In B3 steht Katze. Katze ist NICHT gleich E1 (Hund), also nix. Ergebnis bleibt bei {1;2}.
In B4 steht wieder Hund. ZEILE(1:99) ergibt 4, also {1;2;4}
KKLEINSTE(..;2) sucht den zweit-kleinsten Wert aus einer Matrix. In diesem Fall sucht KKLEINSTE also den zweit-Kleinsten Wert aus {1;2;4}.
Das ist die Zwei.
Auflösung:
{=INDEX(C:C;KKLEINSTE(WENN(B1:B99=E1;ZEILE(1:99)); 2))}
wird zu
{=INDEX(C:C;KKLEINSTE({1;2;4}); 2))}
wird zu
=INDEX(C:C;2)
und in der zweiten Zeile aus C:C steht dein Suchbegriff!
Grüße,
Klaus M.vdT.

Anzeige
AW: INDEX/VERGLEICH soll nicht die erste Pos. liefern
24.01.2014 11:01:47
Giesarazu
Altobelli, jetzt hast Du geantwortet, verstehst das natürlich auch..grmmmpff. Also wunderbar, druck ich mir mal aus als Nachtlektüre heute !!! Tausend Dank, das ist wirklich nicht selbstverständlich, hier so viel Zeit für lau zu opfern :))) Dass mit Kkleinste hab ich schon beim Überfliegen verstanden, Hurraaa. Excel is echt der Oberhammer, vor allem, wenn ichs mal begreifen würde..

Danke für die Rückmeldung! mit Text
24.01.2014 11:19:03
Klaus
Hi Stefan,
ich hab ja auch Spaß dran!
Excel is echt der Oberhammer, vor allem, wenn ichs mal begreifen würde..
Es gab einen Zeitpunkt an dem ich dachte, ich hätte Excel "begriffen". Dann hat mir jemand Matrixformeln gezeigt. Dann kam der Zeitpunkt wieder. Dann habe ich VBA entdeckt. Irgendwann dachte ich, ok mit Formeln + Matrixformeln + VBA kannst du ziemlich alles erschlagen was Excel hergibt. Dann habe ich Lösungen von Rudi gesehen, die mit "regular Expressions" und "Scripting Libarys" innerhalb von ein paar Zeilen und Milisekunden erledigen, wofür ich Seiten-und Minutenweise Code brauche.
Oder die schier unendliche UDF-Sammlung von Luc ... immer nach dem Motto "warte, das Problem habe ich bereits 1973 schonmal gelöst!". Oder natürlich die hunderte "WOW-das-geht-auch?" Formeln von www.excelformeln.de ...
Excel ist auch für mich der Oberhammer, aber das mit dem "begreifen" habe ich mir inzwischen abgeschminkt :-)
Grüße,
Klaus M.vdT.

Anzeige
AW: Danke für die Rückmeldung! mit Text
24.01.2014 12:52:29
Giesarazu
das gibts auch noch alles ? Echt zum Verzweifeln :( aber beruhige Dich, wenn Du das schon alles kannst, biste sicherlich Bundesliga, ich stufe mich inzwischen als Aspirant für den Bezirksligaaufstieg ein, falls ich die Formel bis Montag kapieren sollte...

AW: INDEX/VERGLEICH soll nicht die erste Pos. liefern
24.01.2014 15:14:08
Giesarazu
wirklich sehr sehr gute Erklärung !!! habs mir eben auf der Firmentoilette komplett reingezogen und tatsächlich verstanden :) vielleicht ists sogar reproduzierbar, das wäre dann der Gipfel der Erleuchtung !

AW: INDEX/VERGLEICH soll nicht die erste Pos. liefern
24.01.2014 15:46:03
KlausF
Hallo Namensvetter,
da möchte ich mich anschließen: wirklich eine sehr gute Erklärung!
Du wärest bestimmt ein guter Lehrer ...
Gruß
Klaus
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige