Live-Forum - Die aktuellen Beiträge
Datum
Titel
07.10.2025 18:27:08
Formel anstelle XVWERWEIS für alte Excel-Version
07.10.2025 15:42:10
Identische-Tabellen-identische Sub's funktionieren nicht...
07.10.2025 09:25:19
Bedingte Formatierung mit bezug auf anderes TabellenBlatt
07.10.2025 08:24:37
Formel mit dynamischer Name aus Zelle
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1388to1392
1388to1392
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Sverweis Suchkriterium Teilstring in Zelle

Forumthread: Sverweis Suchkriterium Teilstring in Zelle

Sverweis Suchkriterium Teilstring in Zelle
05.11.2014 08:59:27
Dennis
Hallo zusammen,
das ist mein erster Beitrag und ich hoffe ich kann das verständlich rüberbringen. Ich Suche eine Formel mit der ich innherhalb einer Zelle C2 eine Zeichenfolge (name) suche um dann mit dem Sverweis in einer Matrix diese Zeichenfolge (Namen) finde und dann das Alter (2. Spalte der Matrix) anzeige. Soweit meine ich könnte ich das umsetzen mit =sverweis("name";A2:B7;2). Nun ist es aber so, dass ich das Suchkriterium nicht manuell eingeben möchte (das Suchkriterium ist quasie einmal Bestandteil in im Namenstring und einmal in der Spalte A), weil der Namensstring sehr lang ist. Habt ihr eine Idee? Anbei auch das Beispiel.

Anzeige

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sverweis Suchkriterium Teilstring in Zelle
05.11.2014 09:02:15
SF
Hola,
wenn ich dich richtig verstanden habe, ginge das so:
=sverweis("*"&A1&"*";A2:B7;2;0)
Gruß,
steve1da

Anzeige
AW: Sverweis Suchkriterium Teilstring in Zelle
05.11.2014 09:11:22
Dennis
Hallo,
danke für deine Atw., leider ist das nicht das Richtige. Als Ergbnis müsste 10 in D2 stehen, für Emil=10Jahre (aus der Matrix) und weil er im Namensstring in C2 enthalten ist. Ich habe deine Formel angepasst
=sverweis("*"&C2&"*";A2:B7;2;0)
aber das klappt leider nicht.
LG
Dennis

Anzeige
AW: Sverweis Suchkriterium Teilstring in Zelle
05.11.2014 09:14:16
Dennis
Die Formel soll gucken, ob in C2 Dennis oder Dietmal oder Carsten oder . . . enthalten ist und dann das dazugehörige Alter ausspucken.

AW: Sverweis Suchkriterium Teilstring in Zelle
05.11.2014 09:29:50
SF
Hola,
Emil ist aber auch in C6 enthalten, was soll denn dann passieren?
Beschreib doch mal bitte, nach was genau gesucht werden soll, und wo genau gesucht werden soll.
Gruß,
steve1da

Anzeige
AW: Sverweis Suchkriterium Teilstring in Zelle
05.11.2014 09:59:03
Dennis
Also die Formel in D2 soll gucken, ob irgendein Name in C2 enthalten ist, der auch in A2:A7 enthalten ist und dann das Alter anzeigen. In A2:A7 sind keine Namen doppelt, innherhalb der Zellen in Spalte C stehen auch keine doppelten Namen, nur in der Spalte können Sie doppelt auftauchen (Emil steht in C2 und C6 aber z.B. niemals 2mal in C2. Folgende Ergebnisse müssten dann angezeigt werden:
D2=10 (für Emil)
D3=55 (für Frank)
D4=40 (für Dietmar)
D5=41 (für Carsten)
D6=10 (wieder für Emil)
D7=35 (für Dennis)
Ich hoffe habe es gut rübergebracht. Vielen Dank vorab.
Gruß
Dennis

Anzeige
AW: Sverweis Suchkriterium Teilstring in Zelle
05.11.2014 09:32:52
Hajo_Zi
ich vermute mal es kommt nur ein Name vor?

Tabelle1
 DEF
1Formelergebnis  
21010Emil
3 55Frank
4 40Dietmar
5 41Carsten
6 10Emil
7 35Dennis

verwendete Formeln
Zelle Formel Bereich N/A
D2=SVERWEIS("Emil";$A$2:$B$7;2;0)  
E2:E7{=SUMME(ISTZAHL(SUCHEN($A$2:$A$7;C2))*$B$2:$B$7)}$E$2 
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 18.20 einschl. 64 Bit



Anzeige
prima - genau so
05.11.2014 09:56:04
WF
nimm nur, falls doch mal 2 Namen auftauen, nicht SUMME sondern MAX
Salut WF

AW: prima - genau so
05.11.2014 10:08:43
Dennis
Hallo Hajo,
wenn ich diese Formel =SUMME(ISTZAHL(SUCHEN($A$2:$A$7;C2))*$B$2:$B$7) in C2 einfüge und runterkopiere bekomme ich folgende Ergebnise:
Formelergebnis
0
0
0
0
10
0
Eigentlich sollte es aber so aussehen:
Formelergebnis
10
55
40
41
10
35
Mach ich was falsch?
Gruß
Dennis

Anzeige
AW: prima - genau so
05.11.2014 10:14:15
Hajo_Zi
Hallo Dennis,
Du solltest meinen Beitrag komplett lesen, auch den Hinweis zur Formel.
Gruß Hajo

AW: prima - genau so
05.11.2014 10:23:30
Dennis
Hallo Hajo,
du hast recht, wer lesen kann ich klar im Vorteil.
Vielen Dank, es funktioniert genau so wie ich es wollte.
Gruß
Dennis

Anzeige
AW: prima - genau so
05.11.2014 10:41:19
Dennis
Hallo,
eine Frage hätte ich noch und es tut mir leid aber das habe ich nicht bedacht.
Wie würde die Formel aussehen, wenn statt des Alters dort ein Text steht?
Ich hoffe ich raube euch nicht den letzten Nerv.
Gruß
Dennis

mit INDEX
05.11.2014 10:56:02
WF
Hi,
in D2:
{=INDEX(B:B;MAX(ISTZAHL(SUCHEN($A$2:$A$7;C2))*(ZEILE($2:$7))))}
Salut WF

Anzeige
AW: mit INDEX
05.11.2014 11:30:32
Dennis
Hallo,
hat geklappt vielen Dank!
Gruß
Dennis
;

Forumthreads zu verwandten Themen

Spalten nach Text durchsuchen
Kall

Hallo zusammen,
ich brächte mal eure Hilfe, in der such habe ich nichts passendes gefunden. Ich nöchte die Spalte A ab Reihe 16 bis zum Ende nach einem Begriff "UA" durchsuchen (den es öfter geben kann), und wenn ich diese gefunden habe die komplette Zeile in die Tabelle 5 kopieren, über VB...
19.01.2009 12:47:13
Thread-ID: 1041382
4 Beiträge
Zugriff auf auf Teilinhalte einer Zelle
Der

Hallo da Draußen!
Habe mal wieder ein Problem mit meiner VBA Programmierung. Ich möchte innerhalb einer Exceltabelle auf einzelne Zellen zugreifen, die ein bestimmtes Merkmal haben. Der Inhalt der Zelle beginnt mit einer Zahl (in diesem fall mit einer Postleitzahl). Ich möchte also meine Ab...
20.03.2009 14:52:09
Thread-ID: 1062230
5 Beiträge
Teil einer Formel in Zelle einlesen
Bernd

Hallo Freaks,
ich bastle nun schon geraume Zeit an einem Code, der mir in eine Zelle den Teil einer Formel (Kostenstellennamen) aus einer anderen Zelle schreibt.
Der nachstehene Code (von einem ausgeschiedenen Kollegen) ersetzt einen KST-Namen in einem Link mit einem neuen KST-Namen aus...
13.04.2009 12:55:24
Thread-ID: 1068432
6 Beiträge
Summenprodukt mit Formel als Suchkriterium
Andreas

Hallo liebe Excel-Magier,
meine Summenprodukt-Formel
=SUMMENPRODUKT((Datenquelle!A1:A100=Teil(A1;1;4))*(Datenquelle!B1:B100))
liefert leider das falsche Ergebnis 0.
Ich vermute, es liegt an "=Teil(A1;1;4)". Geht das so überhaupt, oder was mache ich falsch?
Wäre super, wenn m...
02.06.2009 16:08:19
Thread-ID: 1078656
9 Beiträge
Ein Teil einer Formel in andere Zelle ausgelagern
Hans

Ein Teil der Formel – der immer gleich ist - soll in eine andere Zelle ausgelagert werden. Ist die möglich, gibt es hierfür eine spezielle Funktion?
=WENN(E25=5;SVERWEIS(Rechn.!C25;'C:\Ornder\[dateiname.xls]tabelle'!$A$2:$B$17128;2;FALSCH);"")
=WENN(E25=5;SVERWEIS(Rechn.!C25; Verweis au...
14.06.2009 21:29:10
Thread-ID: 1081465
2 Beiträge
Teilbereich aus einer Zelle
MikeM

Hallo zusammen,
eigentlich trivial aber ich bekomme es nicht hin.
In Zelle A1 steht: Datum: 01.01.2009
In A2 möchte ich stehen haben: 01.01.2009
Allerdings kann in A1 auch: 1.1.09 stehen
Die Formel: rechts(A1;10) würde funktionieren, wenn das Datum nicht auch im Format 1.1....
21.07.2009 08:16:34
Thread-ID: 1090112
4 Beiträge
Anzeige
Verteilung des Inhalts einer Zelle auf zwei Zellen
Jeanette

Hallo, gegeben eine Tabelle mit einer Spalte, die Informationen in folgender Form enthält:
1. Ein Wort oder mehrere Wörter, die durch Blank getrennt sind.
2. Unmittelbar auf das letzte dieser Wörter folgt ein Komma, gefolgt von einem Blank.
3. Danach öffnet sich eine Klammer „(„.
20.08.2009 16:33:59
Thread-ID: 1097466
5 Beiträge
Sverweis + Indexvergleich in einer zelle?
urs

Hallo zusammen
Kann ich in einer Zelle Indexvergleiche und einen Sverweis kombinieren oder beisst sich das?
Bekomme nämlich nicht die richtigen Werte.
Danke für Hilfe
Gruss urs
04.09.2009 09:07:40
Thread-ID: 1100537
8 Beiträge
sverweis, suchkriterium in mitte der matrix
Sascha


einen schönen guten morgen,
hoffe ich kann mein problem ordentlich schildern :)
gibt es eine formel (wie sverweis) wo aber das suchkriterium in der mitte der matrix stehen kann.
also z.B. Suchkriterium steht in spalte 4, und dazugehörige ausgabe steht in spalte 2.
vielen d...
15.09.2009 09:21:13
Thread-ID: 1102834
5 Beiträge
Teilen von Zahlen in einer Zelle
Zahlen

Hallo
Ich habe in der Spalte E ca. 16'000 Zeilen mit Zahlenwerten die untereinander mit einem Komma getrennt sind
Beispiel einer solcher Zelle
350,166,12,45
Mein Wunsch ist es nun all diese Zahlen je Zelle einzeln aufzuteilen. Dies als VBA-Variante (?).
Also so, am Beispie...
10.11.2009 09:00:10
Thread-ID: 1115710
5 Beiträge
Hauptteil einer Zelle übernehmen
Harald

Hallo zusammen,
leider konnte ich die Überschrift nicht richtig deklarieren. Im Prinzip kann ich es auch nur mit einem Beispiel erklären.
In Spalte A und einigen viele Zeilen ist folgender Inhalt:
A1= "A1" B1= "1"
A2= "A2" B1= "2"
A3= "N1" B1= "3"
A1= "A4" B1...
02.12.2009 15:31:13
Thread-ID: 1121433
3 Beiträge
SVerweis mit Teilstrings?
Martin

Also hier kommt mein Problem (siehe Datei https://www.herber.de/bbs/user/66371.xls):
Spalte A ist gegeben. Spalte B soll erzeugt werden. Und zwar mit SVerweis - oder ähnlichem - auf Bereich E1:F7. Denn der Eintrag in Spalte B richtet sich nach einem gefundenen Teilstring in der Zelle in Sp...
04.12.2009 15:14:12
Thread-ID: 1121837
5 Beiträge
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Sverweis mit Teilstring in Excel nutzen

Schritt-für-Schritt-Anleitung

Um den SVERWEIS in Excel zu verwenden, um nach einem Teilstring in einer Zelle zu suchen, kannst du folgende Schritte befolgen:

  1. Vorbereitung der Daten: Stelle sicher, dass du eine Matrix hast, in der die ersten Spalten die Suchkriterien enthalten (z.B. Namen) und die zweite Spalte die zugehörigen Werte (z.B. Alter).

  2. Formel eingeben: Verwende die folgende Formel in der Zelle, in der du das Ergebnis anzeigen möchtest (angenommen D2):

    =SVERWEIS("*"&C2&"*";A2:B7;2;0)

    Hierbei wird in C2 nach einem Teilstring gesucht, der in den Zellen von A2 bis A7 enthalten ist.

  3. Matrixformel anwenden: Falls du mehrere Namen in einer Zelle hast und die zugehörigen Werte summieren möchtest, benutze:

    {=SUMME(ISTZAHL(SUCHEN($A$2:$A$7;C2))*$B$2:$B$7)}

    (Drücke Strg + Umschalt + Enter, um die Matrixformel zu aktivieren).

Häufige Fehler und Lösungen

  • Fehler: Ergebnis ist 0: Dies kann vorkommen, wenn das Suchkriterium nicht korrekt formuliert ist oder die Zellen nicht richtig referenziert werden. Stelle sicher, dass die Zellen in deiner Formel korrekt angegeben sind.

  • Fehler: Falsches Ergebnis: Überprüfe, ob sich die Namen in den Zellen in der Matrix (A2:A7) genau so befinden, wie du sie in C2 angegeben hast. Auch Groß- und Kleinschreibung können eine Rolle spielen.

Alternative Methoden

  • INDEX und MAX: Eine alternative Methode zur Verwendung des SVERWEIS ist die Kombination von INDEX und MAX. Diese Methode kann verwendet werden, wenn du den höchsten Wert suchst: 
    {=INDEX(B:B;MAX(ISTZAHL(SUCHEN($A$2:$A$7;C2))*(ZEILE($2:$7))))}

    Diese Formel gibt den Wert aus der Spalte B zurück, der dem ersten gefundenen Namen in C2 entspricht.

Praktische Beispiele

  • Angenommen, in C2 steht „Dennis und Frank“. Du kannst die Formel in D2 eingeben:

    =SUMME(ISTZAHL(SUCHEN($A$2:$A$7;C2))*$B$2:$B$7)

    Diese Formel summiert das Alter von Dennis und Frank, falls beide in der Matrix vorhanden sind.

  • Wenn du nur den ersten gefundenen Namen und dessen Alter zurückgeben möchtest, nutze die SVERWEIS-Formel.

Tipps für Profis

  • Verwendung von Wildcards: Mit * kannst du sicherstellen, dass du nach Teilstrings suchst. Dies ist besonders nützlich, wenn du nur einen Teil des Namens kennst.

  • Fehlerbehandlung: Verwende die IFERROR-Funktion, um Fehlermeldungen zu vermeiden:

    =IFERROR(SVERWEIS("*"&C2&"*";A2:B7;2;0);"Nicht gefunden")

FAQ: Häufige Fragen

1. Wie kann ich SVERWEIS verwenden, um nur den Teil einer Zelle zu suchen?
Verwende die Wildcard-Notation mit *, um nach Teilstrings zu suchen, wie in der Schritt-für-Schritt-Anleitung beschrieben.

2. Was mache ich, wenn ich mehrere Namen in einer Zelle habe?
Du kannst die Formel anpassen, um alle Namen zu durchsuchen. Eine Kombination aus SUMME und ISTZAHL(SUCHEN(...)) ist hier hilfreich.

3. Funktioniert dies in allen Excel-Versionen?
Ja, die beschriebenen Funktionen sind in den meisten modernen Excel-Versionen verfügbar. Achte darauf, dass du die Matrixformeln korrekt eingibst (mit Strg + Umschalt + Enter).

4. Kann ich die Formel so anpassen, dass sie Text anstelle von Zahlen zurückgibt?
Ja, anstelle von Alterswerten kannst du einfach die entsprechende Spalte in deiner Formel anpassen.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige