Microsoft Excel

Herbers Excel/VBA-Archiv

Sverweis Suchkriterium Teilstring in Zelle

Betrifft: Sverweis Suchkriterium Teilstring in Zelle von: Dennis
Geschrieben am: 05.11.2014 08:59:27

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.

  

Betrifft: AW: Sverweis Suchkriterium Teilstring in Zelle von: Dennis
Geschrieben am: 05.11.2014 09:01:05

Nun habe ich vergessen den Link hinzuzufügen:
https://www.herber.de/bbs/user/93556.xlsx


  

Betrifft: AW: Sverweis Suchkriterium Teilstring in Zelle von: SF
Geschrieben am: 05.11.2014 09:02:15

Hola,

wenn ich dich richtig verstanden habe, ginge das so:

=sverweis("*"&A1&"*";A2:B7;2;0)
Gruß,
steve1da


  

Betrifft: AW: Sverweis Suchkriterium Teilstring in Zelle von: Dennis
Geschrieben am: 05.11.2014 09:11:22

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


  

Betrifft: AW: Sverweis Suchkriterium Teilstring in Zelle von: Dennis
Geschrieben am: 05.11.2014 09:14:16

Die Formel soll gucken, ob in C2 Dennis oder Dietmal oder Carsten oder . . . enthalten ist und dann das dazugehörige Alter ausspucken.


  

Betrifft: AW: Sverweis Suchkriterium Teilstring in Zelle von: SF
Geschrieben am: 05.11.2014 09:29:50

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


  

Betrifft: AW: Sverweis Suchkriterium Teilstring in Zelle von: Dennis
Geschrieben am: 05.11.2014 09:59:03

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


  

Betrifft: AW: Sverweis Suchkriterium Teilstring in Zelle von: Hajo_Zi
Geschrieben am: 05.11.2014 09:32:52

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



GrußformelHomepage


  

Betrifft: prima - genau so von: WF
Geschrieben am: 05.11.2014 09:56:04

nimm nur, falls doch mal 2 Namen auftauen, nicht SUMME sondern MAX

Salut WF


  

Betrifft: AW: prima - genau so von: Dennis
Geschrieben am: 05.11.2014 10:08:43

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


  

Betrifft: AW: prima - genau so von: Hajo_Zi
Geschrieben am: 05.11.2014 10:14:15

Hallo Dennis,

Du solltest meinen Beitrag komplett lesen, auch den Hinweis zur Formel.

Gruß Hajo


  

Betrifft: AW: prima - genau so von: Dennis
Geschrieben am: 05.11.2014 10:23:30

Hallo Hajo,

du hast recht, wer lesen kann ich klar im Vorteil.
Vielen Dank, es funktioniert genau so wie ich es wollte.

Gruß
Dennis


  

Betrifft: AW: prima - genau so von: Dennis
Geschrieben am: 05.11.2014 10:41:19

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


  

Betrifft: mit INDEX von: WF
Geschrieben am: 05.11.2014 10:56:02

Hi,

in D2:
{=INDEX(B:B;MAX(ISTZAHL(SUCHEN($A$2:$A$7;C2))*(ZEILE($2:$7))))}

Salut WF


  

Betrifft: AW: mit INDEX von: Dennis
Geschrieben am: 05.11.2014 11:30:32

Hallo,

hat geklappt vielen Dank!

Gruß
Dennis


 

Beiträge aus den Excel-Beispielen zum Thema "Sverweis Suchkriterium Teilstring in Zelle"