Erledigt!!! Abfrage in Excel mit INDEX und VERGLEICH zeigt immer nur den 1. Treffer an und nicht alle gelöst

[udz300862]

Hallo zusammen,

ich habe in Excel 2007 eine Dadenbank erstellt mit Orten, den dazugehörigen Städten und Preise für bestimmte Personenanzahl.

Jetzt habe ich eine Suche in der Spalte Ort/Stadtteil gemacht und einen Treffer bekommen und die dazugehörige Infos und die werden angezeigt.
Mein Problem. Ich muss genau den Ort eingeben und bekomme nur den ersten Treffer.

Was ich erfolglos versucht habe ist das ich nur ein Teil des Ortes angeben ("Lenders" anstelle von "Lendersdorf") kann.
Weiterhin solle alle Treffer angezeigt werden (gebe "Düren" ein und es soll "Düren City" "Düren Ost" "Düren Süd" angezeigt werden).



Gruss
Udz

 

[totsch]

du solltest es mal mit "Spaltenverweis mit Suche nach teilweiser Übereinstimmung" versuchen >>

=INDE$A:$H;VERGLEICH("*"&$J$2&"*";$A:$A;0);2)

"*"&$J$2&"*" >> sucht nach einem Teilstring, den du in J2 eingibst

 

[Dancefree]

Hallo udz300862,

die Funktion „Vergleich“ (Match) mit dem Vergleichstyp 0 gibt nach meinem Wissen IMMER nur den ersten gefunden Wert zurück
(unabhängig davon, ob das Suchkriterium mit Wildcards versehen ist).
Problem dürfte so wohl noch nicht gelöst sein – oder?

Gehe ich recht in der Annahme, dass Dein eigentliches Ziel folgendes ist:
- Du hast in einem Arbeitsblatt eine Tabelle mit beschriebenen Zeilen und Spalten erstellt
- diese Tabelle ist ein Listobject (im Menü „Start“ --> „Als Tabelle formatieren“ gewählt)?
- abhängig vom Ort, der Personenanzahl und der damit zusammenhängenden Preise gibt es auch ein Attribut der benötigten Fahrzeuge, welches von der Personenzahl abhängig ist?

Wonach möchtest Du genau filtern?
Abhängig vom eingegeben Ort soll die Abfrage den Ort, alle Preise und benötigten Fahrzeuge bei allen Personenanzahlen anzeigen?
Abhängig vom eingegeben Ort soll die Abfrage den Ort, den Preis und die benötigten Fahrzeuge bei einer bestimmten Personenanzahl anzeigen?

 

[totsch]

evtl. ist auch das die Lösung für dich >> alle Zellen auflisten, die eine bestimmte Zeichenfolge enthalten

Hier mal ein Beispiel mit der Formel in F2:

{=WENN(ZEILE()-1>SUMME(ISTZAHL(SUCHEN($D$2;A$1:A$100))*1);"";INDEA:A;KKLEINSTE(WENN(ISTZAHL(SUCHEN($D$2;A$1:A$100));ZEILE($1:$100));ZEILE(A1))))}



Beachte Matrixformel!!!
Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!

 

[udz300862]

Hallo und schon einmal vielen Dank für Eure Hilfe.

Ich arbeite noch dran und versuche die Vorschlage durch zu gehen.

Antwort auf die Frage von Dancefee:
"Wonach möchtest Du genau filtern?
Abhängig vom eingegeben Ort soll die Abfrage den Ort, alle Preise und benötigten Fahrzeuge bei allen Personenanzahlen anzeigen?
Abhängig vom eingegeben Ort soll die Abfrage den Ort, den Preis und die
benötigten Fahrzeuge bei einer bestimmten Personenanzahl anzeigen?"

Es soll gefiltert werden nach Ort und alle Preise angezeichnet werden. Die Texte vor und nach den Preisen sind immer gleich.

Gruss
UDZ

 

[totsch]

Was ich erfolglos versucht habe ist das ich nur ein Teil des Ortes angeben ("Lenders" anstelle von "Lendersdorf") kann.
Weiterhin solle alle Treffer angezeigt werden (gebe "Düren" ein und es soll "Düren City" "Düren Ost" "Düren Süd" angezeigt werden).

die Lösung dazu habe ich dir bereits aufgezeigt >> siehe #4

zu deinen weiteren Fragestgellungen (#5) ist es am besten, du machst einen Screenshot und erklärst dann genauer den Aufbau deiner Tabelle und was du erreichen möchtest. Den Screenshot machst du am besten von einer kleinen Muster-Beispieltabelle und nicht von deiner umfangreichen Originaltabelle. Hauptsache das Prinzip wird erkannt. Die Formellösungen kannst du dann ja auf dein Original später anpaassen.

 

[NeHe]

ist es am besten, du machst einen Screenshot

Hier gleich der Hinweis, die Forenregeln zu beachten. Die im Startpost ehemals vorhandenen Bilder wurden entfernt, da trotz Konversation keine Änderung erfolgte.


MfG

 

[Dancefree]

Ich denke auch, dass Dir totsch (nach jetziger, unvollständiger Info) mit dem besagten
Excel-integrierten Befehlen alles an die Hand gegeben hat um das Problem zu lösen.?
Mehr Info bitte.
Weiterhin:
Ist Deine Abfragetabelle auf dem selben Arbeitsblatt enthalten wie die eigentliche "Datentabelle" oder in einem separaten Arbeitsblatt?
Ich habe bei deinen ersten (glöschten) Screenshots in Deiner "Abfragetabelle" die Spalte "Anzahl Fahrzeuge" (oder so ähnlich) gesehen.
Es ist klüger, bei einer Abfrage keine hardcodierten Einträge zu haben.
Erstelle besser eine weitere "Datenbank" in einem weiteren Tabellenblatt in denen Du diese Werte einträgst.
Deine eigentliche "Abfrage" sollte sich dann die Daten aus den beiden "Datentabellen" holen.
Ohne Nutzung von VBA ist dies dann leichter zu realisieren.

 

[udz300862]

Hallo NeHe,

ich bitte vielmals um Entschuldigung das ich 3 Bilder eingestellt habe.

An totsch,

Dein Vorschlag klappt bestens und alle Treffer werden angezeigt.
Ich habe jetzt versucht die Felder neben dem Suchergebnis auch anzeigen zu lassen.
Leider erfolglos.
Ich habe die Matrix von A:A auf A:H erweitert kommt die Meldung #Bezug.

Da die Bilder aus der Betrag #1 gelöscht wurden habe ich jetzt ein neues hinein gemacht mit der angepassten Zellen.

an Dancefee

alle Daten sind in der selben Excel Arbeitsblatt wie die Abtragetabelle.

Schon mal vielen Dank für eure Hilfe.

Gruss
Udz

 

[totsch]

Ich habe die Matrix von A:A auf A:H erweitert kommt die Meldung #Bezug.

die Matrix mit INDEA:A;...... bezieht sich immer nur auf eine Spalte, d.h. der Ausgabewert befindet sich in Spalte A.
bei bei INDEA:H;...... "weiß" Excel nicht, ob der Wert aus Spalte A oder B oder C usw. ausgegeben werden soll >> deshalb
auch der Fehlerwert #Bezug!

versuche es so wie in der Abb. mit den entsprechenden Formeln

G5 {=WENN(ISTFEHLER(INDEA:A;KKLEINSTE(WENN(ISTZAHL(FINDEN($B$1;A:A));ZEILE($1:$103));ZEILE(A1))));"";INDEA:A;KKLEINSTE(WENN(ISTZAHL(FINDEN($B$1;A:A));ZEILE($1:$103));ZEILE(A1))))}
G5 nur nach unten kopieren

H5 {=WENN($G5="";"";INDEB:B;VERGLEICH($G5;$A:$A;0)))}
H5 nach rechts und unten kopieren

Beachte zukünftig:
#11 >> Es ist maximal ein Bild pro Beitrag erlaubt. Maximalgrösse für Bilder: max. 750x750

 

[udz300862]

Hallo Zusammen.

Ich danke euch für eure Hilfe.

So wie von totsch vorgeschlagen klappt es bestens.
Dir einen besonderen Dank.

LG
UDZ

 

[totsch]

in meinem obigen Beispiel wird nach dem Stadteil gesucht und als Ausgabe erfolgt die zugehörige Stadt.

Habe aber durch deinen nachträglichen Screenshot aber bemerkt, dass du genau umgekehrt suchen möchtest >> Teilstringsuche nach Ort und als Ausgabe soll der Stadtteil ausgegeben werden. Da die Ortssuche (in meinem Beispiel "Düren") mehrfach vorkommt, sind die Formellösungen etwas anders.
Hier die Lösung:

G5 {=WENN(ISTFEHLER(INDEB:B;KKLEINSTE(WENN(ISTZAHL(SUCHEN($G$1;B:B));ZEILE($1:$999));ZEILE(A1))));"";INDEB:B;KKLEINSTE(WENN(ISTZAHL(SUCHEN($G$1;B:B));ZEILE($1:$999));ZEILE(A1))))}
Formel nach unten kopieren

H5 =INDEA:A;VERGLEICH(G5;B:B;0))

H6 {=WENN(ODER(G6="";MAISTNV(VERGLEICH(A:A;H$5:H5)*(B:B=G5))=0);"";INDEA:A;MIN(WENN(ISTNV(VERGLEICH(A:A;H$5:H5)*(B:B=G5);ZEILE($1:$999)))))}
Formel nach unten kopieren

I5 {=WENN($G5="";"";INDEC:C;VERGLEICH($H5;$A:$A;0)))}
Formel nach rechts und nach unten kopieren

denk an die umrandeten, geschweiften Klammern { } (Matrixformeln), die du NICHT mit eingibst,
sondern die Formel mit STRG+SHIFT+RETURN abschließt! Dadurch werden die { } automatisch erzeugt.

Hoffe du bist nun zufrieden