Excel Index / Vergleichsmonster entschlacken

[Pyrukar]

Hallo zusammen,

ich bin gerade am überlegen, wie ich eine Formel am Sinnvollsten und einfachsten Gestalte ... Ich bekomme mein Ergebis raus, wenn ich mit Hilfsspalten und Endlos langen Vergleichsketten arbeite, aber ich hoffe ihr habt eine Einfachere Idee.



Meine tabelle sieht wie folgt ungefähr aus (nur natürlich mit viel mehr einträgen

ID	xyz3	xyz2	xyz1
abc1	3	2	1
abc2	3,5	2,5	1,5
abc3	9	6	3

Ich habe jetzt folgendes Problem. Ich gegeben ist die abc ID als Text sowie ein nicht eindeutiger Wert. Ergebnis soll sein, die xyz ID vom nächst Größeren Wert. Bsp. abc2 und 2,3 soll xyz2 zurückgeben. Da die Vergleich Funktion für "Größer als" eine Absteigende Sortierung fordert, habe ich xyz schon von groß nach klein Angeordnet.
Jedoch muss ich ja mehrfach die Suchmatrix angeben und etweder ich mache für die Zeile eine Hilfsspalte oder ich muss mehrfach eine Index(Vergleich()) Schachtel bauen wodurch meine Formel endlos aufgeblasen wird. Gibts ne sauberere Lösung für das Problem ich will eigentlich keine Hilfsspalte für die Zeile einsetzen

gruß
Pyrukar

 

[steve1da]

Hola,
mal auf die Schnelle:
Daten stehen in A1 bis D4, das gesuchte "abc" steht in A11, die 2,3 steht in A12.

=INDEX(B1:D1;AGGREGAT(15;6;SPALTE(B1:D1)-1/((A2:A4=A11)*(B2:D4=MIN(WENN((A2:A4=A11)*(B2:D4>=A12);B2:D4))));1))

In Versionen vor 365 muss die Formel mit Strg-Shift-Enter abgeschlossen werden.
Gruß
steve1da

 

[Pyrukar]

Vielen Dank @steve1da

deine Formel tut was sie soll, leider Verstehe ich die Formel nicht was da was tut und ich tue mich mit der Aggregat Funktion im gesamten etwas schwer. könntest du das mir ein bisschen aufschlüsseln was in deiner Formel was tut?

Edit ... wie zum Henker definierst du das Array???

SPALTE(B1:D1)-1/((A2:A4=A11)*(B2:D4=MIN(WENN((A2:A4=A11)*(B2:D4>=A12);B2:D4))))

Da blicke ich leider echt nicht durch, kann mir das bitte jemand aufdröseln?

 

[steve1da]

Index/Aggregat ist hier schön erklärt:
https://thehosblog.com/2013/05/08/excel-mehrere-suchkriterien-mehrere-treffer-aggregat/
Der min/wenn Teil ist klar?

 

[Pyrukar]

Der min/wenn Teil ist klar?

Nein ... absolut nicht

(siehe Edit)

 

[steve1da]

Index(B1:D1

legt fest, aus welchem Bereich das Ergebnis wiedergegeben werden soll. Der Bereich ist 3 Spalten groß, daher schreibt man entweder

Spalte(B1:D1)-1

oder man könnte auch

Spalte(A1:C1)

schreiben. Im Endeffekt muss die Bereichsgröße immer von 1-X in der Formel definiert sein.
Dann definiert man die Kriterien, die alle übereinstimmen müssen. Zum Einen muss in A2:A4 der Wert aus A11 stehen. Zum anderen wird in B2 bis D4 die kleinste Zahl gesucht, die größer als der Wert in A12 ist und gleichzeitig der Wert in A2:A4 dem Wert aus A11 entspricht.
Das sind die beiden Kriterien für Aggregat.

 

[Pyrukar]

entweder da geht was schief, oder ich habe mich irgendwo vertippt.

=INDEX(G72:M93;AGGREGAT(15;6;SPALTE(G72:M93)-1/((F73:F93=Q67)*(G72:M93=MIN(WENN((F73:F93=Q67)*(G72:M93>=Q69);G72:M93))));1))

Also mein Index ABC steht in Q67 und mein Wert in Q69. Die Gesamte Tabelle geht von F72:M93

Ich bekomme leider nur den Fehler #Zahl! ... zur Info falls das relevant ist, die xyz Werte sind Nummerische Werte.

 

[steve1da]

In welchen Zellen steht abc1, abc2...genau?
In welchen Zellen steht xyz3, xyz2....genau?
Nur die Bereiche bitte.

 

[Pyrukar]

abc1-abcn = F73:F93
xyz1-xyzn = G72:M72

 

[steve1da]

Da stimmt so ziemlich kein Bereich
=INDEX(G72:M72;AGGREGAT(15;6;SPALTE(G72:M72)-6/((F73:F93=Q67)(G73:M93=MIN(WENN((F73:F93=Q67)(G72:M92>=Q69);G73:M93))));1))