Variable Suchmatrix in Index-Vergleich-Funktion über 2 Tabellen

[SliQz]

Hallo zusammen,

nachfolgend möchte ich mein Problem beschreiben, zu dem ich bisher keine Lösung habe.
Zur Veranschaulichung habe ich die Situation beispielhaft in angehängten Excel-Dateien nachgestellt.

Voraussetzungen:

Excel 2013 (Wenns-Funktion nicht möglich)
2 separate Tabellen
    Tabelle A (MPS Easy) ist meine Datenquelle, dort dürfen keine Kalkulation etc. stattfinden und die Daten sind auch nicht dort anders strukturierbar
    Tabelle B (Tracking Easy) ist meine Formeltabelle, dort sollen am Ende die Ergebnisse stehen und diese Tabelle kann ich mit Formeln etc. anpassen
Wenn möglich, keine Makros/VBA


Nun zu meinen 2 Problemen:

1

In Tabelle B habe ich in Spalte C einen Suchwert, der Fix ist. Darauf baut meine Suche auf. In Spalte E möchte ich nun aus Tabelle A den Wert per Formel suchen, bei dem der Suchwert in Spalte C übereinstimmt un die Auswahl in Spalte D c ist, sollte die Auswahl a,b oder d sein, interessieren mich die Werte nicht. Nun ist es natürlich so, dass der Wert, den ich Suche, nicht immer in der selben Spalte ist, sondern theoretisch irgendwo in den Spalte zwischen F und Q, manchmal stehen auch mehrere Werte dort, dann möchte ich jedoch nur den zeitlich ersten haben. 

Beispiel:

Mein Suchwert ist 5678 und die Auswahl muss c sein, meine Ergebnisse stehen also in Reihe 20. In dieser Reihe gibt es zwei Werte, für April 4444 und für Juli 7777777. Ich möchte nur den April Wert finden. 

Das habe ich bisher über verschachtelte Wenn-Funktionen mit Index-Vergleich gelöst. Das funktioniert, aber ist natürlich sehr unschön und geht eigentlich zu sehr in die Länge, hier sind es nur 12 Spalten, in denen mein Ergebnis theoretisch stehen kann, in der Realität sind es deutlich mehr.

Gibt es eine bessere Version als die Wenn-Funktionen mit Index-Vergleich? 

2

Nachdem der erste Teil noch einfach ist, habe ich beim 2. bisher keine Lösung gefunden.

Nachdem ich per Formel als den Wert gefunden haben, möchte ich nun Wissen, welcher Monat zu diesem Wert gehört.

Beispiel:

Wir hatten als Suchwert 5678, Auswahl war c und unser Ergebnis war 4444. (Zelle I20)
Nun möchte ich den dazugehörigen Monat haben, also April (Zelle I1). 

Ich kann jetzt mit einer horizontalen Index-Vergleich Funktion den Wert 4444 suchen lassen und mir dazu den Wert aus Zeile 1 wiedergeben lassen.
Allerdings muss ich dafür eine Suchmatrix bestimmen, in diesem Falle wäre das Reihe 20.
In die Formel kann ich aber nicht Reihe 20 eingeben, da sie sonst ja nicht für andere Suchwerte funktioniert, deren Ergebnisse in anderen Zellen stehen.

Ich hatte überlegt, mit der Formel ZEILE zu arbeiten, da diese mir die Reihe ausgibt, in der mein Ergebnis steht. Allerdings kann ich dies nicht in meine eigentlich Funktion einbauen, da ZEILE lediglich eine Zahl (hier 20) ausspuckt, aber nicht das Tabellen-Blatt dazu. Somit würde meine Formel in Reihe 20 aus Tabelle B suchen, sie muss aber in Reihe 20 aus Tabelle A suchen.

Ich habe bisher keine Möglichkeit gefunden, dieses Problem zu lösen.

Ich hoffe, ihr könnt mir helfen.
Fragen beantworte ich gerne.

Viele Grüße,

Lukas

[Der Steuerfuzzi]

Hallo,

versuch es mal damit:

Code:

=WENNFEHLER(WVERWEIS("?*";INDEX('[MPS Easy.xlsx]Tabelle1'!$F$1:$Q$37&"";VERWEIS(C2&D2;'[MPS Easy.xlsx]Tabelle1'!$C$2:$C$37&'[MPS Easy.xlsx]Tabelle1'!$D$2:$D$37;ZEILE('[MPS Easy.xlsx]Tabelle1'!$D$2:$D$37));0);1;FALSCH);"")

[SliQz]

Hallo,

vielen Dank schon mal, leider löst es das Problem bei mir nicht. Eventuell könnten Sie Ihre bearbeitete Excel-Mappe hochladen?


Folgende Probleme erkenne ich bei mir:

1. Das Suchkriterium des Wverweises ist mit "?*" angegeben, leider verstehe ich den Sinn dahinter nicht. Ist es so beabsichtigt oder müsstest dort nicht eventuell E2 stehen?
2. Die Verweis-Funktion bezieht sich auf C2&D2, wobei die ganze Spalte D eigentlich leer ist, muss sich der Verweis eventuell auf die andere Tabelle beziehen, denn nur dort ist die Spalte D mit Inhalt gefüllt?
3. Normalerweise funktioniert der Wverweis ja nur noch unten, so wie der Sverweis nur nach rechts funktioniert.

Über eine weitere Antwort würde ich mich sehr freue.

Vielen Dank und beste Grüße,

Lukas

[Der Steuerfuzzi]

1. Das Suchkriterium des Wverweises ist mit "?*" angegeben, [...] Ist es so beabsichtigt [...]?

Ja, das ist Absicht.

2. Die Verweis-Funktion bezieht sich auf C2&D2, wobei die ganze Spalte D eigentlich leer ist,

Ich habe es so verstanden, dass in Spalte D der Suchwert © steht. Wenn c fest bleiben soll, einfach C2&"c" ersetzen.

3. Normalerweise funktioniert der Wverweis ja nur noch unten, so wie der Sverweis nur nach rechts funktioniert.

WVERWEIS sucht in der ersten Zeile des Bereichs und gibt den entsprechenden Wert in der angegeben Zeile zurück. Durch Index wird die Zeile mit den übereinstimmenden Werten ermittelt, die in WVERWEIS durchsucht wird. Dabei findet WVERWEIS mit "*?" den ersten Wert in dieser Zeile und gibt diesen zurück.

[shift-del]

Hallo

Hier mal mein Vorschlag.

	A	B	C	D	E	F	G	H	I	J
1			Suchwert		Wert wenn Suchwert übereinstimmt und Auswahl c ist					Datum zu dem Wert aus Spalte E
2	4	7	1234		22					Febraur
3	8	8	2345		333					März
4	12	#DIV/0!	3456		#DIV/0!					#DIV/0!
5	16	10	4567		55555					Mai
6	20	9	5678		4444					April
7	24	#DIV/0!	6789		#DIV/0!					#DIV/0!
8	28	6	7890		1					Januar
9	32	15	8901		10101010					Oktober
10	36	16	9012		11111111111					November

Zelle	Formel
A2	=VERWEIS(42;1/('[MPS Easy.xlsx]Tabelle1'!C$2:C$37=C2)/('[MPS Easy.xlsx]Tabelle1'!$D$2:$D$37="c");ZEILE('[MPS Easy.xlsx]Tabelle1'!$C$2:$C$37))
B2	=1/AGGREGAT(14;6;(ISTZAHL(INDEX('[MPS Easy.xlsx]Tabelle1'!$F:$Q;A2;0))/SPALTE(F:Q));1)
E2	=INDEX('[MPS Easy.xlsx]Tabelle1'!$A:$Q;A2;B2)
J2	=INDEX('[MPS Easy.xlsx]Tabelle1'!$A:$Q;1;B2)

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

[steve1da]

Hola,

verlinkst du bitte deine Beiträge in den verschiedenen Foren untereinander?

Danke.

Gruß,
steve1da

[SliQz]

Bereits vielen Dank für eure Beiträge, ich habe sie in der Beispieldatei übernommen und sie funktionieren.

Leider habe ich noch Probleme, die Struktur in meine richtige Datei zu übertragen.
Die genaue Situation werde ich nächste Woche noch einmal detailliert aufbereiten, so dass wir das hoffentlich lösen können.


Ich habe zu dem gleichen Thema auch in einem anderen Forum unter folgendem Link um Hilfe gebeten:
http://www.herber.de/cgi-bin/callthread....ex=1561444

Bitte entschuldigt, dass ich dies nicht direkt am Anfang gekennzeichnet habe.

