Verweis-Funktion funktioniert nur im begrenzten Bereich

[Mr.TomTom]

Hallo Leute!
Mich quält die "Verweis"-Funktion, weil sie offensichtlich nur sehr begrenzt funktioniert und ich komme einfach nicht dahinter, woran das liegen könnte. Dabei ist die Geschichte gar nicht mal komplex.

Ich nutze die Spalten A um dort von Zelle A3 bis Zelle A23 die Zahlen 1. . .21 aufsteigen abzutragen. In der Spalte B sind nun zu jeder dieser einzelnen Nummern individuelle Werte eingetragen. Aktuelle Werte im Bereich 0 . . . 1548.

In der Spalte D identifiziere ich nun die jeweils fünf größten Werte absteigend und angefangen in D3mit =KGRÖSSTE(B$3:B$23,1), =KGRÖSSTE(B$3:B$23,2) bis eben =KGRÖSSTE(B$3:B$23,5). Soweit so gut und alles kein Hexenwerk und tadelloser Funktion.

Jetzt zum Knackpunkt: In der Spalte C verwende ich nun die Verweis-Funktion wie folgt: =VERWEIS(D3,B$3:B$23,A$3:A$23). =VERWEIS(D4,B$3:B$23,A$3:A$23), =VERWEIS(D5,B$3:B$23,A$3:A$23), =VERWEIS(D6,B$3:B$23,A$3:A$23) und =VERWEIS(D7,B$3:B$23,A$3:A$23), um den fünf identifizierten Werten aus der Spalte B die zugehörige Zahle (1 . . .21) aus der Spalte A zuzuordnen.

Auch das sollte eiegentlich kein Hexenwerk sein und ist ziemlich simple, allerdings funktioniert die korrekte Zuordnung nur für die Zeilen 3 und 4. Ab Zeile 5 wird mir, aus welchen Grund auch immer, stests der Wert bzw. die Zahl 11 zurückgegeben. Der Zahl 11 in Spalte A ist in Splate B allerdings nur ein Wert in Höhe von 38 zugeordnet.

Ich verstehe es nicht . . .
Mache ich irgendeinen Fehler mit meiner Abfrage und sehe den Wald vor lauter Bäumen nicht mehr???

Test_Verweis.ods

(11.94 KiB) 191-mal heruntergeladen

Kann mich irgendjemand aus meiner Verzweifelung retten?

Aller besten Dank und Grüße,
Mr. TomTom

[echo]

Hallo Tom
In Zeile 14 wird der Wert in Spalte B größer wie das Suchkriterium.

Verweis sucht in der Suchspalte nur so lange weiter wie der nächste Wert in der Spalte kleiner oder gleich dem Suchkriterium ist. Wird der Wert größer (wie in B14) bricht Verweis die Suche ab und zeigt den Inhalt der Zelle an.

Deine Liste ist für Verweis schlicht weg nicht geeignet.
Wegen dem oben genannten Grund ist zwingend eine aufsteigende Sortierung der Suchspalte notwendig, sonnst kommt Murks raus.

Verwende statt dessen eine Kombination aus Index und Vergleich.
Vergleich lässt sich so einstellen, das nach einer GENAUEN Übereinstimmung gesucht wird. Da ist sie Sortierung dann nicht notwendig
C3:
=INDEX($A$3:$A$23;VERGLEICH(D3;$B$3:$B$23;0))

Gruß Holger

PS
Sortiere doch deine Liste zum Spaß mal nach Spalte B aufsteigend

[karolus]

Hallo

…andererseits könnte man auch gleich das ganze nach Spalte B absteigend sortieren, hätte damit die gesuchten Werte oben in der gewünschten Reihenfolge und bräuchte infolgedessen kein zusätzliches Formel-geschwurbel.

[Mr.TomTom]

Hallo Karolus, Echo!
Allerbesten Dank!
Das ging fix.
Werde das nicht mehr heute, aber dann hoffentlich gleich morgen testen können.
Ich habe diesen konkreten Fall jetzt noch nicht unter Excel getestet, verwende die Verweis-Funktion dort beruflich aber häufiger und meine, ein solches Phänomen ist mir da nicht nicht begegnet. Oder sollte ich mich da täuschen?
Wie auch immer, bei der privaten Nutzung ist mir LibreOffice deutlich sympathischer als die MS-Produkte. Insofern werde ich mein Wissen in dem Bereich wohl noch ausdehnen müssen.

Beste Grüße.
Mr.TomTom

[echo]

Oder sollte ich mich da täuschen?

JA, Du täuscht dich ! VORSICHT MIT DEM ERGEBNIS !

Anders wie zB. in SVERWEIS oder VERGLEICH kennt VERWEIS den 4. Parameter nicht wo man anweisen kann genau zu suchen.
Auch dort währe eine Sortierung zwingend erforderlich, wenn man den Parameter auf WAHR (1) setzt.

Also bei VERWEIS immer sortieren !!

Gruß Holger

[Mr.TomTom]

Ja, Teufel!
Ich täusche mich tatsächlich!
Warum ist mir das bislnag denn noch nie aufgefallen?
Und jetzt stolpere ich bei einer solch simplen Geschichte darüber.
Das muss ich mir morgen im Büro nochmal genauer ansehen.

Wie auch immer, Deine Index/Verweis-Kombination löst das Problem wie gewünscht.

Noch mal allerbesten Dank!

Gruß,
Mr.TomTom

[Mr.TomTom]

Hallo "Echo", Holger!
Ich bn noch mit einem anderen Problem konfrontiert . . . .
Meine auszuwertende Liste ist etwas länger als die der zuvor angehängten Test-Datei.
Sie enthält in der Spalte A auch keine reinen Zahlen, was aber unerheblich sein sollte.
Ich hatte das lediglich der Einfachheit halber zur Poblemerläuterung beispielhaft entsprechend angelegt.

Das Problem, welches sich auftut ist, wenn sich mehrere identische Zahlen in Spalte D befinden.
Dann bricht die Suche bei der derzeitigen Formelgestaltung immer an der ersten gefundenen Übereinstimmung ab und gibt den zugehörigen Inhalt aus der Spalte A in der Spalte C wieder. Ich würde aber gerne den Inhalt der nächsten Zelle in A sehen, die eben zufällig den gleichen Wert in Spalte D besitzt wie die erste gefundene.

Hätte ich über VLOOKUP und MATCH (sorry, EXCEL Funktionen, die Pendants in CALC muss ich noch recherchieren) eine Chance?

Das aufsteigende Sortieren der Spalte B habe ich noch nicht getestet, weil die Daten in A und B von extern importiert werden. Da müsste ich dann am Import der Daten "stricken", was mir im Augenblick komplexer erscheint.

Besten Dank und Gruß,
Dieter

[echo]

Hallo Dieter
die einfachste Version ist meist, wenn du dafür sorgst das es beim Suchkriterium keine Duplikate gibt, ohne das sich diese in der Reihenfolge ändern. Dazu kann man zB. die Zeilennummer/100000 als sehr kleinen Wert addieren.
Die Reihenfolge der Werte bleibt erhalten aber auch Duplikate unterscheiden sich.

Es geht auch komplett per Matrix-Formel
Ich hänge mal ein Beispiel an
Gruß Holger

[Mr.TomTom]

Hallo Holger!
Die Idee mit der Zeilennummer dividiert durch 100000 ist natürlich perfekt.
Sehr clevere Idee! ich sehe mir aber auch mal die Matrix-Formel in Deinem Beispiel an. Ich denke das ist dass, was Du angehängt hattest. Richtig? Da kann ich bestimm noch einiges lernen und das Verständnis vertiefen.

Wiederum besten Dank und einen ebensolchen Gruß,
Dieter