Excel Formel mit INDEX?
Hallo zusammen,
ich bin auf der Suche nach einer Formel, bekomme es selber aber nicht hin. Ich möchte den Wert aus der gefundenen Zelle mit Inhalt zurückgeben, wenn bestimmte Kriterien erfüllt sind.
INDEX: (Wenn im Suchbereich Zusatz und Straße und Ort korrekt) und Hausnummer im Bereich, dann gib mir den Zellinhalt von E zurück.
Anbei auch ein Foto.
Großes DANKE schon einmal.
Nachtrag: Auf dem Foto ist zu sehen "... dann gib mir den Zellinhalt von E zurück. Es muss aber J sein = Ortsteil
Nachtrag
2 Antworten
Wenn ich Dich richtig verstanden habe willst Du aus x+x+x den Ortsteil angezeigt bekommen.
Dann z.B. so
Nicht mehr. Ich hatte mehrere Varianten. Auf G kann man verzichten. Vergessen rauszunehmen.
Ah okay. Und welche Formel hast Du unter A13? Wenn ich nur die beiden Formeln nehme, bekomme ich #NAME? in F13 zurück.
In A13 steht einfach nur Wahr, das wird gesucht in N:N
Du hast zwar was von Index geschrieben, aber muss es unbedingt mit Index gelöst werden, ich denke mit dem Filter (Sofern Excel 2021 und neuer vorhanden ist), ist es deutlich einfacher:
Ich musst deine Daten ein wenig verändern, da sie sonst nicht eindeutig genug waren und mehrere Ergebnisse raus kamen. Möchtest du dies um jeden Preis verhindern, kannst du auch @Zeichen vor Filter schreiben, Achtung jedoch du wirst dann nicht erfahren, wenn du nicht eindeutige Datensätze hast.
Hey, Danke schon einmal für Deine Rückmeldung. Das mit der Filterung hat nicht geklappt, aber ich verstehe jetzt auch, was Du meinst mit den eindeutigen Werten. Ich guck mir das morgen noch einmal an, vielleicht ist es auch schon zu spät heute.
Mein erster Gedanke war über ein Hilfsmerkmal zu arbeiten. Das habe ich dann aber auch ggf. mehrfach. Selbst wenn es eindeutig wäre, wüsste ich nicht, wie man im zweiten Teil den Hausnummernbereich dann korrekt abfragt.
Gibt es eine Möglichkeit mit INDEX erst den Hausnummernbereich abzufragen =1. Teil und dann das Hilfsmerkmal =2. Teil?
Danke für die Rückmeldung, warum hat denn der Filter nicht grklappt?
Das Problem an INDEX was eigentlich immer mit vergleich kombiniert wird, ist wie auch SVERWEIS oder XWERWEIS. Es gibt dir das erste gefundene Element zurück und zwar entweder wie bei VERGLEICH/SVERWEIS als Spalten/Zeilenindex oder bei XVERWEIS als Zellmatrix.
Fragst du also 2 verschiedene Straßen in Dorfhausen dann kann VERGLEICH nicht entscheiden wenn es die Bedingung Dorfhausen prüft, welche es davon jetzt nehmen soll, nimmt also einfach das erste, du könntest hier maximal abfragen ob alle Elemente aus den verschiedenen Spalten die gleiche Zeile zurück geben (genau hier kommt aber das Problem mit dem ersten Element zu tragen (siehe Beispiel unten). Jetzt aus dem Bauch heraus würde ich sagen man kann den INDEX auch nicht so kombinieren dass man beides zu gleich abfragt außer, wenn du alle Werte miteinander verketten würdest. Könnte aber für andere Probleme sorgen.
Beispiel:
Dorfhausen Dorfstraße
Dorfhausen Dorfweg
Jetzt fragst du in deiner Tabelle nach Dorfhausen VERGLEICH gibt dir also die Zeile 1 zurück, im Anschluss fragst du nach dem Dorfweg was dir Zeile 2 zurück gibt und schon hast du unterschiedliche Zeilen, da VERGLEICH eben einfach das erste Ergebnis ungeachtet der Ergebnisse die im Anschluss raus kommen.
Wenn du mir sagst welche Excel Version du hast kann ich dir gerne eine Lösung ausarbeiten die ohne den Filter auskommt.
@Gipfelstuermer Ah, verstehe. Danke schon einmal für die Infos und die Unterstützung. Hab das mit den Filter jetzt auch noch einmal versucht und hier sagt er mir jedes Mal "Diese Funktion ist ungültig".
Ich stelle Windows nachher/ spätestens morgen auf 11 um und kann Dir danach die Version gerne noch einmal schicken. Die aktuelle ist 1808 (Build 10415.20025). Die Lösung ohne Filter klingt natürlich spannend. Wenn Du die Zeit hast, wäre das natürlich prima, ich lerne ja auch gerne dazu
Noch einmal zur Filterfunktion:
Was passiert, wenn man darüber keinen passenden Treffer aus der Liste erhält? Was gibt es als Wert zurück? (Ich überlege, ob ich die Mehrfacheinträge an einer Stelle bewusst ändere. Ich könnte den Zusatz auf X o.ä. setzen. Über die Filterung sagt er mir im besten Fall, dass er den Treffer nicht gefunden hat und ich dann weiß, dass ich mir das selber ansehen muss und dann aktiv den korrekten Eintrag auswähle.) Das wäre für mich vom Handling her etwas unschöner, aber auch machbar bzw. in Ordnung.
Okay, nach dem Update ist das immer noch diese Version: 1808 (Build 10415.20025)
Das mit der Filterung bekomme ich nicht hin. Da sagt er mir immer noch "Funktion ist ungültig".
Das klingt mir wie Office 2019, das kannte noch keinen Filter und XVerweis, da bleibt dir tatsächlich leider nur Index Vergleich oder der SVerweis, eine Idee, dir mir aber auch noch kam war in DropDown, dass sich automatisch anpasst mit jeder Eingabe die du eingibst.
Sprich gibst du vorne aktiv ein tauchen in den Folgenden Spalte nur noch die auf, die auch in der ersten Spalte den Status aktiv haben, klickst du dort nun auf Dorfstraße müssen für die nächste Spalte beide Kriterien erfüllt sein und noch die Werte, für die das zutrifft werden angezeigt usw. usf. so, dass die Auswahl nach hinten hin immer kleiner werden sollte.
Könnte dir das helfen.
Leider ist das in der Version 2019 auch etwas komplizierter, da die nützliche Funktionen wie eindeutig / Filter / Zeilenwahl etc. fehlen. Daher muss man sich das alles etwas mühselig zusammenbauen, aber nichts ist unmöglich. Man bekommt auch den Filter mit Bordmitteln aus 2013 hin, ist nur ca. 10x so lang als Formel :D
Hey, Danke für die Idee. Kommt die Formel von G2 wirklich in G2?