Ich wünsche euch ein schönes Wochenende und lasse am Dienstag von mir hören.

[SliQz]

Hallo zusammen,

wie bereits gesagt, habe ich noch Probleme, die Formel auf mein echtes Szenario anzuwenden, das liegt vermutlich daran, dass mein Beispiel nicht gut genug war.
Ich habe mal eine bessere Quelldatei und Ziel Datei hochgeladen.
Leider extern, da zu groß für internen Upload.

Bessere Dateien

Quelle (MPS):
In Spalte G steht der variable Suchwert.
In Spalte AB steht der zweite (feste) Suchwert.
In den Spalten AF bis AR stehen die Daten.

Ziel (Skeletton):
In Spalte O steht der variable Suchwert.
In Spalte AZ soll der dazugehörige erste Wert aus der Quelle stehen.

Ich suche immer noch den ersten Eintrag pro Suchwert, der entweder in Spalte AF oder AG oder AH etc. steht.

Ich habe meine Probleme hier noch einmal zusammengefasst und würde mich über eure Hilfe freuen!

@Der SteuerFuzzi:

Du hattest mir empfohlen:

Code:

=WENNFEHLER(WVERWEIS("?*";INDEX('[MPS Easy.xlsx]Tabelle1'!$F$1:$Q$37&"";VERWEIS(C2&"c";'[MPS Easy.xlsx]Tabelle1'!$C$2:$C$37&'[MPS Easy.xlsx]Tabelle1'!$D$2:$D$37;ZEILE('[MPS Easy.xlsx]Tabelle1'!$D$2:$D$37));0);1;FALSCH);"")

Meine adequate Formel dazu lautet:

Code:

=WENNFEHLER(WVERWEIS("?*",INDEX('[MPS.xlsx]Master MPS EF PD OPS'!$AF$1:$AR$47620&"",VERWEIS(O5&"5 Production (Planned)",'[MPS.xlsx]Master MPS EF PD OPS'!$G$2:$G$47620&'[MPS.xlsx]Master MPS EF PD OPS'!$AB$2:$AB$47620,ZEILE('[MPS.xlsx]Master MPS EF PD OPS'!$AB$2:$AB$47620)),0),1,FALSCH),"not in file")

Allerdings gibt mir die Formel nun falsche Werte zurück und gibt oftmals den gleichen Wert zurück. Um das zu verstehen, habe ich mal die einzelnen Bestandteile aufgelistet:

'[MPS Easy.xlsx]Tabelle1'!$F$1:$Q$37 habe ich ersetzt durch '[MPS.xlsx]Master MPS EF PD OPS'!$AF$1:$AR$47620 - also der Bereich, in dem die Daten stehen.
C2&"c" habe ich ersetzt durch O5&"5 Production (Planned)" - O5 is die Zelle, in der der Suchwert steht und die zweite (feste / gleichbleibende) Bedingung lautet 5 Production Planned.
'[MPS Easy.xlsx]Tabelle1'!$C$2:$C$37 habe ich ersetzt durch '[MPS.xlsx]Master MPS EF PD OPS'!$G$2:$G$47620 - also die Spalte, in der der Suchwert steht, jeder Suchwert kommt insgesamt 9x vor
'[MPS Easy.xlsx]Tabelle1'!$D$2:$D$37 habe ich ersetzt durch '[MPS.xlsx]Master MPS EF PD OPS'!$AB$2:$AB$47620 - also die Spalte, in der die zweite (feste) Bedingung steht
Dies habe ich auch für den Bezug der ZEILE-Funktion gemacht.

Anscheinend findet er jetzt aber nicht den genauen Wert, sondern für circa 30 verschiedene Suchwerte immer den gleiche, für die nächsten 30 einen anderen. 
Der gefundene Wert hat in Spalte AB einen andere Wert als 5 Production Planned, da scheint bei mir irgendwas nicht zu funktionieren. 

Irgendetwas an meiner Formel muss ich ändern, ich weiß aber nicht was - kannst du es mir sagen?


@shift-del:

Du hattest mir eine Lösung in 4 Schritten empfohlen, diese habe ich versucht direkt so umzusetzen.


Zelle
A2
=VERWEIS(42;1/('[MPS Easy.xlsx]Tabelle1'!C$2:C$37=C2)/('[MPS Easy.xlsx]Tabelle1'!$D$2:$D$37="c");ZEILE('[MPS Easy.xlsx]Tabelle1'!$C$2:$C$37)

M5
=VERWEIS(42,1/('[MPS.xlsx]Master MPS EF PD OPS'!$G$2:$G$48000=O5)/('[MPS.xlsx]Master MPS EF PD OPS'!$AB$2:$AB$48000="5 Production (Planned)"),ZEILE('[MPS.xlsx]Master MPS EF PD OPS'!$G$2:$G$48000))

Zelle
B2
=1/AGGREGAT(14;6;(ISTZAHL(INDEX('[MPS Easy.xlsx]Tabelle1'!$F:$Q;A2;0))/SPALTE(F:Q));1)

N5
=1/AGGREGAT(14,6,(ISTZAHL(INDEX('[MPS.xlsx]Master MPS EF PD OPS'!$AF:$AR,M5,0))/SPALTE(AF:AS)),1)
Zelle
E2
=INDEX('[MPS Easy.xlsx]Tabelle1'!$A:$Q;A2;B2)

BA5
=INDEX('[MPS.xlsx]Master MPS EF PD OPS'!$A:$AR,M5,N5)

Zelle
J2
=INDEX('[MPS Easy.xlsx]Tabelle1'!$A:$Q;1;B2)

BB2
=INDEX('[MPS.xlsx]Master MPS EF PD OPS'!$A:$AR,1,N5)


Bei der ersten Formel habe ich die Spalte mit dem variable Suchwert und die Spalte mit der zweiten festen Bedingung sowie den Bezug für ZEILE angepasst.
Hat die 42 am Anfang eine spezielle Bedeutung und müsste eventuell auch angepasst werden? Eigentlich gibt mir die Formel bisher relativ sauber die entsprechenden Zeilen-Nummer zurück

Bei der zweiten Formel habe ich die Suchmatrix der Index Funktion angepasst, war mir aber nicht wirklich sicher, was ich mit der Funktion SPALTE machen musste.
Ich habe versucht, die anzupassen, allerdings gibt er mir immer das gleiche Ergebnis aus.
Momentan gibt mir die Funktion als Ergebnis immer 32 aus.

Bei der dritten Formel habe ich die Suchmatrix der Index Funktion sowie die Bedingungen angepasst.
Das Ergebnis ist immer das gleiche, da die Spalte immer 32 ist.

Bei der vierten Funktion habe ich ebenso die Suchmatrix sowie eine Bedingung angepasst.
Allerdings gibt er mir aufgrund von Spalte 32 immer den gleiche Wert zurück (CW 15 / April)



Vielen Dank für eure Mühe und ich hoffe, wir können es zusammen Lösen.

Viele Grüße und ein schönes Wochenende,

Lukas

[WillWissen]

Hi Lukas,

Ich habe mal eine bessere Quelldatei und Ziel Datei hochgeladen.

Leider extern, da zu groß für internen Upload.

bitte keine externen Filehoster verwenden. Dort bleiben Beispieldateien nur temporär und nutzen dann nachfolgenden Usern, die ein ähnliches Problem haben, nicht. Auch wird sich wohl kaum jemand über 22 MB runterladen wollen.

Bitte erstelle eine Kopie deiner Datei(en), lösche bis auf 15-20 relevante Datensätze alles raus, anonymisiere ggfls. sensible Daten und lade sie dann mit der foreneigenen Software hoch.

[shift-del]

Die Helfer sind immer erfreut wenn sie Lösungen für die Tonne erarbeitet haben weil die Datenlage in Wirklichkeit gaaaaaaanz anders ist.

Code:

=VERWEIS(42;1/('[MPS.xlsx]Master MPS EF PD OPS'!$G$2:$G$48000=O5)/('[MPS.xlsx]Master MPS EF PD OPS'!$AB$2:$AB$48000="5 Production (Planned)");ZEILE('[MPS.xlsx]Master MPS EF PD OPS'!$G$2:$G$48000))
=VERWEIS(9^99;1/(1/INDEX('[MPS.xlsx]Master MPS EF PD OPS'!$AF:$AR;M5;0));SPALTE(AF:AR))
=INDEX('[MPS.xlsx]Master MPS EF PD OPS'!$A:$AR;M5;N5)
=INDEX('[MPS.xlsx]Master MPS EF PD OPS'!$A:$AR;1;N5)