Frage von Andr555, 41

Excel, mehrere Spaltenwerte in Tabelle müssen stimmen um anderen Wert in Zeile auszugeben?

Hallo ich habe eine Frage, und zwar habe ich eine Tabelle in dem Stil:

Auto Jahr Wert

BMW 2000 15500

Audi 2000 16800

BMW 2005 18900

Es kann das Auto nur einmal mit einem Datum vorkommen. Wie bekomme ich den Wert automatisch ausgelesen wenn ich zum Beispiel BMW 2005 haben möchte?Bin schon seit Stunden mit Sverweisen und vergleichen und allem anderen am rumtüfteln, komme aber absolut nicht auf eine Lösung

Expertenantwort
von DeeDee07, Community-Experte für Excel, 26

Falls die Daten in den Spalten A:C stehen und das gesuchte Auto in D1, das gesuchte Jahr in E1, verwendest du folgende Formel:

=INDEX(C1:C99;VERGLEICH(D1&E1;A1:A99&B1:B99;0))

Diese Formel als Matrixformel abschließen (mit Strg+Shift+Enter, dabei werden geschweifte Klammern {} erzeugt). Die Bezüge natürlich vorher anpassen.

Kommentar von Oubyi ,

DH!
Ich glaube ich war diesmal 20 Sekunden schneller

Expertenantwort
von Oubyi, Community-Experte für Excel, 23

Angenommen In E1 steht BMW und in F1 steht 2005. Der Suchbereich ist A1:C10, dann diese Formel:

{=INDEX(C1:C10;VERGLEICH(E1&F1;A1:A10&B1:B10;0))}

**ACHTUNG!**
Das ist eine Matrixformel.
Das bedeutet: Die geschweiften Klammern {} NICHT mit eingeben, sondern die Eingabe der Formel NICHT mit ENTER abschließen, sondern mit:
STRG & SHIFT & ENTER (alle drei gleichzeitig).
DAS erzeugt die { } und macht die Formel zu einer Matrixformel.

Klappt es?

P.S.: Bekommt das jemand ohne Matrix-Formel hin? Ich meine das geht auch irgendwie, komme aber nicht drauf.

Kommentar von DeeDee07 ,

DH, fast zeitgleich.

Wenn es eine Kombination von Auto und Jahr nur einmal gibt, dann würde auch

=SUMMENPRODUKT((A1:A99=E1)*(B1:B99=F1)*(C1:C99))

funktionieren. Wobei das auch wieder eine verkappte Matrixformel ist.

Kommentar von Jackie251 ,

Ohne Matrix/hilfszeilen hätte ich grad keine Idee.

Ich persönlich nutze aufgrund der Fehleranfälligkeit jedoch nie den Vergleich mit & sondern immer WENN. Natürlich ist das bei genauer Übereinstimmung egal, da dann keine Fehler auftreten, aber ob man beim nächstens Bereichsverweis dran denkt :-) ^^

Kommentar von Oubyi ,

@DeeDee:DH! Ich hatte eigentlich auch sofort an SUMMENPRODUKT gedacht, es dann aber irgendwie aufgrund eines Fehlschlusses wieder verworfen.

@Jackie: Da hier Text und Zahl kombiniert werden, habe ich es so gemacht, sonst schiebe ich schon mal was dazwischen:
{=INDEX(C1:C10;VERGLEICH(E1&"#"&F1;A1:A10&"#"&B1:B10;0))}

Kommentar von Jackie251 ,

Selbst mit etwas dazwischen schieben bekommt man bei einem sortiertem Vergleich Probleme.

Beispiel T1 ist eine Tarifgruppe und hierzu gibt es verschiedene Tarife gemäß der Dienstjahre.
Gruppe X1 +  1 Dienstjahr

Gruppe X1  + 3 Dienstjahre

Gruppe X1 + 5 Dienstjahre

Die Daten liegen sortiert vor und wenn man mittels Vergleich nach 2 Dienstjahren kommt man auf den Wert von 1 Dienstjahr raus (der größte Wert der kleiner ist als der gesuchte).
Sucht man nach 11 Jahren kommt man bei den 5 Dienstjahren raus.

Muss man jedoch auch in einer Großen Liste zunächst du Gruppe finden und verkettet die Zeilen erhält man

X1 & 1 => X11

X1 & 3 => X13

X1 & 5 => X15

Wird nun nach 2 Dienstjahren gesucht also nach X12
Dann ist nach Excel X11 der passende Wert. Sucht man nach 6 Dienstjahren also X16 ist X15 der passende Wert, soweit auch korrekt.
sucht man jedoch nach 11 Dienstjahren also nach X111 dann ist nach Excellogik die X13 > X111 und daher bleibt trotz 11 Dienstjahren der Tarif von Jahr 1 der Treffer.

Das kann man entweder beheben indem man mittels Text zunächst die Anzahl der Jahre  zweistellig definiert oder eben indem man durch die Wenn Funktion auf das Verketten verzichtet.
Da man an Text extra denken muss und dann später das Format nicht einfach ändern kann, nutzte ich für multiple Vergleiche immer das Wenn, fühlt sich für mich einfach sicherer an :-D
Wie gesagt ist nur eine persönliche Macke, an der Lösung mittels Verketten ist nichts anzusetzen.

Antwort
von Jackie251, 21

Da du uns ja keinerlei infos zum Aufbau denes Sverweis gibst, musst du sinngemäß folgendes umsetzen

=Sverweis(Jahr;Wenn(A1:A3=Auto;A2:C4);3)

das ganze mit STRG+Shift + Enter abschließen

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten