Office: INDEX-VERGLEICH mit Verketten

Hallo zusammen,

ich hab folgendes Problem.

wie kann ich Index- Vergleich und Verketten in einen Formel zusammen führen?

Mit freundlichen Grüßen

 

Hallo!

Sind es immer genau drei Begriffe, die zum Suchbegriff gehören?
Stehen diese Begriffe immer eine Zeile unterhalb des Suchbegriffs?

 

Hallo schatzi,

ja, es sind immer genau 3 Begriffe und die stehen immer unter dem Suchbegriff.

 

Hallo!

Passt es so?

	A	B	C	D	E	F	G
1	Suchbegriff	Schokolade
2	Ergebnis	Ei Zucker Kakao
3					Schokolade
4					Ei	Zucker	Kakao
5					Eintopf
6					Kartoffeln	Möhren	Erbsen

Zelle	Formel
B2	=INDEX(E:E;VERGLEICH(B1;E:E;)+1)&" "&INDEX(F:F;VERGLEICH(B1;E:E;)+1)&" "&INDEX(G:G;VERGLEICH(B1;E:E;)+1)

 

Hallo schatzi,

suppeeerrr.... vielen vielen Dank!

noch eine Verständnisfrage, was muss ich ändern um die 2 oder 3 Zeilen unter die Suchbegriffe zu suchen?

und nochmals vielen Dank

Gruß

 

Hallo!

=INDEX(E:E;VERGLEICH(B1;E:E;)+1)
sucht 1 Zeile darunter

=INDEX(E:E;VERGLEICH(B1;E:E;)+2)
sucht 2 Zeilen darunter

=INDEX(E:E;VERGLEICH(B1;E:E;)+3)
sucht 3 Zeilen darunter

 

Hallo Schatzi,

zur allererst... ich habs ausprobiert ..... superrrr... vielen vielen Dank.


Jedoch... habe ich festgestellt dass die gesuchte Wort, sprich "Schokolade" ab Spalte "E" bis "Z" verteilt ist.
Aufbau ist aber immer gleich.

Ich hab folgendes ausprobiert:

=INDEX(E:Z;VERGLEICH(B1;E:Z;)+1)&" "&INDEX(F:F;VERGLEICH(B1;E:Z;)+1)&" "&INDEX(G:G;VERGLEICH(B1;E:Z;)+1)

was mache ich hier falsch?

 

Hallo!

VERGLEICH verträgt als Suchvektor nur EINE Spalte oder EINE Zeile, daher der Fehler.
Aber deinen Aufbau verstehe ich trotzdem noch nicht.
Vielleicht kannst du diesen anhand einer Beispielmappe mit mehreren möglichen Datensätzen nochmal genauer erklären.

 

Hallo schatzi,

so ungefähr sieht´s aus.

 

Hi,

in B2 diese Formel:

{=INDIREKT(ADRESSE(MAX(($E$1:$K$100=B1)*ZEILE($1:$100))+1;MAX(($E$1:$K$100=B1)*SPALTE($E:$K))))&" "&INDIREKT(ADRESSE(MAX(($E$1:$K$100=B1)*ZEILE($1:$100))+1;MAX(($E$1:$K$100=B1)*SPALTE($E:$K))+1))&" "&INDIREKT(ADRESSE(MAX(($E$1:$K$100=B1)*ZEILE($1:$100))+1;MAX(($E$1:$K$100=B1)*SPALTE($E:$K))+2))}

Achtung Matrixformel! Geschweifte Klammern nicht eingeben sondern Formel mit Str+Shift+Enter abschließen.

Formel dann in B5 und B8 kopieren.

Bis später,
Karin

 

Hallo Karin,

*winke* *winke*

vielen Dank für die "Geile" Formel.

Ich hab versucht zuverstehen, jedoch aufgegeben als meine Gehirn mit Glühen anfing.

noch eine minimale Frage.

unzwar habe ich versucht die auf einen Tabellenblatt zu beziehen:

{=INDIREKT(ADRESSE(MAX((Tabelle!$E$1:$K$100=B1)*ZEILE(Tabelle!$1:$100))+1;MAX((Tabelle!$E$1:$K$100=B1)*SPALTE(Tabelle!$E:$K))))&" "&INDIREKT(ADRESSE(MAX((Tabelle!$E$1:$K$100=B1)*ZEILE(Tabelle!$1:$100))+1;MAX((Tabelle!$E$1:$K$100=B1)*SPALTE(Tabelle!$E:$K))+1))&" "&INDIREKT(ADRESSE(MAX((Tabelle!$E$1:$K$100=B1)*ZEILE(Tabelle!$1:$100))+1;MAX((Tabelle!$E$1:$K$100=B1)*SPALTE(Tabelle!$E:$K))+2))}


