Frage von JensBausE, 77

Hallo, ich brauche eine Sverweis Verknüpfung. Wenn in einer Matrix, Wert x in Spalte A gefunden wird und Wert y zwischen Spalte B und C liegt, dann Spalte F?

Expertenantwort
von schmiddi1967, Community-Experte für Excel, 54

Naja, da man ja wieder erraten muß in welcher Zeile in welcher Zelle was steht oder wo du deine Suchdaten hast, gehe ich jetzt mal von folgender Situation aus.

Die Bezeichnug die du suchst (DC05) steht jetzt zb. in A1 und die Zahl die du suchst (zb. 1,601) in B1.

Deine Bezeichnungen beginnen ab Zelle A2-A6 und die Werte sind im Bereich B2-C6. Das Ergebnis welches du suchst befindet sich dann im Bereich F2-F6.

Dann kannst du dir das Ergebins mit folgender Formel anzeigen lassen:

=VERWEIS(2;1/(B2:B6<=B1)/(C2:C6>=B1)/(A2:A6=A1);F2:F6)

Sollte das ganze Tabellenblatt übergreifend sein musst du das natürlich anpassen, raten geht nämlich schlecht :))

Klappt es?

Expertenantwort
von Iamiam, Community-Experte für Excel, 38

Wenn die Bedingung nur für einen Wert zutreffen kann, dann:

=index(F:F;Summenprodukt((A:A="x")*(B:B<y)*(C:C>y)*Zeile(A:A)))

kann sie auf mehrere Werte zutreffen, müsste man noch ein KGrösste einbauen und eine Matrixformel ohne Summenprodukt draus machen, melde Dich dafür wieder zurück!

evtl <= und/oder >= setzen, da musst Du inhaltlich bestimmen, was Du mit "zwischen" genau meinst!

Zum Testen auf Richtigkeit anstatt A:A. B:B usw erst mal kleinere Bereiche nehmen, zN A20:A40, aber für alle gleich viele Zellen, nur F:F belassen.

Übrigens: pass auf, dass beim nächsten Screenshot Zeilen- und v.a. Spaltenköpfe mit dargestellt werden!

Kommentar von Ninombre ,

DH!  Das ist die saubere Lösung. Summenprodukt kam mir auch direkt in den Sinn, um die Bedingungen abzufragen, ich habe gestern aber nicht die Möglichkeit gedacht, dass für die Zeile zu verwenden. 

Kommentar von Iamiam ,

danke, ich habe aber auch schon Leute gefunden, die Sumprod meiden:"Da bringt man den Leuten bei, dass die Argumente in der Klammer mit ; aneinandergehängt werden, und dann plötzlich eine Multiplikation!" Na ja, um das Potential auszureizen, muss man eben manchmal von Standards abweichen....

Expertenantwort
von Ninombre, Community-Experte für Excel, 42

Mit sverweis geht das nicht mehr, aber vielleicht so

=INDEX(A1:F99;VERGLEICH("x"&MAX(WENN(B1:B99<=5;B1:B99))&MIN(WENN(C1:C99>=5;C1:C99));A1:A99&B1:B5&C1:C99;0);6)

Das "x" und die 5 musst Du gegen Deine X und Y Werte bzgl. Zellbezüge ersetzen.

Das ist eine Matrixformel, daher nach der Eingabe (und jeder Änderung der Formel) immer Strg+Shift+Enter drücken. Dann bilden sich die geschwungenen Klammern um die Formel - ansonsten funktioniert das nicht.

Das ganze setzt aber voraus, dass es einen eindeutigen Treffer gibt - es ist keine Trefferliste, falls es mehrere Ergebnisse mit zutreffender Bedingung geben sollte.

Keine passende Antwort gefunden?

Fragen Sie die Community