Microsoft Excel

Herbers Excel/VBA-Archiv

SVerweis mit variabler Spaltenadressierung

Betrifft: SVerweis mit variabler Spaltenadressierung von: stormlamp
Geschrieben am: 29.09.2014 08:54:08

Hallo Zusammen,

ich möchte in einem SVerweis eine bestimmte Spalte anhand eines anderen Zellinhalts suchen und je nachdem, was dort steht den Wert einer weiteren Spalte zurückgeben oder ansonsten der Erbeniswert auf 0 setzen.

Beispieldatei:
https://www.herber.de/bbs/user/92870.xlsx

In der Tabelle1 soll das Erbenis stehen. Gewünschter Suchvorgang:

Suche für den Namen "abc"(Tabelle1/Spalte A) in Tabelle2 die Spalte, die in Zeile 3 den Wert des Feldes Tabelle1/B2 hat (also 10). Dann prüfe, ob zu dem SVerweis auf den Namen "abc" dort ein "x" oder ein "z" steht. Wenn dort ein "x" steht, ist das in Tabelle1/Spalte C das Ergebnis =0, wenn dort ein "z" steht, übernimm den Wert, der in Spalte "J" zu diesem Namen "abc" gehört, hier als der Wert "110" in Tabelle1/Spalte C.

Die gleiche Formel benutze ich dann in Tabelle1, Spalte D, in die ich mir den Wert2 hole, falls ein "z" als erstes Suchergebnis gedfunden wird, hier also "120".

Ich habe in Tabelle1 die Felder gelb markiert, in denen das Ergebnis dieser Gesamtfunktion stehen soll. Es müssten die Werte herauskommen, die ich dort amuell eingetragen habe.

Für mich ist diese Funktion leider nicht so ganz trivial, daher bitte ich hier um Unterstützung. Ich hoffe, man kann meine Formulierungen einigermaßen verstehen.

Gruß
Hans

  

Betrifft: AW: SVerweis mit variabler Spaltenadressierung von: fcs
Geschrieben am: 29.09.2014 09:48:06

Hallo Hans

man kann es in einer Formel lösen.
Die Formeln sind aber etwas übersichtlicher, wenn man in einer Hilfsspalte ermittelt, ob der Vergleichswert x oder z ist.

Gruß
Franz

Name	Ziel	Wert1	Wert2	Wert1	Wert2	Wert E:I
abc	10	110	120	110	120	z

Formeln
C2:	=WENNFEHLER(WENN(SVERWEIS(A2;Tabelle2!$A$3:$K$31;VERGLEICH(B2;Tabelle2!$E$2:$I$2;
0)+4;FALSCH)="x";0;WENN(SVERWEIS(A2;Tabelle2!$A$3:$K$31;VERGLEICH(B2;Tabelle2!$E$2:$I$2;
0)+4;FALSCH)="z";SVERWEIS(A2;Tabelle2!$A$3:$K$31;10;FALSCH);"---"));"---")
D2:	=WENNFEHLER(WENN(SVERWEIS(A2;Tabelle2!$A$3:$K$31;VERGLEICH(B2;Tabelle2!$E$2:$I$2;
0)+4;FALSCH)="x";0;WENN(SVERWEIS(A2;Tabelle2!$A$3:$K$31;VERGLEICH(B2;Tabelle2!$E$2:$I$2;
0)+4;FALSCH)="z";SVERWEIS(A2;Tabelle2!$A$3:$K$31;11;FALSCH);"---"));"---")
E2:	=WENN(G2="x";0;WENN(G2="z";SVERWEIS(A2;Tabelle2!$A$3:$K$18;10;FALSCH);"---"))
F2:	=WENN(G2="x";0;WENN(G2="z";SVERWEIS(A2;Tabelle2!$A$3:$K$18;11;FALSCH);"---"))
G2:	=SVERWEIS(A2;Tabelle2!$A$3:$K$31;VERGLEICH(B2;Tabelle2!$E$2:$I$2;0)+4;FALSCH)



  

Betrifft: AW: SVerweis mit variabler Spaltenadressierung von: stormlamp
Geschrieben am: 29.09.2014 10:14:30

Hallo Zusammen,

danke für die Unterstützung, habe die Formel inzwischen selbst zusammengestellt:

=WENN(SVERWEIS(A2;Tabelle2!A:K;SUMMENPRODUKT((Tabelle2!$A$2:$K$15=B2)*SPALTE(Tabelle2!$A$2:$K$15));0) ="z";SVERWEIS(A2;Tabelle2!A:K;10;0))

Gruß
Hans


 

Beiträge aus den Excel-Beispielen zum Thema "SVerweis mit variabler Spaltenadressierung"