Kommt zwar keine Fehlermeldung, aber auch keine Ergebnis??

Was habe ich hier falsch gemacht?

Gruß

 

Hi,

du musst auch das INDIREKT auf das andere Tabellenblatt beziehen:

=INDIREKT("Tabelle!"&ADRESSE(MAX((Tabelle!$E$1:$K$100=B1)*ZEILE($1:$100))+1;MAX((Tabelle!$E$1:$K$100=B1)*SPALTE($E:$K))))&" "&INDIREKT("Tabelle!"&ADRESSE(MAX((Tabelle!$E$1:$K$100=B1)*ZEILE($1:$100))+1;MAX((Tabelle!$E$1:$K$100=B1)*SPALTE($E:$K))+1))&" "&INDIREKT("Tabelle!"&ADRESSE(MAX((Tabelle!$E$1:$K$100=B1)*ZEILE($1:$100))+1;MAX((Tabelle!$E$1:$K$100=B1)*SPALTE($E:$K))+2))



Zur Erklärung der Formel:

man benötigt die Zelladresse aller 3 Einträge, um dann ihren Inhalt per & miteinander verknüpfen zu können. Eine Zelladresse ermittelt sich mit dieser Formel: ADRESSE(Zeile;Spalte). Da man dies im gegebenen Fall aus einer Matrix (Bereich E1:K100) feststellen muss, verwendet man für die Zeile:

MAX((Tabelle!$E$1:$K$100=B1)*ZEILE($1:$100))

Da sich alle Begriffe 1 Zeile unterhalb der mit dem Suchbegriff befinden kommt noch +1 dazu

MAX((Tabelle!$E$1:$K$100=B1)*ZEILE($1:$100))+1

Die Spalte des Suchbegriffs wird hiermit festgestellt:

MAX((Tabelle!$E$1:$K$100=B1)*SPALTE($E:$K))

Für den 1. Teil ist sie identisch mit der des Suchbegriffs, für den 2. Teil muss 1 addiert werden, da er sich ja in der Spalte rechts daneben befindet:

MAX((Tabelle!$E$1:$K$100=B1)*SPALTE($E:$K))+1

und für den 3. Teil muss logischer Weise 2 addiert werden:

MAX((Tabelle!$E$1:$K$100=B1)*SPALTE($E:$K))+2

Das Ergebnis sind dann jedoch erst einmal nur die Zelladressen, deshalb wird noch jeweils ein INDIREKT() darum gesetzt, sodass man den Inhalt der betreffenden Zelle erhält.

Ich hoffe, ich konnte alles verständlich erklären.

Bis später,
Karin

 

Hi Karin,

vielen Dank für die ausführlicher Erklärung.
Dies hat die Horizont erweitert. *schoko rüberreicht*

Ich habe auch die Formel entsprechend für mich angepasst. Funzt auch ganz gut.

Ich hab zwar nochne Herausforderung jedoch mache ich hier einen neuen Tread "Zeilen Inhalte in Spalte untereinder Einfügen".

Vielen Vielen Dank. *noch´ne schoko n´überreicht*

Gruß

 

Hallo Karin,

ich muss nochmal an dich wenden.

Ich hab deine Formel umgeändert weil die Zeilen nach unten nicht 100 sondern bis 2000 gehen. Hab auch Spalten und Zellen die betroffen sind umgeändert. Jedoch kommt Falsche Ergebnisse. Was hab ich hier falsch gemacht??

Code:

=INDIREKT("ZVV!"&ADRESSE(MAX((ZVV!$R$2:$CT$2000=N2)*ZEILE($2:$2000))+1;MAX((ZVV!$R$2:$CT$2000=N2)*SPALTE($R:$CT))))&" "&INDIREKT("ZVV!"&ADRESSE(MAX((ZVV!$R$2:$CT$2000=N2)*ZEILE($2:$2000))+1;MAX((ZVV!$R$2:$CT$2000=N2)*SPALTE($R:$CT))+1))&" "&INDIREKT("ZVV!"&ADRESSE(MAX((ZVV!$R$2:$CT$2000=N2)*ZEILE($2:$2000))+1;MAX((ZVV!$R$2:$CT$2000=N2)*SPALTE($R:$CT))+2))

Gruß

 

Hi,

du hast die Formel auch als Matrixformel eingegeben?

Bis später,
Karin