Office: sverweis

Helfe beim Thema sverweis in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Ich habe zwei Listen. In der einen Liste stehen in 2 Spalten in jeder Zelle je ein Name und eine Nummer. In der zweiten Liste steht in einer Spalte der... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von tomqu, 15. April 2011.

  1. tomqu Neuer User

    sverweis


    Ich habe zwei Listen. In der einen Liste stehen in 2 Spalten in jeder Zelle je ein Name und eine Nummer. In der zweiten Liste steht in einer Spalte der Name und ein Zusatz drin. Wie kann ich die Zuordnung der Nummern in die zweite Liste bekommen? Ich kann nur einen Teil der Zelle für den Verweis verwenden.
    Z.B.: 1.Liste 1.Spalte 1.Zelle: "Halt 45" 2.Spalte 1.Zelle: "480"
    2. Liste 1.Spalte 1.Zelle: "Halt 45 gros"
    Ich brauche in der zweiten Liste in der 2. Spalte in der 1. Zelle die Nummer "480".
    Wie muss ich das machen?
     
  2. Hajo_Zi
    Hajo_Zi Erfahrener User
    =Sverweis(Links(A3;7);C:D;2;0)

    Gruß Hajo
     
    Hajo_Zi, 15. April 2011
    #2
  3. tomqu Neuer User
    Was bedeutet A3;7?
    Die Namen in der Zellen sind unterschiedlich lang.
     
  4. Hajo_Zi
    Hajo_Zi Erfahrener User

    sverweis

    einschl. Links bedeutet es 7 Zeichen von links aus Zelle A3
    Waas A3;7 bedeutet kann ich nicht schreiben.
     
    Hajo_Zi, 15. April 2011
    #4
  5. tomqu Neuer User
    Lieber Hajo,
    sorry, bin Neuling und nicht mehr der Jüngste. Bitte um etwas Geduld. Was mach ich, wenn die Namen in den Zellen unterschiedliche Längen haben? Da kann ich die genaue Zeichenanzahl nicht angeben.
    Außerdem kommt bei mir als Antwort immer #NV.
    Gruß Tom
     
  6. Hajo_Zi
    Hajo_Zi Erfahrener User
    Hallo Tom

    ich sehe Deine Datei nicht, darum kein Hinweis zu #NV. Das ist eigentlich ein Zeichen für nicht gefunden.
    mit unterschiedlicher Wortlänge wird es kompliziert. Ich bin nicht der Formelmensch, ich baue es auch nicht nach um es zu testen.

    Gruß Hajo
     
    Hajo_Zi, 15. April 2011
    #6
  7. schatzi Super-Moderator
    Hallo!

    Diese Möglichkeit wäre einen Versuch wert:

     ABCDEF
    1abc 123999  Halt 45 gros480
    2Halt 45480  abc 123 xyz999
    3      
    ZelleFormel
    F1=VERWEIS(9^9;FINDEN(A$1:A$999;E1)/(A$1:A$999<>"");B$1:B$999)
    F2=VERWEIS(9^9;FINDEN(A$1:A$999;E2)/(A$1:A$999<>"");B$1:B$999)
     
    Zuletzt von einem Moderator bearbeitet: 30. November 2020
    schatzi, 15. April 2011
    #7
  8. tomqu Neuer User

    sverweis

    Hallo,
    vielen Dank für die Hilfe. Genauso hat es funktioniert.
    Ich weiß nur nicht warum. Könntest Du mir eine Erklärung zu dieser Formel geben?
    Tom
     
  9. schatzi Super-Moderator
    Hallo!

    Diese Formel ist etwas tricky, daher auch nicht ganz einfach zu erklären...
    Ich versuch's dennoch:

    (Diese Erklärung setzt allerdings voraus, dass du die grundlegende Funktionsweise der verwendeten Funktionen kennst!)

    FINDEN(A1:A999;E1)
    nimmt sich den Inhalt von E1 als Vorgabe und durchsucht, ob {A1, A2, A3, ... , A998, A999} in E1 enthalten sind.
    Falls ja, dann gibt FINDEN die Position der Fundstelle zurück.
    Falls nein, dann gibt FINDEN den Fehlerwert #WERT! zurück.
    Das könnte also z.B. ein Array ergeben wie
    {#WERT!;#WERT!;1;#WERT!;#WERT!;...}

    (A1:A999<>"")
    checkt, ob {A1, A2, A3, ... , A998, A999} leer sind.
    Falls ja, dann wird FALSCH (bzw. 0) zurückgegeben.
    Falls nein, dann wird WAHR (bzw. 1) zurückgegeben.

    Der rote Teil wird nun durch den blauen Teil dividiert.
    Da man aber durch Null nicht teilen darf, wird bei leeren Zellen also der Fehlerwert #DIV/0! zurückgegeben.

    Das Ergebnis bisher sollte also ein Array sein, welches bei x Treffern genau x Zahlenwerte enthält, und sonst nur Fehlerwerte (#WERT! oder #DIV/0!) ausweist.
    (Im Idealfall sollte es hier genau ein Treffer sein! Sonst nur Fehlerwerte!)

    Jetzt kommt die VERWEIS-Funktion ins Spiel:
    Ich suche nach 9^9, also einer utopisch großen Zahl in dem eben erklärten Array.
    Diese Zahl wird zwar nie gefunden werden, aber Excel erklärt sich hier (Gott sei Dank!) auch mit kleineren Zahlen als Fundstelle schon einverstanden. (Alle Fehlerwerte werden hier vom VERWEIS ignoriert! That's the trick!)

    Da 9^9 hier nicht gefunden wurde, nimmt Excel toleranterweise den letzten gefundenen Zahlwert im Array als Referenz und gibt den dazugehörigen Wert aus Spalte B aus.

    Puuuh!
    Ich denke, deine Verwirrung ist nun komplett!
     
    schatzi, 15. April 2011
    #9
Thema:

sverweis

Die Seite wird geladen...
  1. sverweis - Similar Threads - sverweis

  2. SVERWEIS

    in Microsoft Excel Hilfe
    SVERWEIS: Hej! bin ein absoluter Excel Anfänger und vermutlich wird hier nun über meine Frage geschmunzelt. Sicher findet sich trotzdem ein schlauer Kopf der mir helfen kann. Ich möchte die fehlende PNR...
  3. Bedingte Formatierung funktioniert nicht

    in Microsoft Excel Hilfe
    Bedingte Formatierung funktioniert nicht: Hallo, ich habe eine Excel Datei erstellt und es funktioniert alles, bis auf eine bedingte Formatierung. Ich verstehe einfach nicht wieso. Habe in der Liste schon andere Formatierungen, die sehr...
  4. Access mit Sverweis,???

    in Microsoft Access Hilfe
    Access mit Sverweis,???: =WENN(ISTNV(SVERWEIS(Eingang[@Vorgang];NrDrei2;2;0));"";SVERWEIS(Eingang[@Vorgang];NrDrei2;2;0)) Gesetz dem Falle mich versteht gerade jemand mit dem o. A. Anliegen!!! Ich möchte von Excel mal...
  5. Tabellenformatierung: Effekt auf Verweise

    in Microsoft Excel Hilfe
    Tabellenformatierung: Effekt auf Verweise: Hallo zusammen, ich habe eine Excel, in der sehr viele Produktdaten in einer Listenform gesammelt sind. Viele weitere Dateien sind über S- und X-Verweise mit dieser Produktliste verknüpft. Gerne...
  6. Sverweis mit zwei Intervallen

    in Microsoft Excel Hilfe
    Sverweis mit zwei Intervallen: Hello again, mein Vorsatz für nächstes Jahr ist auf jeden Fall eine Excel-Fortbildung um euch hier nicht immer wieder zu belästigen :oops::rolleyes: Ich steh (mal wieder) etwas auf dem Schlauch....
  7. Werte in 2 Tabellen vergleichen und Zählen wenn Suchbegriff zutrifft

    in Microsoft Excel Hilfe
    Werte in 2 Tabellen vergleichen und Zählen wenn Suchbegriff zutrifft: Hallo, vielleicht kann mir jemand helfen: Ich habe zwei Tabellenblätter und in beiden steht in Spalte A der Nachnahme Es sollen nun alle Namen in den beiden Tabellenblätter miteinander...
  8. sverweis und Wochenenden in Matrix darstellen

    in Microsoft Excel Hilfe
    sverweis und Wochenenden in Matrix darstellen: Hallo, ich habe zwei Probleme, welche ich aktuell nicht gelöst bekomme. Nr. 1 Ich habe eine Jahresübersicht in Form einer Matrix, wo ich nur die WE farblich dargestellt bekommen möchte. Über...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden