Excel (2010): Index mit Suche nach bestimmter Zeichenfolge in einem der Suchkriterien

[nona]

Hallo ihr Lieben,

ich habe ein Problem mit einer Formel, die ich zur Erstellung eines Reports auf der Arbeit brauche.
Ich habe eine Exceldatei, die hier die Basis der Projektplanung bildet, und die in etwa wie im Tabellenblatt "Projektplanung" in der angehängten Datei aussieht (alles, was ich hier hochlade, habe ich natürlich "anonymisiert"):
Anhang anzeigen Frage ComputerBase.xlsx

Die Spalten ganz rechts ("Aktuell" und "Neu") beinhalten jeweils eine Formel, die mir aus den Spalten E bis P das jeweilige Datum ziehen, unter dem das x steht. Die Formel habe ich hier nicht mit eingefügt, die funktioniert einwandfrei (zusammengesetzt aus INDEX, MAX und weiteren). "Neu" bedeutet, dass die Produkteinführung zu diesem Datum der Überschrift stattfinden soll, "Aktuell", dass zum Ende des Vormonats dieses Produkt aus dem Markt genommen werden soll.

Das erste Tabellenblatt "Überblick" beinhaltet nun quasi dieselben Informationen, jedoch etwas anders sortiert: die einzelnen Länder, die an dem Projekt teilnehmen, sind einzeln aufgelistet und kommen nicht in einer einzigen Zelle vor. Bisher hatte ich nur die Spalte "Land" benutzt und unabhängig davon, ob das Land auch wirklich in "Projektplanung" gelistet ist, die Daten aus "Projektplanung" Spalte Q und R in "Überblick" Spalte F und G gezogen. Für den Fall der aktuellen Artikelnr. habe ich folgende Formel benutzt, die auch super funktioniert:

{=WENNFEHLER(INDEX(Projektplanung!$Q:$Q;VERGLEICH(D3;WENN(Projektplanung!$B:$B=A3;Projektplanung'!$C:$C);0));"")}

Für die neue Artikelnr. habe ich einfach die Zellen- und Spaltenbezüge geändert.

Nun möchte ich gerne noch eine weitere Bedingung einbauen, die es mir erlaubt zu erkennen, ob die Länder, die im "Überblick" alle einzeln aufgelistet sind, überhaupt für das jeweilige Projekt und den jeweiligen Wechsel in der Artikelnummer vorgesehen sind. Mein Problem dabei ist, dass in der "Projektplanung" nur die Abkürzungen der Länder innerhalb einer Zelle vorkommen und ich keine Ahnung habe, wie man innerhalb der obigen Formel ein weiteres Kriterium einbauen kann, das in der kompletten Spalte A in "Projektplanung" die Zelle findet, in der eben z.B. der Inhalt der Zelle "Überblick" C3 vorkommt, unter der Voraussetzung, dass die Angaben über Projekt und Artikelnummer auch mit dem übereinstimmen, was in der entsprechenden Zeile im "Überblick" steht.
Ich habe es bereits folgendermaßen versucht:

{=WENNFEHLER(INDEX(Projektplanung!$Q:$Q;VERGLEICH(D3;WENN(UND(C3=Projektplanung!$A:$A;Projektplanung!$B:$B=A3);Projektplanung!$C:$C);0));"")}

Das funktioniert natürlich nicht, weil ja C3 nicht dem kompletten Inhalt der Zellen in "Überblick!A:A" enspricht, sondern eben wenn überhaupt nur ein Teil davon ist. Ich hab's auch schon probiert C3 in der Formel zu ersetzen durch "*"&C3&"*", damit innerhalb der Zellen nach dem Inhalt von C3 gesucht wird, aber auch das funktioniert nicht.

Ich bin durchaus fortgeschritten in Excel, aber mit Makros kenne ich mich gar nicht aus. Sollte es also nur so gehen, bräuchte ich entweder eine ganz genaue Beschreibung, wie ich das anstelle, oder aber ich müsste die Quelldatei "Projektplanung" umgestalten und darum bitten, dass sie von den Kollegen anders geführt wird. Das wäre aber sehr kompliziert (ich habe die Datei hier sehr vereinfacht dargestellt). Daher wäre es mir lieber, wenn es durch eine Formel oder so zu lösen wäre.

Vielen Dank im Voraus für eure Hilfe! Ich hoffe ihr könnt mir bei meinem Problem helfen.
Liebe Grüße,
nona

 

[floq0r]

=WENNFEHLER(
	INDEX(
		Projektplanung!$Q:$Q;
		VERGLEICH(
			D3;
			WENN(
				((Projektplanung!$B:$B=A3)*(WENNFEHLER(SUCHEN(C3;Projektplanung!$A:$A)>0;FALSCH)));
				Projektplanung!$C:$C);
			0
		)
	);
"")

ich hoffe du hast eine schnelle CPU

Nachtrag:

ich würde hier structured references benutzen, das senkt den berechnungsaufwand erheblich:
die daten in Projektplanung als tabelle formatieren (hier benannt als PP) und die formel wie folgt abändern:

=WENNFEHLER(
    	INDEX(
    		PP[Aktuell];
    		VERGLEICH(
    			D3;
    			WENN(
    				((PP[Projekt]=A3)*(WENNFEHLER(SUCHEN(C3;PP[Land])>0;FALSCH)));
    				PP[akt. Art.nr]);
    			0
    		)
    	);
    "")

 

[nona]

Hallo floq0r,

super, vielen lieben Dank!!! Funktioniert scheinbar einwandfrei
Ich hatte es selbst schon mit der FINDEN-Funktion probiert, aber ich habe leider noch nicht ganz den Gebrauch von * zum Verknüpfen mehrerer Kriterien o.ä. verstanden, da muss ich mich nochmal ransetzen. Aber Hauptsache ist erst einmal, dass es funktioniert.

Danke auch für den Hinweis mit den strukturierten Verweisen! Davon wusste ich bisher noch nichts, werde ich mir dann wenn ich ein wenig Zeit habe auch mal zu Gemüte führen. Mit solchen Verknüpfungen wie hier arbeite ich nämlich täglich. Bisher bekommt mein lieber Lenovo das alles hin, aber braucht eben manchmal so seine Zeit

Ganz lieben Dank dir!!!
Gruß,
nona

 

[cumulonimbus8]

aber ich habe leider noch nicht ganz den Gebrauch von * zum Verknüpfen mehrerer Kriterien o.ä. verstanden

Das ist in einfachen Worten Rechnen mit Logik. (a>b) * (c=d) entspricht (a>b) UND (c=d). Ein + statt dem * wäre ein [einschließendes] ODER. Denn jede unserer Klammern (jede Vergleichsoperation) liefert entweder eine 1 wenn die Bedingung zutrifft und eine 0 wenn es nicht zutrifft. Und so ergibt diese Multiplikation oder Addition einen Endwert der entweder gleich 0 ist oder größer als 0. Die Funktion schließlich die auf ein stimmt oder stimmt nicht reagiert nimmt die 0 für stimmt nicht und alles Andere für stimmt.

CN8