Excel Tabelle 2 Suchkriterien?
Hallo Leute,
ich brauche mal Eure Hilfe. Die Funktion SVERWEIS ist mir bekannt, auch mit 2 Suchkriterien. Hier stoße ich aber an meine Grenzen, da die Spalten nicht nebeneinander liegen bzw über mehrere Spalten gesucht werden soll.
Ich möchte (siehe Foto) in dieser Tabelle nach einem Datum und einem Namen suchen. Gesucht werden soll der Name in mehreren Spalten, hier C1:E12. Dazu passend soll dann in G1:I12 die jeweilige Zahl gesucht und ausgegeben werden.
Vielen Dank.
4 Antworten
Update:
Viel zu kompliziert gedacht
V1 =ZUZEILE(WENN((C1:E12=E16)*(A1:A12=E15);G1:I12;#NV);2)
V2 =MAX(WENN((C1:E12=E16)*(A1:A12=E15);G1:I12;""))
Im Normalfall sollte hier ja nur ein Wert auftauchen, daher ist V2 vollkommen ausreichend und sehr abwärtskompatibel.
Handel es sich hingegen in den Spalten G bis I um Texte wird V2 nicht funktionieren.
- - - alte Antwort
Auch wenn viele mir was anderes erzählen wollen, aber Abseits des Grundes der Kompatibilität ist SVERWEIS eigentlich tot und selbst da wird er meist durch die bessere Variante aus Index+Vergleich ersetzt. Denn genau das kann der SVERWEIS nicht in mehreren Spalten gleichzeitig suchen. Ich verfolge hier einen sehr ähnlichen Ansatz wie daCypher nur mit einer anderen herangehensweise.
=LET(Spaltenindex;NACHZEILE(C1:E12;LAMBDA(s;VERGLEICH(E16;s;0)));
INDEX(FILTER(G1:I12;(A1:A12=E15)*ISTZAHL(Spaltenindex));;@ZUSPALTE(Spaltenindex;2)))
Leider ist dein Datensatz äußerst ungünstig ich hoffe mal deine echten Daten enthalten keine Werte die sowohl in Spalte 1 als auch in Spalte 2 zu finden sind, zu Demonstrationszwecken habe ich dir mal die Namen "Sascha" (funktioniert Normal) und "Anna" wird mehrfach gefunden herausgepickt.
Sascha
Anna
zu vergleichszwecken habe ich mal daCypher seine Formel mit eingefügt, auch diese liefert hier in #NV, was vollkommen richtig ist, da keine eindeutige Zuordnung gefunden werden kann.
Durch die Nachzeile Funktion, liefert meine Variante zumindest einen Index für die Spalte, jedoch stimmt logischerweise nur eines der beiden Ergebnisse und es gibt einen Überlauf, der sicherlich nicht erwünscht ist
Würde mich zum letzten Punkt über Rückmeldung freuen.
Ich zerlege sowas immer gerne in mehrere Teilprobleme. Das geht ganz gut mit der LET-Funktion. Erst die Zeile suchen, um die es geht, dann schauen, in welcher Spalte der Name steht und dann beides zusammenführen:
=LET(
Zeile; FILTER($A$1:$I$12;($A$1:$A$12=D15)*(($C$1:$C$12=D16)+($D$1:$D$12=D16)+($E$1:$E$12=D16)));
Spalte; VERGLEICH(D16;Zeile;0);
INDEX(Zeile;1;Spalte+4)
)
Die Formel hat aber ein Problem damit, wenn ein Name mehrmals an einem Datum vorkommt, wie in deinem Beispiel mit "Anna". Dann kommt als Ergebnis nur #NV raus.
Wie wäre es abseits deiner Tabelle also z.B. etwas weiter rechts, mit einer Hilfszelle. Hier suchst du zuerst nach dem Namen. In der Zelle daneben suchst du mit einem weiteren SVERWEIS nach der Zahl. Diese zeigst du dann in deiner eigentlich Tabelle.
Vorteil:
- Die Formel wird übersichtlicher
- Excel bekommt nicht die Krise
Nachteil:
- Wenn die Tabelle nicjt nur für dich ist musst du darauf hinweisen das sich außerhalb noch was befindet
Wenn die Zelle nn der Spalte fixiert ist kannst du sagen "diesen Wert aus dieser Zelle x Zellen weiter links bzw rechts verwenden".
@ImmerAufTour ich stehe auf dem Schlauch. Kannst du mir die passende Formel zu deiner Idee geben?
Vielleicht hilft dir das weiter:
https://www.herber.de/forum/archiv/144to148/144253_Suche_Wert_und_gebe_daneben_stehenden_aus.html
@ImmerAufTour nee, ich stehe völlig auf dem Schlauch. Trotzdem Danke. :)
Du kannst nach einem Wert suchen, aber diesen nicht anzeigen lassen, sondern den Wert der z.B. 7 Zellen weiter links steht.
z.B. so, wenn Du die Ergebnisse der Spalten G+H+I addiert haben willst.
in D17
=WENNFEHLER(XVERWEIS(D15&D16;A1:A12&C1:C12;G1:G12);0)+WENNFEHLER(XVERWEIS(D15&D16;A1:A12&D1:D12;H1:H12);0)+WENNFEHLER(XVERWEIS(D15&D16;A1:A12&E1:E12;I1:I12);0)
in E17
=WENNFEHLER(XVERWEIS(E15&E16;A1:A12&C1:C12;G1:G12);0)+WENNFEHLER(XVERWEIS(E15&E16;A1:A12&D1:D12;H1:H12);0)+WENNFEHLER(XVERWEIS(E15&E16;A1:A12&E1:E12;I1:I12);0)
@GutenTag2003 bei mir funktioniert die Formel nicht, es kommt immer 0 raus. Mir ist auch aufgefallen, dass ich einen Fehler in der Tabelle hab. Habe mich gewundert wieso du was addieren willst. Jeder Name kommt (eigentlich) bei einem Datum immer nur einmal vor.
Die Namen kommen mehrfach an einem Datum vor z.B. Anna am 1.8. in C und D, am 3.8. ebenso, Paul am 3.8.. in E und D
Bietet Deine Excel-Version den XVerweis an?
Die Abfrage zu D15&D16; und E15&E16 muss ggf. angepasst werden.
Auch ne interessante Lösung, aber warum WENNFEHLER, nutze doch einfach den WENN_NICHTS_GEFUNDEN Parameter des XVerweis:
=XVERWEIS(D15&D16;A1:A12&C1:C12;G1:G12;XVERWEIS(D15&D16;A1:A12&D1:D12;H1:H12;XVERWEIS(D15&D16;A1:A12&E1:E12;I1:I12)))
kürzt die Formel deutlich ein.
aber mit einer Hilfszelle hab ich doch trotzdem das Problem, dass in jeweils 3 Spalten gesucht werden muss. Oder wie meinst du das?