Zeilennummer anhand von 2 (mehrfach vorkommenden) Suchkriterien ausgeben (Formel)

[Phil_Be]

Hallo liebe Excelprofi´s,

ich stehe vor einem, für mich kopfzerbrechendem, Excel Problem. Ich verweise schonmal auf den Anhang (Excel Tabelle).

Folgender Sachverhalt: 
Mein Ziel ist es, über =Index(Matrix;Zeilennummer;Spaltennummer) einen Zellwert auszulesen (Zahl "32" in der Spalte "qty. scrap"). Ist ja soweit recht simpel. Die Matrix stellt erstmal kein Problem dar (da hätte ich aber später noch eine Frage zu). 

Über =Summenprodukt habe ich die oberste Zeile meiner Tabelle (die oberste Zeile ist variabel was die position der spalten angeht) aus der ich auslesen möchte, nach einem bestimmten Text gesucht und mir dann, wenn der Text gefunden wurde, die Spaltennummer in eine Zelle ausgeben lassen. Möchte ich der übersicht halber jetzt nicht drauf eingehen. Klappt.

Fehlt nun noch die Zeilennummer meiner Zeile, die es zu finden gilt. Um diese zu ermitteln, will ich der Formel 2 Suchkriterien vorgeben. 
Das erste Suchkriterium wäre "A", welches in der WBS Spalte gesucht werden muss.
Das zweite Suchkriterium wäre "ADA", welches in der WGS Spalte gesucht werden muss.
 
Beide Kriterien können in ihrer jeweiligen Spalte mehrmals vorkommen; jedoch ist es ausgeschlossen, das es zu beispielweise dem Kriterium "A" zweimal das Kriterium "ADA" gibt. 
Mit anderen Worten, jede Zeile ist für sich einmalig. Zu meinem WBS "A" kann nur einmal das Suchkriterium "ADA" gefunden werden und andersrum. 
Ein zweites "ADA" aus der WGS Spalte würde sich dann auf ein anderes WBS beziehen (B oder C, wie in der Tabelle dargestellt).

Und hier komme ich nicht weiter:
ERST wenn die passende Zeile gefunden wurde, also die Zeile, in der bei in der Spalte WBS "A" und in der Spalte WGS "ADA" steht, soll mir die Formel die Zeilennummer zurückgeben. 
Dabei möchte ich die Spalte in der gesucht wird, über die Spaltennummer, die ich über =summenprodukt ermittelt habe, ansprechen (Sagen wir mal WBS hat die Spaltennummer 1, WGS die Spaltennummer 2). Hintergrund dabei ist eine Tabelle, die zwar immer die gleichen Spalten enthält, diese aber an manchmal anderer Stelle. 
Das ganze möchte ich ohne VBA lösen. 

Diese Zeilennummer (bzw. formel, die mir die zeilennummer ausgibt) möchte ich direkt in meine oben erwähnte Indexformel einpflanzen, damit mir die indexformel dann die zahl "32" aus der spalte "qty. scrap" zurückgibt.



Ich hoffe ich konnte mein Problem verständlich darlegen und freue mich, wenn ihr mir weiterhelfen könnt.


Vielen Dank und einen schönen Abend, 
Philipp

[WillWissen]

Hi Philipp,

ich würde das mit einer Hilfsspalte, die du einfach ausblendest, lösen:

Bex

	B	C	D	E	F	G
2	WBS	WGS	Tot. Qty	Qty Scrap	HS
3	A	AAA		Zeile1	AAAA	5
4	A	acc		Zeile2	Aacc
5	A	ABE		Zeile3	AABE
6	A	THA		Zeile4	ATHA
7	A	ADA		Zeile5	AADA
8	A	HRA		Zeile6	AHRA
9	A	TVA		Zeile7	ATVA
10	A	ZZA		Zeile8	AZZA
11	B	ADA		Zeile9	BADA
12	B	KKL		Zeile10	BKKL
13	B	KAI		Zeile11	BKAI

Formeln der Tabelle

Zelle Formel F3 =B3&C3 G3 =VERGLEICH("A"&"ADA";$F$3:$F$20;0)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[Jockel]

Hallo, ich weiß nicht, ob ich das richtig verstanden habe..:

oder auch ..:

Code:

=AGGREGAT(15;6;E3:E20/(B3:B20="A")/(C3:C20="ADA");1)

Arbeitsblatt mit dem Namen 'Bex'
	B	C	D	E	F	G	H	I
2	WBS	WGS	Tot. Qty	Qty Scrap
3	A	AAA				32	2	32
4	A	ADA		32
5	A	ABE
6	A	THA
7	A	KIL
8	A	HRA
9	A	TVA
10	A	ZZA
11	B	ADA
12	B	KKL
13	B	KAI
14	B	IPÜ
15	B	GSR
16	B	TSA
17	C	BCF
18	C	ADA
19	C	ISH
20	C	OPA

Zelle	Formel
G3	=VERWEIS(2;1/(B3:B20&"*"&C3:C20="A"&"*"&"ADA");E3:E20)
H3	=SUMMENPRODUKT(N(ZEILE(A1:A18)*(B3:B20="A")*(C3:C20="ADA")))
I3	=INDEX(E3:E20;H3)

[Phil_Be]

Hallo, ich weiß nicht, ob ich das richtig verstanden habe..:

Hallo Jockel, 
vielen Dank für deinen Vorschlag. Der kommt meiner Vorstellung schon recht nahe. :28:  Ich habe deine Lösung soweit nachvollzogen. Allerdings müsstest du mir dein =summenprodukt genauer erklären, genauer den Bereich "ZEILE(A1:A18)"..
Deine =summenprodukt-Formel funktioniert nach meiner Vorstellung, da diese mir die Zeilennummer ausgibt. 

Nur ist es in meinem Fall so, das ich diese Formel gerne so umbauen würde, das ich die Spalte in der z.B. nach dem "A" bzw. dem "ADA" gesucht wird, variabel ansprechen will, am besten über die Spaltennummer. Denn beispielsweise die Spalte WBS muss nicht immer im Bereich B3:B20 sein, sondern kann sich auch mal bei D3: D20 befinden.. Das bedeutet: Diese Bereiche möchte ich variabel ansprechen.  Dass ist das, wo ich hänge.. 


Der Name WBS bleibt dabei aber vorhanden, sodass ich quasi die erste Zeile meiner Tabelle (die zeile mit den spaltennamen wbs usw.) nach diesem schlagwort durchsuche und mir dann die entsprechende spaltennummer ausgeben lassen. 
Und eben diese spaltennummer möchte ich in bspw. deine =summeproduktformel einbauen, damit ich die "32" auch auslesen kann, wenn sich die spalten verschieben.


Für meine Zelle, in der ich die 32 ausgeben lassen will, würde ich wie beschrieben =Index(matrix;zeilennummer;spaltennummer) nutzen. Die spaltennummer wäre hier immer die nummer der "qty. scrap" spalte, die matrix ist erstmal klar und über die zeilennummer reden wir gerade. :))


Beste Grüße, 
Philipp

EDIT: Kann ich da mit =adresse(zeile;spalte) arbeiten?? Kann ich daraus eine Matrix bilden, die durchsucht werden soll? darin könnt ich ja dann variabel zeile und spalte angeben..

[Phil_Be]

Quasi so:

Code:

Anstatt  =SUMMENPRODUKT(N(ZEILE(A1:A18)*(B3:B20="A")*(C3:C20="ADA")))

schwebt mir das vor =SUMMENPRODUKT(N(ZEILE(A1:A18)*(adresse(zeile;spalte):adresse(zeile;spalte)="A")*(adresse(zeile;spalte):adresse(zeile;spalte)="ADA")))

Geht das überhaupt? 

Grüße, 
Philipp


(sry für den doppelpost; editieren war weg)

[Jockel]

hallo Philipp, ein zwei konkrete Beispiele in (d)einer (Beispieldatei) wären nich schlecht, wo auch zu erkennen ist was das Suchkriterium ist, wo es steht z.B. in extra Zellen und wo dein Ergebnis hin soll. Die Funktion ADRESSE() und deren Funktionsweise hast du dir aber noch nicht wirklich angeschaut, oder?

[Phil_Be]

Guten Morgen Jockel, 

nein, habe ich mir nicht angeguckt. Hab dieses Problem allerdings gestern gelöst bekommen über indirekt().

Was nett wäre, wenn du mir nochmal erklären könntest, was es mit (A1:A18) aus deiner summenproduktformel auf sich hat.. Wie würde diese Formel (bzw. Dieser Bereich) aussehen, wenn sich meine Tabelle von A1:L1338 erstreckt, statt von B3:E20? Das wäre meine letzte Hürde..  :05:

Gruß, Philipp

[Jockel]

Hallo, dann stelle doch bitte deine INDIREKT() Variante vor... ... ich bin erst ab Mittag wieder online muss gleich weg...

[Phil_Be]

Die Stelle ich selbstverständlich gerne vor! Das hatte ich eh vor.
Bekomme nur die Daten leider nicht vom Firmenrechner rüber ins Forum, Anmeldung ist gesperrt.

Was mir gerade helfen würde, wäre eine Erklärung deiner summenproduktformel 
:)

Lösung werde ich logischerweise nachreichen!

[Jockel]

Hallo, schau mal, ob dir das weiterhilft..:

https://www.youtube.com/watch?v=OJbPAtjC81Y