Zeilennummer anhand von 2 (mehrfach vorkommenden) Suchkriterien ausgeben (Formel)
#1
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: Huh
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


Angehängte Dateien
.xlsx   Test_Index.xlsx (Größe: 8,98 KB / Downloads: 8)
Zitieren to top
#2
Hi Philipp,

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

Bex

BCDEFG
2WBSWGSTot. QtyQty ScrapHS
3AAAAZeile1AAAA5
4AaccZeile2Aacc
5AABEZeile3AABE
6ATHAZeile4ATHA
7AADAZeile5AADA
8AHRAZeile6AHRA
9ATVAZeile7ATVA
10AZZAZeile8AZZA
11BADAZeile9BADA
12BKKLZeile10BKKL
13BKAIZeile11BKAI
Formeln der Tabelle
ZelleFormel
F3=B3&C3
G3=VERGLEICH("A"&"ADA";$F$3:$F$20;0)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
Zitieren to top
#3
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'
BCDEFGHI
2WBSWGSTot. QtyQty Scrap
3AAAA 32232
4AADA 32
5AABE
6ATHA
7AKIL
8AHRA
9ATVA
10AZZA
11BADA
12BKKL
13BKAI
14BIPÜ
15BGSR
16BTSA
17CBCF
18CADA
19CISH
20COPA

ZelleFormel
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)
cu Jörg eine Rückmeldung wäre ganz reizend XL2003 bis XL2013
[-] Folgende(r) 1 Benutzer sagt Danke an Jockel für diesen Beitrag:
  • Phil_Be
Zitieren to top
#4
(21.07.2017, 22:15)Jockel schrieb: 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..  Huh


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. Smile)


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..
Zitieren to top
#5
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)
Zitieren to top
#6
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?
cu Jörg eine Rückmeldung wäre ganz reizend XL2003 bis XL2013
Zitieren to top
#7
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
Zitieren to top
#8
Hallo, dann stelle doch bitte deine INDIREKT() Variante vor... ... ich bin erst ab Mittag wieder online muss gleich weg...
cu Jörg eine Rückmeldung wäre ganz reizend XL2003 bis XL2013
Zitieren to top
#9
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 
Smile

Lösung werde ich logischerweise nachreichen!
Zitieren to top
#10
Hallo, schau mal, ob dir das weiterhilft..:

https://www.youtube.com/watch?v=OJbPAtjC81Y
cu Jörg eine Rückmeldung wäre ganz reizend XL2003 bis XL2013
[-] Folgende(r) 1 Benutzer sagt Danke an Jockel für diesen Beitrag:
  • Phil_Be
Zitieren to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste