Office: 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?

 

=Sverweis(Links(A3;7);C:D;2;0)

Gruß Hajo

 

Was bedeutet A3;7?
Die Namen in der Zellen sind unterschiedlich lang.

 

einschl. Links bedeutet es 7 Zeichen von links aus Zelle A3
Waas A3;7 bedeutet kann ich nicht schreiben.

 

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

 

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

 

Hallo!

Diese Möglichkeit wäre einen Versuch wert:

	A	B	C	D	E	F
1	abc 123	999			Halt 45 gros	480
2	Halt 45	480			abc 123 xyz	999
3

Zelle	Formel
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)

 

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

 

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!