Clever-Excel-Forum › Office-Anwendungen › Excel v
Sverweis aus Matrix mit |Pipes|

Thema geschlossen 
Baumstrukturmodus
Sverweis aus Matrix mit |Pipes|
niels1983 Offline
pausiert
Registriert seit: 05.11.2018
Version(en): 365
#1
20.12.2018, 16:20
Hallo Community,

ist es möglich einen Sverweis zu erstellen, in welchem mehrere Werte in einem Textfeld mit "Pipes" getrennt sind? 
Mein Suchkriterium umfasst eine 5 Stellige PLZ z.B. 24536
In dem Textfeld innerhalb der Matrix steht |24534|24536|24537|24539
Der Spaltenindex enthält die Information "Neumünster", diese soll mir aufgrund des Suchkriteriums angezeigt werden.
Ist das Möglich, oder funktioniert der Sverweis in meinem Fall nur mit einer 5 Stelligen Zahl? Huh

Hoffe das ist verständlich genug :19:

Viele Dank im Voraus
Suchen
Top
MisterBurns Offline
Benutzer
Registriert seit: 02.05.2018
Version(en): Excel 365 & 2016
#2
20.12.2018, 16:26
Hallo,

du kannst im SVERWEIS problemlos "*" vor und nach deinem Suchbegriff verwenden.
Code:
=SVERWEIS("*"&A1&"*";DeineTabelle!A1:B100;2;0)
Schöne Grüße
Berni
Suchen
Top
niels1983 Offline
pausiert
ThreadStarter
Registriert seit: 05.11.2018
Version(en): 365
#3
20.12.2018, 17:25 (Dieser Beitrag wurde zuletzt bearbeitet: 20.12.2018, 17:26 von niels1983.)
Vielen Dank Bernie,

das funktioniert bei mir irgendwie leider nicht.

Hab die Datei mal hochgeladen. In A befinden sich die Postleitzahlen, in B der zust. Kreis, bzw. die kreisfreie Stadt.
Über die Spalte C soll dann der Kreis ausgelesen werden. Bekomme immer Fehlerzeichen...so ein Sverweis hat sonst immer geklappt Huh

LG

Niels


Angehängte Dateien
.xlsx   Rohdaten_test.xlsx (Größe: 34,29 KB / Downloads: 6)
Suchen
Top
shift-del Offline
{Irrtum / Versuch * Glaskugel}
Registriert seit: 12.04.2014
Version(en): Office 365
#4
20.12.2018, 17:29
Moin

Du hast die Datei vermutlich woanders hochgeladen.
Was ist ein Fehlerzeichen?
Wir sehen uns!
... Detlef

Meine Beiträge können Ironie oder Sarkasmus enthalten.

Homepage Suchen
Top
niels1983 Offline
pausiert
ThreadStarter
Registriert seit: 05.11.2018
Version(en): 365
#5
20.12.2018, 17:47
Meinte #NV bzw. #WERT :19:


Angehängte Dateien
.xlsx   Rohdaten_test.xlsx (Größe: 34,29 KB / Downloads: 1)
Suchen
Top
Der Steuerfuzzi Offline
Excel Rose
Registriert seit: 11.03.2015
Version(en): mittlerweile meistens 2019
#6
20.12.2018, 17:50
Di Formel von Berni funktioniert doch:
Code:
=SVERWEIS("*"&C2&"*";A2:B200;2;FALSCH)
Wenn Du dann in C2 noch eine PLZ einträgst, die in der Liste vorkommt, dann gibt er auch den Kreis aus.
Gruß
Michael
Suchen
Top
WillWissen Offline
CEF-Moderator
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
#7
20.12.2018, 17:55
Hi,

mein Vorschlag:

