Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
18.04.2024 19:30:20
VBA - Tabelle mit Mannschaftsergebnissen sortieren
18.04.2024 18:04:29
Formel fehler Wert#
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

2 SVERWEISE (2. durchsucht Liste des 1.)

2 SVERWEISE (2. durchsucht Liste des 1.)
20.01.2014 13:02:43
Thomas
Hallo liebes Forum!
Habe ein Problem zu lösen, dass ich selbst mit den Beiträgen und Tippsammlungen hier noch nicht in den Griff bekommen habe.
Konkret geht es darum, dass ich mittels SVERWEIS Spalteninfos eines 2. Tabellenblattes hinzufüge. Allerdings gibt es das Suchkriterium mehrmals und erst eine weitere Spalte macht es eindeutig. Das zweite Kriterium ist aber eine Zahl und daher nicht zum Verketten geeignet, als auch das 2. Suchkriterium dazwischen liegt.
Lange Rede, kurzer Sinn, eine Beispieltabelle:
Codetabelle Code 1    Kilometer    Code 2 1       2,35 1001 1 5,78 1002 1 11,93 1003 1 34,89 1004 2 1,49 2001 2 3,89 2002 3 33,39 3001 Suche soll zB bei folgenden Eingaben das Ergebnis (Code 2) liefern: 
Code 1     Kilometer     Code 2
1          3,78          1002
Schwierig ist also die Abstimmung mit dem Kilometer, weil dieser nicht mit der Eingabstabelle übereinstimm(en muss) sondern immer kleiner gleich den vorangegangenen Wert prüfen soll.
Wer kann mir bei diesem Problem helfen?
Herzlichen Dank
Thomas

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: 2 SVERWEISE (2. durchsucht Liste des 1.)
20.01.2014 13:42:30
Rudi
Hallo,
Liste muss nach Code1/ km sortiert sein.
ABCDEFG
1Code 1KilometerCode 2    
212,351001 22,51002
315,781002    
4111,931003    
5134,891004    
621,492001    
723,892002    
8333,393001    

ZelleFormel
G2=INDEX(C2:C10;VERGLEICH(F2;BEREICH.VERSCHIEBEN(B1;VERGLEICH(E2;A2:A10;);;ZÄHLENWENN(A2:A10;E2)))+1)

Gruß
Rudi

Anzeige
AW: 2 SVERWEISE (2. durchsucht Liste des 1.)
20.01.2014 13:46:37
Thomas
Wahnsinn Rudi, danke für die 1A-Lösung und vor allem für die Geschwindigkeit.
Mit Index und Vergleich habe ich mich auch beschäftigt, kannst du vielleicht noch ganz kurz erläutern, was es mit Bereich.Verschieben auf sich hat?
Wirklich, 1000 Dank!
Thomas

AW: 2 SVERWEISE (2. durchsucht Liste des 1.)
20.01.2014 13:48:40
Thomas
Wobei Rudi, das Ergebnis stimmt aber dann doch nicht ganz, denn bei 2 und 2,5 müsste er doch 2002 und nicht 1002 finden ...

AW: 2 SVERWEISE (2. durchsucht Liste des 1.)
20.01.2014 14:29:10
Thomas
Wobei Rudi, das Ergebnis stimmt aber dann doch nicht ganz, denn bei 2 und 2,5 müsste er doch 2002 und nicht 1002 finden ...

INDEX bis zum umfallen
20.01.2014 13:42:41
Klaus
Hallo Thomas,
https://www.herber.de/bbs/user/88898.xlsx
meine Formel findet bei 1 & 5,77 die 1002, bei 1 & 34,90 gibt es einen Fehler (da das größer ist als jeder Wert in Code 1).
Dein Beispiel ist aufsteigend sortiert, zunächst nach Code1 dann nach Kilometer. Ich gehe davon aus, dein Master ist genauso sortiert, sonst klappt es nicht.
Grüße,
Klaus M.vdT.

Anzeige
AW: INDEX bis zum umfallen
20.01.2014 14:01:52
Thomas
Ja, das sind eine Menge Index-Formeln ... da steige ich noch nicht durch, das Ergebnis passt prinzipiell, außer dass der Kilometer, im Beispiel etwa Code 1 (1), Kilometer (5,78) schon in die nächste Zeile bei Code 2 (1003) landet und nicht noch bei 1002.
Wow, danke auf jeden Fall für deine prompte Hilfe - vielleicht findest du ja für das letzte Quentchen auch noch eine Lösung.
Das Forum ist hier echt Spitze.

AW: INDEX bis zum umfallen
20.01.2014 14:34:19
Klaus
Hallo Thomas,
"Kilometer (5,78) schon in die nächste Zeile bei Code 2 (1003) landet und nicht noch bei 1002"
ja, so funktioniert VERWEIS ... Jetzt müsste man ein ganz langes WENN drum herum gestalten, ob es den exakten Wert gibt. Oder Trick 17: Statt 5,78 VERGLEICHen wir einfach 5,77999999999999 und dann klappt es auch damit.
Ändere die Formel hier:
=INDEX(INDEX(C:C;VERGLEICH(F2;A:A;)):INDEX(C:C;ZÄHLENWENN(A:A;F2)-1+VERGLEICH(F2;A:A;)); 1+WENNFEHLER(VERGLEICH(F3;INDEX(B:B;VERGLEICH(F2;A:A;)):INDEX(B:B;ZÄHLENWENN(A:A;F2) -1+VERGLEICH(F2;A:A;));1);0))
entsprechend ab:
=INDEX(INDEX(C:C;VERGLEICH(F2;A:A;)):INDEX(C:C;ZÄHLENWENN(A:A;F2)-1+VERGLEICH(F2;A:A;)); 1+WENNFEHLER(VERGLEICH(F3-1/1000000000000;INDEX(B:B;VERGLEICH(F2;A:A;)):INDEX(B:B; ZÄHLENWENN(A:A;F2)-1+VERGLEICH(F2;A:A;));1);0))
Grüße,
Klaus M.vdT.

Anzeige
AW: INDEX bis zum umfallen
20.01.2014 15:05:00
Thomas
Danke Klaus, toller Trick mit der Verringerung um einen sehr geringen Anteil - jetzt passt's ganz genau!

Danke für die Rückmeldung! owT.
20.01.2014 15:06:41
Klaus
.

AW: INDEX bis zum umfallen
20.01.2014 14:09:08
Thomas
Ok, das Anpassen der Zeile war einfach (statt -1, einfach -2) allerdings klappt das nicht für den letzten Kilometer einer Liste, das wäre natürlich praktisch.
Was allerdings super in deiner ersten Formel ist: sollte ein Kilometer größer als ein vorhandener abgefragt werden, gibt's einen Fehler! :-)

eine kurze INDEX() -Formel ... und alternativ ...
20.01.2014 18:26:23
neopa
Hallo Thomas,
... auch vorausgesetzt, dass die Daten so sortiert sind, wie Klaus und Rudi schon schrieben, würde ich hier eine MATRIXFormel wie untenstehend in I5 einsetzen. Alternativ etwas länger, dafür mit "Abfangung" unkorrekter Eingaben die Formel in I5.
 ABCDEFGHI
1CodeKilometer   Code 2      
212,351001    Code1
315,781002    Kilometer5,78
4111,931003      
5134,891004    Code 21002
621,492001     1002
723,892002      
8333,393001      
9         

Formeln der Tabelle
ZelleFormel
I5{=INDEX(C:C;MIN(WENN((A2:A99=I2)*(B2:B99>=I3); ZEILE(2:99))))}
I6{=WENN(ISTNV(VERGLEICH(I2;A:A;)); "Code ?";WENNFEHLER(--INDEX(C:C;MIN(WENN((A2:A99=I2)*(B2:B99>=I3); ZEILE(2:99)))); "km-Angabe außerhalb des def. Bereiches"))}
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

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige