Sverweis ohne genauen Text

Tobias_M_bc4893 · 17. Januar 2008 um 16:12

Hallo zusammen,

kann man einen Sverweis auch mit der Suche nach Textpassagen machen.

Problem ist folgendes.
Ich habe Tabellenblatt 1 in dem unter anderem jeweils eine spalte für Nachname und eine für Vorname gibt.

Und habe Tabellenblatt 2 bei dem Vorname und Nachname in einer Spate stehen.

Gibt es einen Sverweis der mir nun Tabellenblatt 2 nach Name durchsucht und mir dann beispielsweise die Telefonnummer angibt.

Freu mich auf eure Antworten.

Reinhard_fb8cb7 · 17. Januar 2008 um 16:50

Hi Felix,

kann man einen Sverweis auch mit der Suche nach Textpassagen
machen.

m.W. nach nicht.

Ich habe Tabellenblatt 1 in dem unter anderem jeweils eine
spalte für Nachname und eine für Vorname gibt.
Und habe Tabellenblatt 2 bei dem Vorname und Nachname in einer
Spate stehen.
Gibt es einen Sverweis der mir nun Tabellenblatt 2 nach Name
durchsucht und mir dann beispielsweise die Telefonnummer
angibt.

Grundsätzlich wäre dann das Problem wenn das ginge nur nach Vornamen zu suchen, was machst mit doppelten Vornamen!?

Nimm doch diesen Sverweis:
A1=Vorname
B1=Nachname

=Sverweis(A1&" "&B1;Tabelle2!A:e;3;0)

Gruß
Reinhard

Freu mich auf eure Antworten.

Tobias_M_bc4893 · 18. Januar 2008 um 08:17

Hi,

danke super hat geklappt. Sieht nun so aus:
=(SVERWEIS(A1&" "&B1;Tabelle1!B7:Q1500;5;FALSCH))

Noch zwei Fragen, was ist der unterschied wenn ich „FALSCH“ am Ende einsetze bzw eine „0“?

Und wie kann ich es möglich machen, dass er mir auch mehrer Daten in die andere Liste „rüberzieht“.

Also nehmen wir an Hans Müller steht zweimal in Tabelle1 und ich möchte, dass er mir mit Hilfe des Sverweises beide Telefonnummern in meine anderes Tabellenblatt kopiert.

Gibt es eine Lösung dafür?

Felix

Alex_Winter · 18. Januar 2008 um 09:01

Hi Felix,

danke super hat geklappt. Sieht nun so aus:
=(SVERWEIS(A1&" "&B1;Tabelle1!B7:Q1500;5;FALSCH))

Noch zwei Fragen, was ist der unterschied wenn ich „FALSCH“ am
Ende einsetze bzw eine „0“?

Beim SVerweis gibt es zwei Möglichkeiten, den vierten Parameter zu formulieren. 1 oder wahr bzw. das von dir verwendete 0 oder falsch. Bei 0 wird die genaue Übereinstimmung gesucht, bei 1 der nächstgrößere Wert.

Und wie kann ich es möglich machen, dass er mir auch mehrer
Daten in die andere Liste „rüberzieht“.

Schau dir dazu mal folgende Formeln an: http://www.excelformeln.de/formeln.html?welcher=28

Gruß Alex

Tobias_M_bc4893 · 18. Januar 2008 um 09:12

hi,
ja habe ich auch dran gedacht, allerdings hätte ich die Ergebnisse gerne in einer Zelle mit Komma getrennt,
alos z.B.
069-2545511;0171-5566612;…

Geht das auch irgendiwe?

Felix

Alex_Winter · 18. Januar 2008 um 09:23

hi felix,

Klar geht das, aber (meines Wissens) nur mit Hilfsspalte. Du müsstest halt die Ergebnisse deines SVerweises mit & verketten.

Gruß Alex

Tobias_M_bc4893 · 18. Januar 2008 um 09:30

DANKE,
also mit der Index-Funktion komme ich nicht klar,

wenn ich den Sverweis mit „&“ verbinde bekomme ich natürlich das gleiche Ergebnis doppelt.

Geht es wirklich nicht, dass ich mit hilfe des Sverweises alle Ergebnisse geliefert bekomme?
Felix

Alex_Winter · 18. Januar 2008 um 10:07

Hallo Felix!

Schick mir doch einfach mal die Datei rüber ([email protected]) ich werde sie anschauen und dir Bescheid geben, wenn ich die Lösung habe. Es könnte aber auch sein, dass du einfach nur vergessen hast, die Matrix-Formel mit Strg+Shift+Enter abzuschließen. Probier das mal aus, bitte, bevor du mir die Datei schickst.

Gruß Alex

Verena_7dd250 · 18. Januar 2008 um 10:13

Hallo,

Problem ist folgendes.
Ich habe Tabellenblatt 1 in dem unter anderem jeweils eine
spalte für Nachname und eine für Vorname gibt.

Und habe Tabellenblatt 2 bei dem Vorname und Nachname in einer
Spate stehen.

Gibt es einen Sverweis der mir nun Tabellenblatt 2 nach Name
durchsucht und mir dann beispielsweise die Telefonnummer
angibt.

Versuch’s doch mal mit dem Verbinden der Zellen: Wenn z. B. im gesuchten Bereich „Vorname Name“ steht und in dem anderen „Name“ und „Vorname“ - sagen wir in Spalte A der Name und in B der Vorname -, dann könntest Du in einer Zusatzspalte schreiben =B1&" "&A1, worauf das Ergebnis dann der Vorname aus B1 und nach einem Leerschritt der Name aus A1 wäre. Diese Formel einfach runterziehen und dann den Inhalt als Suchkriterium benutzen.
Hilft das?
Gruß, Verena

Reinhard_fb8cb7 · 19. Januar 2008 um 19:31

also mit der Index-Funktion komme ich nicht klar,
wenn ich den Sverweis mit „&“ verbinde bekomme ich natürlich
das gleiche Ergebnis doppelt.
Geht es wirklich nicht, dass ich mit hilfe des Sverweises alle
Ergebnisse geliefert bekomme?

Hi Felix,

für die ersten 3 Egebnisse ginge das so:
=SVERWEIS($A2;Tabelle2!$A:blush:B;2;0)

=SVERWEIS(A2;INDIREKT(„Tabelle2!A“&VERGLEICH(A2;Tabelle2!A:A;0)
+1&":B1000");2;0)

=SVERWEIS(A2;INDIREKT(„Tabelle2!A“&VERGLEICH(A2;Tabelle2!A:A;0)
+VERGLEICH(A2;INDIREKT(„Tabelle2!A“&VERGLEICH(A2;Tabelle2!’:A;0)
+1&":A1000");0)+1&":B1000");2;0)

hindert dich ja keiner daran dies weiterzuentwickeln für den 4ten, 5ten Eintrag

Aber Index in Verbindung mit einem Hilfsblatt ist da einfacher zu pflegen:

Annahme, deine Datenquelle:

Tabelle:
F:\[SVerweisMehrfachInSpalteMitHilfsblatt.xls]!Tabelle2
 │ A │ B │ 
──┼─────┼────┤
1 │ a │ 10 │ 
2 │ b │ 11 │ 
3 │ a │ 12 │ 
4 │ b │ 13 │ 
5 │ g │ 14 │ 
6 │ a │ 15 │ 
7 │ a │ 16 │ 
──┴─────┴────┘

In einem Hilfsblatt Tabelle3 hast du:

Tabelle:
F:\[SVerweisMehrfachInSpalteMitHilfsblatt.xls]!Tabelle3
 │ A │ B │ C │ D │ 
──┼─────┼─────┼─────┼─────┤
1 │ a │ 1 │ 3 │ 6 │ 
2 │ b │ 2 │ 4 │ #NV │ 
3 │ g │ 5 │ #NV │ #NV │ 
──┴─────┴─────┴─────┴─────┘
Benutzte Formeln:
A1: =Tabelle1!A1
B1: =VERGLEICH(A1;Tabelle2!$A$1:blush:A$1000;0)
C1: =VERGLEICH($A1;INDIREKT("Tabelle2!$A"&B1+1&":blush:A$1000");0)+B1
D1: =VERGLEICH($A1;INDIREKT("Tabelle2!$A"&C1+1&":blush:A$1000");0)+C1
A2: =Tabelle1!A2
B2: =VERGLEICH(A2;Tabelle2!$A$1:blush:A$1000;0)
C2: =VERGLEICH($A2;INDIREKT("Tabelle2!$A"&B2+1&":blush:A$1000");0)+B2
D2: =VERGLEICH($A2;INDIREKT("Tabelle2!$A"&C2+1&":blush:A$1000");0)+C2
A3: =Tabelle1!A3
B3: =VERGLEICH(A3;Tabelle2!$A$1:blush:A$1000;0)
C3: =VERGLEICH($A3;INDIREKT("Tabelle2!$A"&B3+1&":blush:A$1000");0)+B3
D3: =VERGLEICH($A3;INDIREKT("Tabelle2!$A"&C3+1&":blush:A$1000");0)+C3

So sieht danndeine Auswertung in Tabelel1 so aus:

Tabelle:
F:\[SVerweisMehrfachInSpalteMitHilfsblatt.xls]!Tabelle3
 │ A │ B │ C │ D │ 
──┼─────┼─────┼─────┼─────┤
1 │ a │ 1 │ 3 │ 6 │ 
2 │ b │ 2 │ 4 │ #NV │ 
3 │ g │ 5 │ #NV │ #NV │ 
──┴─────┴─────┴─────┴─────┘
Benutzte Formeln:
A1: =Tabelle1!A1
B1: =VERGLEICH(A1;Tabelle2!$A$1:blush:A$1000;0)
C1: =VERGLEICH($A1;INDIREKT("Tabelle2!$A"&B1+1&":blush:A$1000");0)+B1
D1: =VERGLEICH($A1;INDIREKT("Tabelle2!$A"&C1+1&":blush:A$1000");0)+C1
A2: =Tabelle1!A2
B2: =VERGLEICH(A2;Tabelle2!$A$1:blush:A$1000;0)
C2: =VERGLEICH($A2;INDIREKT("Tabelle2!$A"&B2+1&":blush:A$1000");0)+B2
D2: =VERGLEICH($A2;INDIREKT("Tabelle2!$A"&C2+1&":blush:A$1000");0)+C2
A3: =Tabelle1!A3
B3: =VERGLEICH(A3;Tabelle2!$A$1:blush:A$1000;0)
C3: =VERGLEICH($A3;INDIREKT("Tabelle2!$A"&B3+1&":blush:A$1000");0)+B3
D3: =VERGLEICH($A3;INDIREKT("Tabelle2!$A"&C3+1&":blush:A$1000");0)+C3

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

Reinhard_fb8cb7 · 19. Januar 2008 um 19:36

VBA: Sverweis ersatz sucht auch nach Teilstrings
Hi Felix,

Ich habe Tabellenblatt 1 in dem unter anderem jeweils eine
spalte für Nachname und eine für Vorname gibt.
Und habe Tabellenblatt 2 bei dem Vorname und Nachname in einer
Spate stehen.
Gibt es einen Sverweis der mir nun Tabellenblatt 2 nach Name
durchsucht und mir dann beispielsweise die Telefonnummer
angibt.

Einbindung: Alt+F11, Einfügen–Modul, Code reinkopieren, editor schliessen

Benutzung: =SV(A4;Tabelle2!$A$1:blush:C$20;2;1)

Option Explicit
’
Function SV(ByVal Suchbegriff As Variant, ByRef DurchsuchBereich As Range, ByVal RueckgabenSpalte As Integer, Optional Suchmodus As Byte = 0) As Variant
’ Funktion sucht wie Sverweis, kann aber auch Teilstrings finden
’ Groß/Kleinschreibung spielt keine Rolle
’ Suchmodus 0 oder nicht angegeben, Suchbegriff muß mit durchsuchten Zellen ganz übereinstimmen
’ Suchmodus 1 Suchbegriff kann Teilstring an beliebiger Stelle in den durchsuchten Zellen sein
On Error GoTo Fehler
If RueckgabenSpalte > DurchsuchBereich.Columns.Count Then SV = „Spalte zu groß!“
If RueckgabenSpalte 0 And Suchmodus 1 Then SV = „Suchmodus nicht 0 oder 1“
If SV „“ Then Exit Function
If Suchmodus = 1 Then Suchbegriff = „*“ & Suchbegriff & „*“
SV = „Nicht Gefunden!“
SV = Application.WorksheetFunction.Match(Suchbegriff, DurchsuchBereich.Columns(1), 0)
If IsNumeric(SV) Then SV = DurchsuchBereich.Cells(SV, RueckgabenSpalte)
Exit Function
Fehler:
End Function