Arbeitsblatt mit dem Namen 'Tabelle1'
ABCDE
1PLZ PoolKreis fiktivgesuchte plzKreisWo zu finden?
2|44623|44625|44627|44628|44629|44649|44651|44652|44653Herne44653HerneA2
3|45525|45527|45529|45549|58256|58285|58300|58313|58332|58339|58452|58453|58454|58455|58456Ennepe-Ruhr-Kreis47119hättste wohl gern#NV
4|34431|57392|59755|59757|59759|59821|59823|59846|59872|59889|59909|59929|59939|59955|59964|59969Hochsauerlandkreis45143hättste wohl gern#NV
5|58507|58509|58511|58513|58515|58540|58553|58566|58579|58636|58638|58640|58642|58644|58675|58706|58708|58710|58762|58769|58791|58802|58809|58840|58849Märkischer Kreis58453Ennepe-Ruhr-KreisA3
6|57368|57399|57413|57439|57462|57482|57489Olpe45307hättste wohl gern#NV
7|57072|57074|57076|57078|57080|57223|57234|57250|57258|57271|57290|57299|57319|57334|57339Siegen-Wittgenstein59425UnnaA9
8|58739|59457|59469|59494|59505|59510|59514|59519|59555|59556|59557|59558|59581|59590|59597|59602|59609Soest47179hättste wohl gern#NV
9|44532|44534|44536|58239|58730|59174|59192|59199|59368|59379|59423|59425|59427|59439Unna33154hättste wohl gern#NV
10|64283|64285|64287|64289|64291|64293|64295|64297Darmstadt59199UnnaA9
11|60311|60313|60314|60316|60318|60320|60322|60323|60325|60326|60327|60329|60385|60386|60388|60389|60431|60433|60435|60437|60439|60486|60487|60488|60489|60528|60529|60549|60594|60596|60598|60599|65929|65931|65933|65934|65936Frankfurt am Main45481hättste wohl gern#NV

ZelleFormel
D2=WENNFEHLER(INDEX(B:B;VERGLEICH("*"&C2&"*";A:A;0));"hättste wohl gern")
E2="A"&VERGLEICH("*"&C2&"*";A:A;0)
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Suchen
Top
Jockel Offline
VUP & {Formelsympathisant}
Registriert seit: 10.04.2014
Version(en): Office 2019
#8
20.12.2018, 17:58
Hallo, ich habe das so verstanden..: 

PHP-Code:
=WENNFEHLER(VERWEIS(42;1/SUCHEN(C2;$A$2:$A$1000);$B$2:$B$1000);"") 
Gruß Jörg
stolzes Mitglied im ----Excel-Verein
Freund einer excellenten Power Query-Abfrage
Suchen
Top
niels1983 Offline
pausiert
ThreadStarter
Registriert seit: 05.11.2018
Version(en): 365
#9
20.12.2018, 18:31
Vielen Dank für eure vielen Vorschläge. :19:

Leider reicht es bei meinen Excel-Kenntnissen gerade für einen SVERWEIS.

Ich habe meine Tabelle nochmal aktualisiert und komplettiert. Es geht darum aus 13.115 Postleitzahlen aus einer entsprechenden PLZ den Ort anzuzeigen.

Hoffe ich bringe euch jetzt nicht auch noch durcheinander wie mich Blush


Viele Grüße


Angehängte Dateien
.xlsx   Rohdaten_test2.0.xlsx (Größe: 335,72 KB / Downloads: 4)
Suchen
Top
WillWissen Offline
CEF-Moderator
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
#10
20.12.2018, 18:38
Hi,

wenn du, wie du schreibst, nur den SVERWEIS() kennst, dürfte das bei deinem jetzigen Beispiel (das im Übrigen absolut nichts mit deinem Eröffnungspost zu tun hat) doch kein Problem darstellen.

Arbeitsblatt mit dem Namen 'Tabelle 1'
ABCD
1plzortgesuchte plzKreis
278267Aach59067
354298Aach45143
452062Aachen58453
552064Aachen58453
652066Aachen45307
752068Aachen59425
852070Aachen47179
952072Aachen33154
1052074Aachen59199Bönen
1152076Aachen45481
1252078Aachen47269
1352080Aachen22303
1473430Aalen45479
1573431Aalen45479
1673432Aalen45479
1773433Aalen47445
1873434Aalen59557
1965326Aarbergen45479
2025560Aasbüttel45131
2191183Abenberg59069
2293326Abensberg58239
2355767Abentheuer45356
2491720Absberg59955
2574232Abstatt47918

ZelleFormel
D2=WENNFEHLER(SVERWEIS(C2;$A$2:$B$2000;2;0);"")
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Suchen
Top
Thema geschlossen 


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste