Gleichen, nächst höheren Wert bei zwei Suchkriterien

[HeilyDioh]

Hallo zusammen,

ich arbeite seit gut einer Woche an einer Lösung, doch leider finde ich keine..Ich hoffe, ihr könnt mir helfen!

Folgende Problemstellung:

Suchwert = Mengenstaffel (definiert)

Spalte A = 1. Suchkriterium,  Werte können mehrfach gleich vorkommen (Materialnummer)
Spalte B = 2. Suchkriterium, Werte können nur 1 mal vorkommen, abhängig von Anzahl 1. Suchkriterium (Mengenstaffel)
Spalte C = Ergebnis (Preis)

Das Problem liegt darin, dass im 2. Suchkriterium entweder

1. der gleiche Suchwert in Spalte B gefunden werden soll
2. der nächst größere Wert in Spalte B gefunden werden soll, falls nicht vorhanden
3. oder falls Suchwert >MAX-Wert in Spalte B, dann soll der MAX-Wert aus Spalte B gefunden werden

Ich habe hierzu eine Beispieldatei hinzugefügt.

Danke allen schon mal vorab!

Grüße Heily

[echo]

Hallo
In Spalte A / B / C stehen keine Werte

ein Suchkriterium steht in der Zelle J3.
Wenn ich das richtig verstehe, so soll der gesuchte Werte aus Spalte F größer oder gleich diesem Wert sein.

Aber
in welcher Zelle steht das andere Suchkriterium?
Wo soll das andere Suchkriterium gesucht werden? in Spalte E?

Gruß Holger

[HeilyDioh]

Hallo Holger,

vielen Dank für deine Nachricht.
Ich habe eben nochmal die Beispieldatei aktualisert und hochgeladen.

Das 1. Suchkriterium ist J3
Das 2. Suchkriterium ist I3

Wenn die Materialnummer gefunden wurde, dann soll die dazugehörige Mengenstaffel gefunden werden. Allerdings sind diese unterschiedlich. Falls in Spalte F der Wert in J3 1:1 gefunden wird, dann soll der Preis ausgegeben werden. Falls J3 > als MAX-Wert in Spalte F, dann soll der Preis zum MAX-Wert der unter I3 gesuchten Mengenstaffel wiedergegeben werden.Falls J3 zwischen 2 Mengenstaffeln liegen sollte, dann den Preis zur nächst höheren Preisstaffel...

Ich hoffe, ich konnte es erklären.

Danke und Gruß

[HeilyDioh]

Hallo zusammen,

hier mal ein Beispiel, wie die Ergebnisse lauten sollten...

[shift-del]

Moin

In Tabelle1 müssen die Daten sortiert sein:
1. Materialnummer aufsteigend sortiert
2. Mengenstaffel aufsteigend sortiert

Code:

=INDEX(Table1[Preis];AGGREGAT(15;6;(ZEILE(Table1[Preis])-ZEILE(Table1[[#Kopfzeilen];[Preis]]))/(Table1[Materialnummer]=I3)/(Table1[Mengenstaffel]>=J3);1))

[echo]

Hallo
ich würde das dann aber in drei kleinen Schritten lösen. Finde ich eleganter wie so eine Mammut-Formel.

Gruß Holger

Nachtrag:
Enthält Matrixformel: Umrandende { geschweifte Klammern } nicht mit eingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

[Jockel]

Hallo, warum nicht einfach(er)..?

PHP-Code:

=MAX(INDEX(G2:G10*((E2:E10=I3)*F2:F10>=J3);;)) 


oder..:

PHP-Code:

=MMULT(AGGREGAT({14;15};6;G2:G10/(F2:F10>=J3)/(E2:E10=I3);1);{1.-1}) 


[echo]

3. oder falls Suchwert >MAX-Wert in Spalte B, dann soll der MAX-Wert aus Spalte B gefunden werden

Hallo Jörg ,die erste Formel erfüllt die Bedingung nicht (Ergebnis 0), die Zweite gibt dann #Zahl zurück
Gruß Holger

[Jockel]

Hi Holger, okay, okay, aber ich denke trotzdem relativ einfach machbar..:

PHP-Code:

=MAX(INDEX(G2:G10*((E2:E10=I3)*F2:F10>=MIN(J3;MAX(INDEX(F2:F10*(E2:E10=I3);))));;)) 


Arbeitsblatt mit dem Namen 'Sheet1'
	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Materialnummer	Mengenstaffel	Preis										AAB
2	AAB	100	5,00 €		Materialnummer	Mengenstaffel	Preis		Max-Wert (AAB)	>= 501		gefunden in Zeile:	EER
3	AAB	80	6,00 €		AAB	501	2,00 €		500	100.000		3	QQW
4	AAB	500	2,00 €										UUT
5	EER	350	5,00 €			Alles in einer Formel	2,00 €
6	EER	150	10,00 €				2,00 €
7	PPU	200	9,00 €
8	QQW	40	100,00 €
9	QQW	1000	30,00 €
10	UUT	200	10,00 €
11

Zelle	Formel
M2	="Max-Wert ("&I3&")"
N2	=">= "&J3
K3	{=INDEX(Table1[Preis];VERGLEICH(I3&"#"&WENN(N3>M3;M3;N3);Table1[Materialnummer]&"#"&Table1[Mengenstaffel];0))}
M3	{=MAX((Table1[Materialnummer]=I3)*Table1[Mengenstaffel])}
N3	{=MIN(WENN((Table1[Materialnummer]=I3)*(Table1[Mengenstaffel]>=J3);Table1[Mengenstaffel];100000))}
P3	{=VERGLEICH(I3&"#"&WENN(N3>M3;M3;N3);Table1[Materialnummer]&"#"&Table1[Mengenstaffel];0)}
K5	{=INDEX(Table1[Preis];VERGLEICH(I3&"#"&WENN(MIN(WENN((Table1[Materialnummer]=I3)*(Table1[Mengenstaffel]>=J3);Table1[Mengenstaffel];100000))>MAX((Table1[Materialnummer]=I3)*Table1[Mengenstaffel]);MAX((Table1[Materialnummer]=I3)*Table1[Mengenstaffel]);MIN(WENN((Table1[Materialnummer]=I3)*(Table1[Mengenstaffel]>=J3);Table1[Mengenstaffel];100000)));Table1[Materialnummer]&"#"&Table1[Mengenstaffel];0))}
K6	=MAX(INDEX(G2:G10*((E2:E10=I3)*F2:F10>=MIN(J3;MAX(INDEX(F2:F10*(E2:E10=I3);))));;))

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[HeilyDioh]

Hallo shift-del ,

vielen Dank! Die Lösung werde ich mir anschauen!