Office: (Office 2010) Sverweis mit 2 Kriterien nach Teilstring einer Zelle suchen lassen

Hallo,

Ich habe eine zusammengeschriebene Ordnungsziffer "727033808056" die mir die genaue Hierarchie einer Einheit beschreibt.

Beispiel: 72 70 3380 8056
Geschäftsbereich Niederlassung Abteilung Arbeitsplatz

Wie kann ich jetzt mittels eines SVerweises o.ä. nach dem Teilstring z.B. "70" suchen lassen?

Zusätzlich darf der SVerweis das Ergebniss (den Ort) nur ausgeben wenn er "70" gefunden hat & zusätzlich in der gefundenen Zeile der Ort auch noch als Niederlassung (NL) gekennzeichnet ist.

Wie funktioniert das?

Habe es mit =SVERWEIS(VERKETTEN(TEIL(A7;3;2));A1:C99;2;FALSCH) probiert. Das will nicht so

Vielen Dank für die Hilfe

 

Hola,

warum Verketten(), du verkettest doch gar nichts?

Und "will nicht so" hilft nur dir weiter.

Ich tippe, dass du TEXT mit ZAHLEN vergleichst. Ins Blaue:

Code:

=SVERWEIS(TEIL(A7;3;2)*1;A1:C99;2;FALSCH)

Gruß,
steve1da

 

Schon mal vielen Dank vorab.

Habe Anstelle von Verketten auch mal mit Links probiert, dass funktioniert zwar als Formel an sich, gibt leider immer nur #NV aus.
Nur mit Teil wie von dir vorgeschlagen klappt. Daher vielen Dank :-)

Jetzt stehe ich noch vor dem problem, dass ich 2 Kriterien da rein bringen muss. Einmal natürlich die Zahl (70) und zusätzlich die kennzeichnung (NL) nur dann soll der Wert ausgegeben werden.

Vielen Dank.

 

Hola,

Code:

=INDEX(B1:B99;SUMMENPRODUKT((TEIL(A7;3;2)*1=A1:A99)*(C1:C99="NL")*ZEILE(1:99)))

für den Fall dass die Kennzeichnung in Spalte C steht.

Gruß,
steve1da

 

Das sieht jetzt (wie erwartet) verdächtig nach der Formel aus die mir weiter hilft. Danke.

Habe sie mal auf meine Bedürfnisse umgebaut (wohl ein Fehler) denn sie funktioniert leider nicht da sie nur #NV ausgibt. Habe ich da evtl einen Denkfehler?

=INDEX(Adressliste!G:G;SUMMENPRODUKT((TEIL(A7;3;2)*1=Adressliste!D: D)*(Adressliste!A:A="NL")*ZEILE(1:9999)))

In Adressliste Spalte G: steht der Name der Ausgegeben werden soll.
In Adressliste Spalte D: steht die Zahl die verglichen werden soll z.B. 70
In Adressliste Spalte A: steht die Kennzeichnung z.B. "NL"

Das Tabellenblatt Adressliste ist 1700 Zeilen lang

Im aktuellen Tabellenblatt in A7 steht der Wert der vom Teilsting als gesuchter Wert vorgegeben wird.

Stimmt evtl meine Anordnung nicht?

Danke im Voraus

 

Hola,

#NV erscheint, weil die Bereiche nicht gleicht groß sind. Du fragst ganze Spalten ab, bei ZEILE am Ende steht aber nur 1:9999.
Ganze Spalten sollte man auch tunlichst vermeiden - die machen das ganze nur unnötig langsam.

Wenn du doch genau weißt, wie groß die Bereiche sind, dann schreib das auch in die Formel :)

Gruß,
steve1da

 

Hallo,

dann versuche es mal hiermit:

=INDEX(Adressliste!G:G;SUMMENPRODUKT((TEIL(A7;3;2) *1=Adressliste!D: D)*(Adressliste!A:A="NL")*ZEILE(A:A)))

 

Super, danke euch beiden! Funktioniert tadellos! Lag wirklich nur an den verschieden großen Bereichen.

So lange wie ich da immer Tüftle könnte ich mir das fast alles per Hand abschreiben und wäre schneller :-)