Wert in Matrix suchen, Wert aus 1 Zeile der Spalte ausgeben (WVERWEIS)
Hallo,
ich habe eine Tabelle in der in der ersten Zeile eine Überschrift steht.
Ich möchte jetzt nach einem Wert suchen und die Überschrift finden.
ein Beispiel:
X | A B C
--|-----------|---------|-----------
1 |Sehr gut | Gut | Schlecht
2 |Peter | Paul | Simon
3 |Hans | Tobi | Mario
4 |Martin | Michael | Klaus
5 |Sascha | | Oskar
6 | | | Ricardo
7 | | | Alfred
Suche: Paul
Ergebnis:In A1, B1 und C1 stehen die "Überschriften" und dadrunter die Daten.
Die Werte die in Suche eingetragen werden, werden über ein Dropdown Menü ausgewählt.
Ich gebe also einen Namen bzw. ein Text an und möchte dann den Inhalt der ersten Zeile, von der Spalte wo der Wert gefunden wurde, haben.
Ich habe es schon mit dem WVERWEIS versucht, leider ohne Erfolg, als ergebnis erhalte ich immer #NV. Die Formel habe ich dabei folgendermaßen benutzt:
=WVERWEIS(F1;A1:C7;1;FALSCH)bzw.:
=WVERWEIS(F1;A1:C7;1;WAHR)Danke schon mal im voraus für eure Hilfe!
3 Antworten
Dein Suchwort scheint in F1 zu stehen?
Dann die Formel:
=WENN(ZÄHLENWENN(B3:D9;$F$1)<1;"Suchargument nicht vorhanden";WENN(ZÄHLENWENN(B3:D9;$F$1)<1;"Suchargument mehrfach vorhanden";INDEX(A1:E1;SUMMENPRODUKT((B3:B9=$F$1)*SPALTE(B1)+(C3:C9=$F$1)*SPALTE(C1)+(D3:D9=$F$1)*SPALTE(D1)))))
ich habs leider nicht geschafft, die Matrix 2-dimensional zu formulieren, um die Wiederholung von (B3:B9=$F$1)*SPALTE(B1) zu vermeiden, aber bei 3 Spalten ist das noch überschaubar. Das doppelte wenn(Zählenwenn... brauche ich, weildie Restformel -für mich überraschend- bei fehlendem Argument trotzdem was bringt und dann lag es nahe, zwischen nicht und mehrfach auch noch zu unterscheiden.
Müsste das 2. < nicht ein > sein?
Ansonsten ist deine Formel ähnlich der Lösung von excelformeln.de
Dein Suchargument scheint in F1 zu stehen? dann:
=Wenn(Zählenwenn(B3:D9;$F$1)<1;"Suchargument nicht vorhanden ";Index(A1:D1;Summenprodukt((B3:B9=$F$1)*Spalte(B1)+(C3:C9=$F$1)*Spalte(C1)+(D3:D9=$F$1)*Spalte(D1))))
Die Bereiche musst du natürlich anpassen.
bei mehrfachem Auftauchen des Sucharguments ergibt sich ein #Bezug!-Fehler, ebenso beim Verschieben des Blocks nach rechts (nach unten gehts).
Habs leider nicht geschafft, das zu einer zweidimensionalen Matrixformel für viele Spalten zu kürzen, aber bei wenigen Spalten bleibt das hier ja überschaubar.
Ausserdem kannst Du Dich so auch für nicht zusammenhängende Spalten entscheiden.
F1 kannst Du natürlich woandershin verschieben
sorry, hab das anscheinend vorher schon losgeschickt gehabt und mich gewundert, wo's geblieben ist, -mir wurde weiterhin "keine Antworten" angezeigt- deshalb (nur leicht verbessert) nochmal.
Das mit wenn(...>1 funktioniert nicht, gibt Bezug-Fehler.
Bei vielen Spalten sehe ich nur eine Lösung mit einer Hilfszeile, ich kümmere mich gleich noch drum.
Da ich gar nicht weiß, auf welches Blatt Deiner Datei sich das beziehen soll (18 Spalten?), hier nochmal ein Vorschlag mit Hilfszeile zum selbst anpassen:
Hilfszeile 12, in B12 die Formel:
=(Summenprodukt((B2:B10=$F$1)*1)>0)*Spalte()
+Summenprodukt((B2:B10=$F$1)*1)/100
nach rechts kopieren, ergibt die Spaltennummern, in denen was zu finden ist
Mehrfachnennungen in eiiner Spalte werden durch die Hundertstel angezeigt.
Diese Zeile kannst du später ausblenden oder/und weit nach unten verschieben.
Dann Formel in A13 (Zeile12 blendest du ja später vllt aus):
=Index($B$1:$U$1;Kgrösste($B$12:$U$12;Zeile(A1)))
Das ergibt die Überschrift der letzten Spalte, wo das Suchargument auftaucht (die Nachkommastellen in B12:U12 stören nicht, index nimmt automatisch die Ganzzahl)
Das ein paar Zellen nach unten kopiert, ergibt die vorletzte, 3.-letzte usw. Spalte des Auftauchens und #WERT!, wenn nichts mehr kommt.
Sollte das #WERT! stören, einbetten in
=Wennfehler(...;"keine weitere Nennung")
Funktionierts?
Du kannst es mit dieser Formel lösen http://excelformeln.de/formeln.html?welcher=39 Es ist die vorletzte, unter "Spaltenüberschrift"
Nachtrag: Der Wverweis funktioniert deswegen nicht, weil dabei nach dem Suchbegriff nur in der ersten Zeile gesucht wird.
Super vielen vielen Dank!!
Die folgende Formel war/ist die Lösung:
=INDEX(A1:C1;SUMMENPRODUKT((A1:C7=F1)*(SPALTE(A1:C1))))Hab es auf das obige Beispiel angepasst.
Kannst du die Formel für deine große Tabelle hinschreiben? Wird der Suchbegriff genauso geschrieben und formatiert, wie er in der Tabelle vorkommt? (Manchmal gibt es versteckte Leerzeichen oder Zahlen als Text formatiert, dann wird nichts gefunden wegen nicht identischer Begriffe)
Falls deine Tabelle nicht in Spalte A anfängt, musst du trotzdem beim letzten Teil der Formel ab Spalte A(1) verwenden, mit entsprechend vielen Spalten wie in den anderen Teilen. Wenn also der Index von C1:F7 geht (4 Spalten), verwendest du SPALTE(A1:D1)
@ DeeDee: gut gesucht ist oft besser als das Rad neu erfunden, DH!
also ich hab den Bereich jetzt auf 19 Spalten erweitert (durch einfügen von Spalten !>mittendrin<!) und bekomme keinen Fehler! (Suchargument in T1):
=INDEX($A$1:$S$1;SUMMENPRODUKT(($A$1:$S$7=$T$1)*(SPALTE($A$1:$S$1))))
Hast du vllt Mehrfachnennungen?
Ich habe es nochmal mit der Formel von lamiam probiert.
Dopplungen treten auch auf, wobei es egal ist welchen der Werte dann genommen wird (der erste, der letzte oder zufällig).
Da ich merke das es mit der Formel "imaginär" basteln und dann anpassen zu Komplikationen führt, habe ich die Liste einfach mal "freigegeben".
Ich bitte euch mit Disskretion damit umzugehen.
Ich weiß das ich mich hier in einem Forum befinde und sämtliche Leute auf diese Datei dann zugreifen können. Daher habe ich auch ein kleines Copyright geschrieben das besagt, dass die Daten weder verändert noch weitergegeben werden dürfen. Das Dokument ist auch für den privaten Zweck gedacht und darf daher nicht kommerziell genutzt werden!
https://www.dropbox.com/s/2sn63mtmi94is53/Trainingsplan_v1.4.1_Default.xlsm?dl=0
Sobald eine Lösung für das Problem gefunden wurde, werde ich diese Datei aus der Freigabe löschen.
Die Formel funktioniert, wenn der Suchbegriff nur einmal vorkommt. Ansonsten wird etwas falsches berechnet und zurückgegeben.
Einen Ansatz um die Position eines mehrmals vorkommenden Begriffs zu finden, findest du hier http://excelformeln.de/formeln.html?welcher=58
Die so ermittelte Spaltenzahl kannst du dann als Spaltenindex verwenden.
Hallo,
vielen Dank für die Hilfe, das bringt mich schon mal ein ganzes Stück weiter. Nur hab ich das Problem das die Tabelle auf die ich das Anwenden will 18 Spalten hat :)
Ich werde mir gleich mal die Syntax zu der Formel angucken, hab diese mal eben nur kopiert, angepasst (da die Zellenbezüge nicht ganz gepasst haben) und getestet, klappt soweit...