Sverweis?

Computer: Software & Programmierung Office Software
#1

Hallo,

ich habe eine Tabelle in der von Spalte A1 bis H383 unterschiedliche Werte stehen.

Entweder steht da:
14 PK450 (14 P steht immer davor, danach folgt entweder direkt eine 4 stellige Nummer oder K und eine 3 stellige Nummer)
oder
es steht eine sechsstellige Nummer z.B. 14 4128 (die 14 und das Leerzeichen sind immer gleich).

In jeder Zeile steht jeweils nur ein Wert.

z.B.:

A1: 14 PK450
B2: 14 PK406
B3: 14 P4983
C4: 14 4128
B5: 14 4159

Jetzt möchte ich in einem zweiten Reiter einen SVERWEIS einfügen, der nach diesen Werten sucht und sie im Bereich A1 bis B383 wiedergibt.
Und zwar sollen in der Spalte A alle Werte mit PK oder P auftauchen und in der Spalte B alle 6 stelligen Zahlenfolgen.

Ich hoffe ihr habt verstanden, was ich will und könnt mir eine Formel nennen, mit der ich mein Problem lösen kann!

Vielen Dank!

#2

Liste auftrennen mit Hilfsspalten

14 PK450 (14 P steht immer davor, danach folgt entweder direkt
eine 4 stellige Nummer oder K und eine 3 stellige Nummer)
oder
es steht eine sechsstellige Nummer z.B. 14 4128 (die 14 und
das Leerzeichen sind immer gleich).

In jeder Zeile steht jeweils nur ein Wert.

Und zwar sollen in der Spalte A alle Werte mit PK oder P
auftauchen und in der Spalte B alle 6 stelligen Zahlenfolgen.

Hallo Jankat,

nur aus darstellerischen Gründen andere Spalten genommen.
Formeln von D:G gehören bei dir in H:K.

In Tabelle2!A1 kannste dann schreiben:
=Wenn(istfehler(Tabele1!J1);"";Tabele1!J1)
und nach unten und in B kopieren.

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │ D │ E │ F │ G │
──┼──────────┼──────────┼─────────┼──────────┼─────────┼──────────┼─────────┤
1 │ 14 PK450 │ │ │ 14 PK450 │ │ 14 PK450 │ 14 4128 │
──┼──────────┼──────────┼─────────┼──────────┼─────────┼──────────┼─────────┤
2 │ │ 14 PK406 │ │ 14 PK406 │ │ 14 PK406 │ 14 5159 │
──┼──────────┼──────────┼─────────┼──────────┼─────────┼──────────┼─────────┤
3 │ │ 14 P4983 │ │ 14 P4983 │ │ 14 P4983 │ #ZAHL! │
──┼──────────┼──────────┼─────────┼──────────┼─────────┼──────────┼─────────┤
4 │ │ │ 14 4128 │ │ 14 4128 │ #ZAHL! │ #ZAHL! │
──┼──────────┼──────────┼─────────┼──────────┼─────────┼──────────┼─────────┤
5 │ │ │ 14 5159 │ │ 14 5159 │ #ZAHL! │ #ZAHL! │
──┴──────────┴──────────┴─────────┴──────────┴─────────┴──────────┴─────────┘
Benutzte Formeln:
D1: =WENN(ISTNV(VERGLEICH("\*P\*";A1:C1;0));"";INDEX(A1:C1;1;VERGLEICH("\*P\*";A1:C1;0)))
D2: =WENN(ISTNV(VERGLEICH("\*P\*";A2:C2;0));"";INDEX(A2:C2;1;VERGLEICH("\*P\*";A2:C2;0)))
usw. in D

E1: =WENN(D1="";INDEX(A1:C1;VERGLEICH("\*";A1:C1;0));"")
E2: =WENN(D2="";INDEX(A2:C2;VERGLEICH("\*";A2:C2;0));"")
usw. in E

Benutzte Matrixformeln:
F1: {=WENN(ZEILE(D1)\>ANZAHL2(D:smiley:);"";INDEX(D:smiley:;KKLEINSTE(WENN(D$1:smiley:$1000"";ZEILE($1:blush:1000));ZEILE(A1))))}
F2: {=WENN(ZEILE(D2)\>ANZAHL2(D:smiley:);"";INDEX(D:smiley:;KKLEINSTE(WENN(D$1:smiley:$1000"";ZEILE($1:blush:1000));ZEILE(A2))))}
usw. in F

G1: {=WENN(ZEILE(E1)\>ANZAHL2(E:E);"";INDEX(E:E;KKLEINSTE(WENN(E$1:E$1000"";ZEILE($1:blush:1000));ZEILE(B1))))}
G2: {=WENN(ZEILE(E2)\>ANZAHL2(E:E);"";INDEX(E:E;KKLEINSTE(WENN(E$1:E$1000"";ZEILE($1:blush:1000));ZEILE(B2))))}
usw. in G

(Matrixformeln nicht mit "Enter" sondern mit "Strg+Shift+Enter" eingeben.
Die Spezialklammern nicht manuell eingeben, sie werden von Excel erzeugt.)

A1:G5
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

#3

Die Formeln funktionieren super, vielen lieben Dank!