HERBERS

Excel-Forum (Archiv)

20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Rückwärts Blättern

Aktuelles Verzeichnis

Übersicht Verzeichnisse

Nächster Thread

Vorwärts blättern

Inhaltsverzeichnis

dynamische Such und Rückgabematrix im XVERWEIS

Forumthread: dynamische Such und Rückgabematrix im XVERWEIS
23 Beiträge zu diesem Forumthread
Anzeige

Forumthread: dynamische Such und Rückgabematrix im XVERWEIS

16.06.2025 17:42:31

Christian

Hallo, bitte helft mir bei den XVERWEIS Formeln in AZN!B1:C1.

Es geht mir wie gesagt um deren Such- und Rückgabematrix

Wenn Leute!D301>Leute!D302, dann soll wie gehabt bis Zeile 301 gesucht werden.
Wenn wie in der BspDatei Leute!D301=Leute!D302, dann soll bis Zeile 302 gesucht werden
ist auch Leute!D303 identisch, dann bis Zeile 303
ist auch Leute!D304 identisch, dann bis Zeile 304 usw.

Tabelle Leute ist wie im Beispiel nach Spalte D sortiert

Gibt es da eine Lösung?

Danke
Christian

https://www.herber.de/bbs/user/177781.xlsx

23
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: dynamische Such und Rückgabematrix im XVERWEIS

16.06.2025 17:50:02

BoskoBiati
AW: dynamische Such und Rückgabematrix im XVERWEIS

16.06.2025 18:04:11

BoskoBiati
AW: dynamische Such und Rückgabematrix im XVERWEIS

16.06.2025 18:33:07

Christian
AW: dynamische Such und Rückgabematrix im XVERWEIS

16.06.2025 17:57:02

daniel
AW: dynamische Such und Rückgabematrix im XVERWEIS

16.06.2025 18:38:40

Christian
So ganz klar...

16.06.2025 18:27:02

Case
habe eine Lösung gefunden...

16.06.2025 19:29:09

Christian
AW: habe eine Lösung gefunden...

16.06.2025 21:00:16

BoskoBiati
AW: habe eine Lösung gefunden...

16.06.2025 21:14:35

daniel
AW: habe eine Lösung gefunden...

17.06.2025 00:56:44

Christian
AW: habe eine Lösung gefunden...

16.06.2025 21:40:58

BoskoBiati
AW: habe eine Lösung gefunden...

17.06.2025 01:02:01

Christian
AW: habe eine Lösung gefunden...

17.06.2025 00:57:58

Christian
AW: dynamische Such und Rückgabematrix im XVERWEIS

16.06.2025 20:14:09

daniel
AW: dynamische Such und Rückgabematrix im XVERWEIS

17.06.2025 01:06:49

Christian
AW: So ganz klar...

16.06.2025 18:41:15

Christian
kleiner Nachtrag

16.06.2025 18:46:46

Christian
Vielleicht habe...

16.06.2025 19:15:56

Case
AW: Vielleicht habe...

16.06.2025 19:33:55

Christian
tausendmal sorry

17.06.2025 01:05:12

Christian
Also ich bin da...

16.06.2025 20:09:24

Case
AW: Also ich bin da...

17.06.2025 01:21:51

Christian
Das geht auch...

16.06.2025 20:45:42

Case

AW: dynamische Such und Rückgabematrix im XVERWEIS

16.06.2025 17:50:02

BoskoBiati

Hi,

warum begrenzt Du den Bereich?
In 365 begrenzt man den Bereich so:

=XVERWEIS(A1;Leute!B$2:.B$301;Leute!C$2:.C$301;"";0;1)

Bei Deiner Formel wird der erste Eintrag gefunden.
So wird der letzte Eintrag gefunden:

=XVERWEIS(A1;Leute!B$2:.B$301;Leute!C$2:.C$301;"";0;-1)

Gruß

Edgar

AW: dynamische Such und Rückgabematrix im XVERWEIS

16.06.2025 18:04:11

BoskoBiati

Hi,

habe vergessen die Formel komplett zu korrigieren:

=XVERWEIS(A1;Leute!B:.B;Leute!C:.C;"";0;-1)

zum Nachlesen:

https://support.microsoft.com/de-de/office/trimrange-funktion-d7812248-3bc5-4c6b-901c-1afa9564f999

Gruß

Edgar

AW: dynamische Such und Rückgabematrix im XVERWEIS

16.06.2025 18:33:07

Christian

Hallo Edgar,

da hast du wohl leider was falsch verstanden.
Die Texte in Leute Spalte B haben keine Duplikate. Daher hilft mir die Suche nach ersten und letztem Eintrag wenig weiter.

Danke
Christian

AW: dynamische Such und Rückgabematrix im XVERWEIS

16.06.2025 17:57:02

daniel

Hi
leider ist mir nicht so klar, was du willst.
du suchst ja nach dem Inhalt der Spalte B, da ist es doch egal.
was genau stellst du dir denn in deinem Fall als Ergebnis vor?
Gruß Daniel

AW: dynamische Such und Rückgabematrix im XVERWEIS

16.06.2025 18:38:40

Christian

Hallo,

das Ergebnis soll sein, dass der Name und der Geburtstag der Person nur ausgegeben wird, wenn die Person zu den 300 jüngsten Personen in der Tabelle Leute gehört.
Mit dem Ziel dass bei den anderen Personen dann Name und Geburtstag leer bleibt und ich die Zeilen dann vorhabe zu löschen.

Ich weiß das geht auch mit PQ, ich mache es absichtlich mit Formeln. In den Tabellen gibt es im Originalen zuviel was sich nicht mit PQ erstellen lässt, wie z.b. von hand geschriebene Kommentare zu jeder Person.

Gruß
Christian

So ganz klar...

16.06.2025 18:27:02

Case

Moin Christian, :-)

... ist das mit den Doppelungen/Mehrfachen noch nicht. ;-)

Wenn du nur den Namen und das Geburtsdatum suchst, kannst du auch den Filter nehmen: ;-)

=FILTER(Leute!C:.D;Leute!B:.B=A1;"")

Es gibt ja an verschiedenen Stellen Doppelungen/Mehrfache - 47/48, 900/901, 1071/1072... ;-)

Da musst du noch etwas weiter ausholen. ;-)

Servus
Case

habe eine Lösung gefunden...

16.06.2025 19:29:09

Christian

=WENN(RANG(C1;Leute!D$2:D$350)=300;XVERWEIS(A1;Leute!B$2:B$350;Leute!C$2:C$350;"";0;1);"")

unter der Voraussetzung dass das 300 jüngste Datum nie mehr als 49 mal vorkommen wird, was ich ausschließe.

Aber falls jemand eine einfachere Idee hat gerne her damit.

Gruß
Christian

AW: habe eine Lösung gefunden...

16.06.2025 21:00:16

BoskoBiati

Hi,

in Deiner Tabelle AZN stehen zwei Formeln, die sich beide auf den Wert in A1 beziehen. Was soll denn da ermittelt werden? Diesen Wert gibt es nur einmal in der Tabelle Leute. Wie hängt das mit dem 300ten Wert in der Liste Leute zusammen?
Für mich ist das Ganze sehr rätselhaft.
Vielleicht kannst Du mich dazu mal aufklären?

Gruß

Edgar

AW: habe eine Lösung gefunden...

16.06.2025 21:14:35

daniel

kuckst du seinen Beitrag von 16:38:40 an mich.
er will nur dann ein Ergebnis, wenn die gesuchte Person zu den 300 jüngsten Personen der Liste gehört. Dabei ist es natürlich unschön, wenn von zwei Personen, die in der Liste sind und das selbe Geburtsdatum haben, eine ein Ergebnis bekommt und eine nicht.

direkt am Beispiel:
Person auf Platz 301 soll bei Suche in der Liste erscheinen.
Person auf Platz 302 soll bei Suche nicht mehr erscheinen, wenn sie älter ist als Person 301, aber sie soll erscheinen, wenn sie genauso alt ist wie Person 301.

AW: habe eine Lösung gefunden...

17.06.2025 00:56:44

Christian

Hallo Edgar,

ich versuch es mal so. Die Tabelle AZN soll eine Auflistung der 300 jüngsten Personen aus der Liste Leute sein.
Zur Liste Leute kommen wöchentlich Leute dazu. Da die beiden Formeln nur Name und Geburtstag ausgeben, wenn die Person zu den 300 jüngsten gehört, sehe ich an den leeren Zellen, welche ich nach dem "Update" aus AZN löschen kann, weil zu alt.

Wie ich bereits schon sagte, soweit noch mit PQ lösbar, was mit PQ nicht mehr geht, ist die im Bsp nicht , aber im Original vorhandene Spalte mit selbstgeschriebenen Kommentaren.
Daher mit Formeln.

Gruß
Christian

AW: habe eine Lösung gefunden...

16.06.2025 21:40:58

BoskoBiati

Hi,

das ist eine mögliche Variante, aber warum bezieht er sich auf die Nummer in A1, denn die gibt es offensichtlich nur einmal. Wie sollen dann mehrere Einträge angezeigt werden?
Wenn ich Deiner Theorie glauben darf, wobei ich nicht verstehe, welche Werte angezeigt werden sollen, der 300te, oder alle die jünger sind als der 300te, dann sollte doch das reichen:

=FILTER(Leute!A:.D;Leute!D:.D=KGRÖSSTE(Leute!D:.D;300))

=FILTER(Leute!A:.D;Leute!D:.D>=KGRÖSSTE(Leute!D:.D;300))

oder auch das:

=FILTER(Leute!C:.C;(Leute!D:.D>=KGRÖSSTE(Leute!D:.D;300))*(Leute!D:.D=C1))

=LET(xa;Leute!D:.D;FILTER(Leute!C:.C;(xa>=KGRÖSSTE(xa;300))*(xa=C1)))

Gruß

Edgar

AW: habe eine Lösung gefunden...

17.06.2025 01:02:01

Christian

Hallo Edgar, ja XVERWEIS kann unterscheiden zwischen mehreren Einträgen. ich nehme XVERWEIS weil ich mit der Sortieren und Filter und so nicht so vertraut bin. Sorry falls das zu Misverständnissen geführt hat, jetzt glaube ich wenigstens zu ahnen, wo deine Zweifel herkommen.

ZU deinen Formeln, die zweite Formel passt, auch wenn sie im Gegensatz zu meiner Tabelle noch die Überschriftenzeile mit einschließt.

Danke
Christian

AW: habe eine Lösung gefunden...

17.06.2025 00:57:58

Christian

Hallo Daniel,

besser hätte ich es nicht ausdrücken können. Danke
Christian

AW: dynamische Such und Rückgabematrix im XVERWEIS

16.06.2025 20:14:09

daniel

Hi

beispielsweise so:
diese Formel in AZN B1 eintragen
die Zelle AZN C1 muss leer sein, weil diese Formel auch gleich die Spalte C mit ausfüllt

=LET(a;VERGLEICH(Leute!$D$301-0,5;Leute!$D:$D;-1);

XVERWEIS($A1;Leute!$B$2:INDEX(Leute!$B:$B;a);Leute!$C$2:INDEX(Leute!$D:$D;a);"";0;1))

oder du arbeitest mit dem Filter (Sortierung weiterhin notwendig):

=FILTER(Leute!C:D;(Leute!B:B=A1)*(Leute!D:D>=Leute!D$301);"")

Gruß Daniel

AW: dynamische Such und Rückgabematrix im XVERWEIS

17.06.2025 01:06:49

Christian

Hallo Daniel,

auch deine beiden Vorschläge funktionieren einwandfrei.

Danke
Christian

AW: So ganz klar...

16.06.2025 18:41:15

Christian

Hallo Case,

ich hoffe so ist es verständlicher ausgedrückt, Name und Geburtstag sollen ausgegeben werden, wenn die Person zu den 300 jüngsten in der Tabelle Leute gehört, sonst nicht.
Das Problem ist jetzt nur, dass ausgerechnet das 300 jüngste Datum 2x vorkommt und deshalb 301 Personen zu den "300 jüngsten" gehören.

Hoffe das ist verständlicher.

Gruß
Christian

kleiner Nachtrag

16.06.2025 18:46:46

Christian

bevor du mich jetzt fragst, warum ich nicht einfach 301 in der Formel in 302 ändere...

Weil sich die Liste im Blatt Leute wöchentlich ändert und damit auch die Anzahl des Vorkommens des 300 jüngsten Datums.

Gruß
Christian

Vielleicht habe...

16.06.2025 19:15:56

Case

Moin Christian, :-)

... ich dich richtig verstanden? ;-)
https://www.herber.de/bbs/user/177782.xlsx

Wenn das mit den Punkten im Bereich bei dir nicht geht, gib den Bereich entsprechend an. ;-)

Servus
Case

AW: Vielleicht habe...

16.06.2025 19:33:55

Christian

Hallo Case,

die erste Formel funktioniert, die zweite produziert bei mir Überlauf

Aber auch wenn du dir da sehr viel Mühe gegeben hast, ich kann kaum nachvollziehen, was die Formel macht.
Schau dir doch bitte mal meinen Lösungvorschlag an, den ich in der Zwischenzeit gepostet habe, vlt. kannst du ja dazu was sagen.

Gruß
Christian

tausendmal sorry

17.06.2025 01:05:12

Christian

Ja du hast recht mit dem Überlauf, ich habe die zweite Formel rechts neben der ersten eingefügt.

Jetzt wo ich sie ganz ohne die erste eingefügt habe, funktioniert auch die zweite Formel bei mir.

Danke
Christian

Also ich bin da...

16.06.2025 20:09:24

Case

Moin Christian, :-)

... so rangegangen: ;-)

=INDEX(Leute!D2:D5000;300) - gibt mir das Geburtsdatum bei 300 aus - also Zeile 301. ;-)

=ZÄHLENWENN(INDEX(Leute!D2:D5000;300+1):Leute!D2:D5000;INDEX(Leute!D2:D5000;300)) - gibt mir aus wieviel Mehrfache danach kommen. ;-)

=FILTER(Leute!C2:D5000;(Leute!B2:B5000=A1)*(ZEILE(Leute!D2:D5000) =302);"") - gibt mir Name und Geburtstag aus. ;-)
Die 302 berechnen wir ja. Das ist nur die LET-Formel aufgedröselt.

RECHTS davon darf nichts stehen - sonst #ÜBERLAUF! ;-)

Das eben in eine Formel gepackt mit LET. ;-)

Geht es anders - ja. ;-)
Geht es kürzer - ja. ;-)

Aber es funktioniert - denke ich. ;-)

Servus
Case

AW: Also ich bin da...

17.06.2025 01:21:51

Christian

und danke auch für die ausführliche Erklärung.

Ich versuche mal mit eigenen Worten zusammenzufassen, korrigier mich bitte wenn ich falsch liege

deine Originalformel

=LET(v;Leute!D:.D;w;300;x;INDEX(v;w);y;ZÄHLENWENN(INDEX(v;w+1):v;x);z;w+y;FILTER(Leute!C:.D;(Leute!B:.B=A1)*(ZEILE(Leute!D:.D)=z);""))

1. v;Leute!D:D

Definition von der Variable v, die den Bereich mit den Geburtsdaten definiert

2. w;300

ich setze den Startpunkt auf die 300. Zeile von v, also D301.

3. x;INDEX(v;w)

Ich hole mir den Wert aus D301 und speicher ihn in x.

4. y;ZÄHLENWENN(INDEX(v;w+1):v;x)

Zählen wie oft das gleiche Datum x nach Zeile 301 erneut vorkommt, INDEX(v;w+1):v heißt von D302 bis zum Ende, ZÄHLENWENN(...;x) zählt die Anzahl des Datums

5. z;w+y

Bestimmung der letzten Zeile mit dem Datum

6. FILTER(...)

gibt alle Zeilen mit übereinstimmendem Datum aus, "" falls es keine Übereinstimmung gibt.

Hoffe ich habe mich jetzt nicht komplett blamiert.

Gruß und danke nochmal
Christian

Das geht auch...

16.06.2025 20:45:42

Case

Moin Christian, :-)

... ganz gut mit Power Query. ;-)

Servus
Case

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