Office: SVERWEIS mit mehreren Antworten;oder doch eine andere Funkti

Hallo liebe Leute, ich bin hier neu und habe mir bislang immer mal nützliche Formeln von Euch kopiert und verwendet. Leider stoße ich momentan an meine Grenzen und finde leider auch nichts passendes hier im Forum.
Bitte um Hilfe.

In der Excel Tabelle im Anhang möchte ich mehrere Werte die Untereinander stehen suchen lassen und diese sowie die dazu gehörigen Bezeichnungen links wiedergeben lassen.

Ich habe in die Kästchen das Ergebnis welches ich erreichen möchte reingeschrieben.

Vielen Dank für Eure bemühungen.

 

Hallo,

da Pivottabelle hier suboptimal erscheint, fiel mir nur sowas ein:

Arbeitsblatt mit dem Namen 'Tabelle1'

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Palette 1

Palette 2

Palette 3

Palette 4

Palette 5

2

5

2

Artikel 1

Nr 123

5 kg

Palette 1

5

Artikel 1

Nr 123

5 kg

3

2

1

Artikel 2

Nr 124

6 kg

9

Artikel 6

Nr 128

10 kg

4

2

Artikel 3

Nr 125

7 kg

#ZAHL!

#ZAHL!

#ZAHL!

#ZAHL!

5

5

Artikel 4

Nr 126

8 kg

#ZAHL!

#ZAHL!

#ZAHL!

#ZAHL!

6

9

Artikel 5

Nr 127

9 kg

#ZAHL!

#ZAHL!

#ZAHL!

#ZAHL!

7

9

Artikel 6

Nr 128

10 kg

#ZAHL!

#ZAHL!

#ZAHL!

#ZAHL!

8

4

Artikel 7

Nr 129

11 kg

9

3

6

Artikel 8

Nr 130

12 kg

Zelle	Format	Wert
I2	"Palette" 0	1

Zelle	Formel
K2	=SVERWEIS($J2;INDEX($A:$H;;$I$2):INDEX($A:$H;;8);SPALTE(F$1)-$I$1;0)
J2	{=INDEX(INDEX($A:$H;;$I$2);KKLEINSTE(WENN(INDEX($A:$H;2;$I$2):INDEX($A:$E;10;$I$2)<>"";ZEILE($2:$10));ZEILE(A1)))}

<table><tr><td>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span>Strg+Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table>[/parsehtml]<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]

Achte auf das Zellformat in I2.
Formel aus J2 nach unten kopieren und aus K2 nach rechts und unten.
Fehlermeldungen je nach Officeversion abfangen!

 

Hallo silex1. Vielen lieben Dank für die Hilfe.
Es funktioniert auch alles bei Palette 1.
Habe aber den NAmen Palette weggelassen wegen der Formation.

Nur wenn ich 2 für Palette 2 eingebe rutschen die sv werte eins nach rechts.

Ich weis nicht wieso. Habe mal die DAtei reingestellt.
Danke nochmal für alle weiteren hilfen.

 

Hallo :lol: :shock: ,
was mir jetzt aufgefallen ist, dass wenn in spalte A z.b. 2x bei unterschiedlichen Artikeln der selbe wert eingegeben wird weil die selbe menge von zwei Artikel auf einer Palette liegen, dann wird der erste Wert wiedergegeben und nicht der nächste selbe Wert mit dessen Artikel.
Es ist schon länger her bei mir, aber ich meine das man eine Spalte immer weiter nach unten nach werten durchsuchen kann und dann die Zeile in der ein wert steht wird wiedergegeben. Ich bekomms einfach nicht hin. Bin schon am Probieren mit Wenn Dann +1.

Danke für Eure Hilfe

 

Hallo,

oh ja! Hab ich völlig übersehen! So müsste es besser sein:

Arbeitsblatt mit dem Namen 'Tabelle1'

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Palette 1

Palette 2

Palette 3

Palette 4

Palette 5

2

5

2

Artikel 1

Nr 123

5 kg

Palette 1

5

Artikel 1

Nr 123

5 kg

3

5

2

1

Artikel 2

Nr 124

6 kg

5

Artikel 2

Nr 124

6 kg

4

2

Artikel 3

Nr 125

7 kg

9

Artikel 6

Nr 128

10 kg

5

5

Artikel 4

Nr 126

8 kg

#ZAHL!

#ZAHL!

#ZAHL!

#ZAHL!

6

9

Artikel 5

Nr 127

9 kg

#ZAHL!

#ZAHL!

#ZAHL!

#ZAHL!

7

9

Artikel 6

Nr 128

10 kg

#ZAHL!

#ZAHL!

#ZAHL!

#ZAHL!

8

4

Artikel 7

Nr 129

11 kg

9

3

6

Artikel 8

Nr 130

12 kg

Zelle	Format	Wert
I2	"Palette" 0	1

Zelle	Formel
J2	{=INDEX(INDEX($A:$H;;$I$2);KKLEINSTE(WENN(INDEX($A:$H;2;$I$2):INDEX($A:$E;10;$I$2)<>"";ZEILE($2:$10));ZEILE(A1)))}
K2	{=INDEX(F:F;KKLEINSTE(WENN(INDEX($A:$H;2;$I$2):INDEX($A:$E;10;$I$2)<>"";ZEILE($2:$10));ZEILE(A1)))}

<table><tr><td>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span>Strg+Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table>[/parsehtml]<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]

 

Danke Danke werde ich gleich mal probieren. Hab schon mit ist leer rumgepielt.

 

KLASSE, PIRMA,
Großen Dank.
Nun noch alles in die richtige Tabelle einfügen und die komplzierte arbeit die Woche über ist leichter geworden. Nochmals Danke

 

Hallo silex1 ,
Danke nochmal,
nun wollte ich vor die

Zelle Formel
J2 {=INDEX(INDEX($A:$H;;$I$2);KKLEINSTE(WENN(INDEX($A:$H;2;$I$2):INDEX($A:$E;10;$I$2)<>"";ZEILE($2:$10));ZEILE(A1)))}

noch ein istzahl packen aber nun bekomme ich #Name als ausgabe

Ich wollte wenn zb. die letzte zahl wiedergegeben wurde das #Zahl nicht erscheint

Was mache ich verkehrt??

Noch eine Frage zu Formel in K2
=INDEX(F:F;KKLEINSTE(WENN(INDEX($A:$H;2;$I$2):INDEX($A:$E;10;$I$2)<>"";ZEILE($2:$10));ZEILE(A1)))

wieso ist die letzte Zelle A1 nicht fest. Beim kopieren nach rechts und unten verschiebt die sich aber das ergebnis ist das richtige??

 

Hallo,

wenn die Datei nur in XL07 verwendet werden soll:
=WENNFEHLER(Formel);"")

soll sie aber auch in XL03 arbeiten:
=WENN(ISTFEHLER(Formel);"";Formel)

Das A1 ist das "k" von KKLEINSTE() und damit man nicht immer 1..2...3 schreiben muss, schreibt man nur A1. Schau Dir dazu auch mal "relative und absolute Bezüge" an!

 

Vielen lieben Dank, die nächste Woche ist gerettet
:-D

 

hi, ich würde das mit der fehlerabfrage so lösen, obwohl der thread wohl schon abgeschlossen ist..:

1	2	3	4					Eingabe
5					Artikel 1	Nr 123	5 kg	1	Artikel 1	Nr 123	5 kg
	2	1			Artikel 2	Nr 124	6 kg		Artikel 3	Nr 125	7 kg
5			2		Artikel 3	Nr 125	7 kg		Artikel 6	Nr 128	10 kg
		5			Artikel 4	Nr 126	8 kg
	9				Artikel 5	Nr 127	9 kg
9					Artikel 6	Nr 128	10 kg
			4		Artikel 7	Nr 129	11 kg
		3			Artikel 8	Nr 130	12 kg

Zelle	Formel
J2	{=WENN(ANZAHL2(BEREICH.VERSCHIEBEN($A$2;;--$I$2-1;9))>=ZEILE(A1);INDEX(F$1:F$9;KKLEINSTE(WENN(INDEX($A$2:$D$9;;VERGLEICH($I$2;$A$1:$D$1;0));ZEILE($A$2:$A$9));ZEILE(A1)));"")}
K2	{=WENN(F12="";"";INDEX(G$1:G$9;KKLEINSTE(WENN(INDEX($A$2:$D$9;;VERGLEICH($I$2;$A$1:$D$1;0));ZEILE($A$2:$A$9));ZEILE(B1))))}

<table><tr><td>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span>Strg+Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table>[/parsehtml]<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]

 

Hallo, vielen DAnk für eine weitere Lösung.
Ich werde es gleich mal probieren.
Danke

 

Hallo,

bei kleinen überschaubaren Datenmengen, ist jede Lösung in Ordnung.
Bei größeren Datenmengen söllte jedoch auf volatile Funktionen verzichtet werden.
Oder man kauft sich nen High-End Rechner (jeden Monat neu!). :mrgreen:

 

hi silex1, okay, dann eben so (geht das soweit in ordnung?)..:

Arbeitsblatt mit dem Namen 'Tabelle1'

A

B

C

D

E

F

G

H

I

J

K

L

1

1

2

3

4

Eingabe

2

5

Artikel 1

Nr 123

5 kg

1

Artikel 1

Nr 123

5 kg

3

2

1

Artikel 2

Nr 124

6 kg

Artikel 3

Nr 125

7 kg

4

5

2

Artikel 3

Nr 125

7 kg

Artikel 6

Nr 128

10 kg

5

5

Artikel 4

Nr 126

8 kg

6

9

Artikel 5

Nr 127

9 kg

7

9

Artikel 6

Nr 128

10 kg

8

4

Artikel 7

Nr 129

11 kg

9

3

Artikel 8

Nr 130

12 kg

Zelle	Formel
J2	{=WENN(ANZAHL(INDEX($A$2:$D$9;;$I$2))>=ZEILE(A1);INDEX(F$2:F$9;KKLEINSTE(WENN(INDEX($A$2:$D$9;;$I$2);ZEILE($A$1:$A$8));ZEILE(A1)));"")}

<table><tr><td>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span>Strg+Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table>[/parsehtml]<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]

 

Hallo!

{=WENN(ANZAHL(INDEX($A$2:$D$9;;$I$2))>=ZEILE(A1);INDEX(F$2:F$9;KKLEINSTE(WENN(INDEX($A$2:$D$9;;$I$2);ZEILE($A$1:$A$8));ZEILE(A1)));"")}

Das Rote könnte man dann auch noch sparen...
Aber bei langen Listen (und schwachem Rechner) würde ich dann doch eher auf einen VBA-basierten Spezialfilter zurück greifen!