Gruß
Reinhard

Tobias_M_bc4893 · 22. Januar 2008 um 08:57

Hallo,

vielen Dank für deine Mühen. Ich habe mal versucht, deine Formal auf mein Problm umzuschreiben, leider bekomme ich folgende Fehlermeldung:
#Name?

Kannst du auf den ersten BLick erkenne wo der „Teufel“ steckt?

=(SVERWEIS(A175&" „&B175;INDIREKT(„HLV-Halle1!D“&VERGLEICH(A175&“ „&B175;HLV-Halle1!D1:smiley:;0)
+VERGLEICH(A175&“ „&B175;INDIREKT(„HLV-Halle1!D“&VERGLEICH(A175&“ „&B175;HLV-Halle1!D:smiley:;0)
+1&“1000");0)+1&":E2000");2;0))

Danke
Felix

Hi Felix,

für die ersten 3 Egebnisse ginge das so:
=SVERWEIS($A2;Tabelle2!$A:blush:B;2;0)

=SVERWEIS(A2;INDIREKT(„Tabelle2!A“&VERGLEICH(A2;Tabelle2!A:A;0)
+1&":B1000");2;0)

=SVERWEIS(A2;INDIREKT(„Tabelle2!A“&VERGLEICH(A2;Tabelle2!A:A;0)
+VERGLEICH(A2;INDIREKT(„Tabelle2!A“&VERGLEICH(A2;Tabelle2!’:A;0)
+1&":A1000");0)+1&":B1000");2;0)

hindert dich ja keiner daran dies weiterzuentwickeln für den
4ten, 5ten Eintrag

Aber Index in Verbindung mit einem Hilfsblatt ist da einfacher
zu pflegen:

Annahme, deine Datenquelle:

Tabelle:
F:[SVerweisMehrfachInSpalteMitHilfsblatt.xls]!Tabelle2
│ A │ B │
──┼─────┼────┤
1 │ a │ 10 │
2 │ b │ 11 │
3 │ a │ 12 │
4 │ b │ 13 │
5 │ g │ 14 │
6 │ a │ 15 │
7 │ a │ 16 │
──┴─────┴────┘

In einem Hilfsblatt Tabelle3 hast du:

Tabelle:
F:[SVerweisMehrfachInSpalteMitHilfsblatt.xls]!Tabelle3
│ A │ B │ C │ D │
──┼─────┼─────┼─────┼─────┤
1 │ a │ 1 │ 3 │ 6 │
2 │ b │ 2 │ 4 │ #NV │
3 │ g │ 5 │ #NV │ #NV │
──┴─────┴─────┴─────┴─────┘
Benutzte Formeln:
A1: =Tabelle1!A1
B1: =VERGLEICH(A1;Tabelle2!$A$1:blush:A$1000;0)
C1:
=VERGLEICH($A1;INDIREKT(„Tabelle2!$A“&B1+1&"A$1000");0)+B1
D1:
=VERGLEICH($A1;INDIREKT(„Tabelle2!$A“&C1+1&"A$1000");0)+C1
A2: =Tabelle1!A2
B2: =VERGLEICH(A2;Tabelle2!$A$1:blush:A$1000;0)
C2:
=VERGLEICH($A2;INDIREKT(„Tabelle2!$A“&B2+1&"A$1000");0)+B2
D2:
=VERGLEICH($A2;INDIREKT(„Tabelle2!$A“&C2+1&"A$1000");0)+C2
A3: =Tabelle1!A3
B3: =VERGLEICH(A3;Tabelle2!$A$1:blush:A$1000;0)
C3:
=VERGLEICH($A3;INDIREKT(„Tabelle2!$A“&B3+1&"A$1000");0)+B3
D3:
=VERGLEICH($A3;INDIREKT(„Tabelle2!$A“&C3+1&"A$1000");0)+C3

So sieht danndeine Auswertung in Tabelel1 so aus:

Tabelle:
F:[SVerweisMehrfachInSpalteMitHilfsblatt.xls]!Tabelle3
│ A │ B │ C │ D │
──┼─────┼─────┼─────┼─────┤
1 │ a │ 1 │ 3 │ 6 │
2 │ b │ 2 │ 4 │ #NV │
3 │ g │ 5 │ #NV │ #NV │
──┴─────┴─────┴─────┴─────┘
Benutzte Formeln:
A1: =Tabelle1!A1
B1: =VERGLEICH(A1;Tabelle2!$A$1:blush:A$1000;0)
C1:
=VERGLEICH($A1;INDIREKT(„Tabelle2!$A“&B1+1&"A$1000");0)+B1
D1:
=VERGLEICH($A1;INDIREKT(„Tabelle2!$A“&C1+1&"A$1000");0)+C1
A2: =Tabelle1!A2
B2: =VERGLEICH(A2;Tabelle2!$A$1:blush:A$1000;0)
C2:
=VERGLEICH($A2;INDIREKT(„Tabelle2!$A“&B2+1&"A$1000");0)+B2
D2:
=VERGLEICH($A2;INDIREKT(„Tabelle2!$A“&C2+1&"A$1000");0)+C2
A3: =Tabelle1!A3
B3: =VERGLEICH(A3;Tabelle2!$A$1:blush:A$1000;0)
C3:
=VERGLEICH($A3;INDIREKT(„Tabelle2!$A“&B3+1&"A$1000");0)+B3
D3:
=VERGLEICH($A3;INDIREKT(„Tabelle2!$A“&C3+1&"A$1000");0)+C3

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

Reinhard_fb8cb7 · 26. Januar 2008 um 20:38

Hallo Felix,
du brauchst ein Hilfsblatt, Tabelle2.
Tabelle1 sieht dann im Ausschnitt so aus:

Tabelle:
F:\[Januar-f3bfc62c7b.xls]!Tabelle1
 │ F │ G │ 
──┼────────────────┼──────────────┤
3 │ 60m;200m;Weit │ 6,91;22;6,55 │ 
──┴────────────────┴──────────────┘
Benutzte Formeln:
F3: =INDIREKT("HLVHalle1!"&ADRESSE(Tabelle2!B1;4))&";"&INDIREKT("HLVHalle1!"&ADRESSE(Tabelle2!C1;4))&";"&INDIREKT("HLVHalle1!"&ADRESSE(Tabelle2!D1;4))
G3: =INDIREKT("HLVHalle1!"&ADRESSE(Tabelle2!B1;5))&";"&INDIREKT("HLVHalle1!"&ADRESSE(Tabelle2!C1;5))&";"&INDIREKT("HLVHalle1!"&ADRESSE(Tabelle2!D1;5))

Das Hilfsblatt Tabelle2 so:

Tabelle:
F:\[Januar-f3bfc62c7b.xls]!Tabelle2

 │ A │ B │ C │ D │ E │ 
──┼─────────────┼─────┼─────┼─────┼─────┤
1 │ Weber Felix │ 1 │ 4 │ 5 │ #NV │ 
──┴─────────────┴─────┴─────┴─────┴─────┘

Benutzte Formeln:
A1: =Tabelle1!A3&" "&Tabelle1!B3
B1: =VERGLEICH(A1;HLVHalle1!$A$1:blush:A$1000;0)
C1: =VERGLEICH($A1;INDIREKT("HLVHalle1!$A"&B1+1&":blush:A$1000");0)+B1
D1: =VERGLEICH($A1;INDIREKT("HLVHalle1!$A"&C1+1&":blush:A$1000");0)+C1
E1: =VERGLEICH($A1;INDIREKT("HLVHalle1!$A"&D1+1&":blush:A$1000");0)+D1

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard