Excel Spalten vergleich und Ergebnisausgabe

[rooney723]

Moin liebes Forum,

meine Excelkenntnisse sind begrenzt und ich habe nun ein Problem wo ich an meine Grenzen stoße. Ich bin mir sicher das Problem lässt sich leicht via Google lösen wenn man denn wüsste wonach man suchen soll. Von daher entschuldige ich meine Unwissenheit schon einmal im Vorfeld

Erstmal folgende Ausgangssituation, ich bekomme aus unseren Systemen diverse Reports und möchte gerne eine Übersicht erstellen über die Auftragsgebiete mit Zuordnung zur Auftragsnummer und Sollzeit.

Aus Liste 1 bekomme ich die Auftragsnummer inkl. des Auftragsgebietes.
Aus Liste 2 bekomme ich die Auftragsnummer inkl. der Sollzeiten pro Vorgang (heißt die Auftragsnummer ist in mehreren Zeilen hinterlegt).

Auftragsgebiet(1)	Auftragsnummer(1)	Auftragsnummer(2)	Vorgang(2)	Sollzeit(2)
Hamburg	1000	1000	1	1,05
Lübeck	1001	1000	2	2,35
Kiel	1002	1000	3	1,45
Hamburg	1004	1001	1	1,86

Als gemeinsamen Nenner habe ich die Auftragsnummer idendifiziert, die kommt natürlich in Liste 2 (in der Beispieltabelle ist die Listennummer in Klammer ergänzt) deutlich öfter vor als in Liste 1.
In der Realität habe ich in Spalte A und B jeweils 399 Datensätze, in C,D und E sind es jeweils 1080 Datensätze.

Meine Google Fähigkeit hat mich auf den VERWEIS Ansatz gebracht, der funktioniert aber nicht so wie ich mir das erhofft hatte.
Um die Daten aus Liste 2 weiterzuverarbeiten möchte ich im Prinzip eine Spalte F erstellen in der das Auftragsgebiet ausgegeben wird.
Mein ausgeklügelter Plan soll die Formel wie folgt verwenden:

=VERWEIS(C2;B2:B400;A2:A400)

Die dumme Sau schmeißt mir allerdings ein, mir zumindest, nichtssagendes #NV aus. Einige Zeilen funktionieren allerdings, warum auch immer.
Mir ist aufgefallen das die Formel beim normalen runterziehen (rechte untere Ecke und dann die ganze Spalte F runterziehen) jeweils den Such- und Ergebnisvektor der Zeile anpasst, sicher ein Problem welches ich soblad die Formel passt lösen kann via Google.

Vielleicht fehlt mir einfach nur der richtige Suchbegriff oder mein Ansatz ist komplett falsch, ich bin über jede Hilfe dankbar!

LG
rooney723

 

[Smily]

=XVERWEIS(C2;B:B;A:A)

Oder mit deiner
=VERWEIS(C2;$B$2:$B$400;$A$2:$A$400)
Ist das dein gewünschtes Ergebnis?

Das $ macht die Spalte oder die Zeile fest. Tatsächlich würde ich die Zeilen nicht begrenzen, sonst funktioniert deine Formel ab Zeile 400 nämlich nicht mehr. Also einfach A:A also ganze Spalte.

XVERWEIS ist besser und neuer, nutze ich nur.

 

[Tzk]

Ich wiederhole nur kurz, damit ich hoffentlich nichts falsch verstanden habe:
Du willst mit der Auftragsnummer quasi das Auftragsgebiet von Liste 1 in Liste 2 übertragen?

Da wäre SVERWEIS schon der richtige Ansatz. Zwei Sachen würde ich dabei beachten:

1. in Liste 1 sollten die Auftragsnummern aufsteigend sortiert sein (oben die Kleinste)
2. in Liste 1 sollte die Auftragsnummer links vom Gebiet stehen

SVERWEIS wird so genutzt:

SVERWEIS( Suchkriterium; Suchmatrix; Spalte; genauer Treffer?)

• Suchkriterium ist deine Auftragsnummer
• Suchmatrix sind die beiden Spalten mit Auftragsnummer und -gebiet in Liste 1
• Spalte ist die Spalte des Rückgabewerts, hier also also 2
• "genauer Treffer" würde ich immer setzen, damit das Ergebnis auch passt. Hier also FALSCH.

Schaut dann so aus und funktioniert natürlich auch mit zwei Tabellenblättern:

XVERWEIS ist besser und neuer, nutze ich nur.

Kann aber nicht jede Version von Excel. SVERWEIS sollte dagegen auch in Excel 2019 laufen... Aber ja, wenn XVERWEIS unterstützt wird sollte man es nutzen.

 

[rooney723]

=XVERWEIS(C2;B:B;A:A)

Super, danke! Damit funktioniert es einwandfrei

Da werd ich mich mal genauer einlesen, bisher läuft das ganze Thema hier eher stümperhaft nebenher.
Danke auch an @Tzk mit SVERWEIS statt VERWEIS funktioniert es ebenfalls!

 

[Tzk]

VERWEIS, SVERWEIS und XVERWEIS tun im Prinzip das gleiche, sind nur verschiedene Versionen der Funktion. VERWEIS ist das Älteste, dann kam SVERWEIS (u.A. Excel 2019) und mittlerweile wurde XVERWEIS eingeführt. Müsste man in Excel 365 und 2021 finden... Je nach Excel Version ist halt nicht jede Variante verfügbar.

XVERWEIS sollte etwas mächtiger als die anderen beiden sein. Wenn du X hast, dann nutz das.

 

[rooney723]

Danke!
Da wir auf absehbare Zeit mit Office 365 und all seinen Funktionen unterwegs sind wirds in der Zukunft der XVERWEIS werden

 

[Smily]

SVERWEIS hat zum Beispiel den Nachteil, man muss die Spalten zählen (wenn man das Ergebnis in Spalte J und die Suchspalte in AX hat wird das ekelig) und kann nur nach rechts, nicht nach links schauen.

Nur zur Info.

 

[steve1da]

@Smily: zählen muss man die Spalten nicht. Wenn man ganze Spalten markiert wird die Anzahl oberhalb der Markierung als Infotext ausgegeben, wenn man zB J2:AX10 markiert wird die Anzahl der Spalten im Namensfeld ausgeben.
Ansonsten hast du natürlich recht

 

[Smily]

wird die Anzahl der Spalten im Namensfeld ausgeben.

Ja natürlich, aber ist doch kagge, das muss ich ja vor der Formel schon vorbereiten und sich merken , mit XVERWEIS kann ich meine Spalte einfach anklicken.

mit Office 365

Dann habt ihr eh immer das neuste, perfekt. Wenn du noch was hast, melden. Weil die Tabelle soll ja sicher später noch mehr können/auswerten, oder?

 

[rooney723]

@Smily
Die Daten werden weiter als Pivot Tabelle und PowerBI verwurstet, da bin ich schon eher zu Hause.
In der Regel bekomme ich meine Daten schon clean, hier geht es im großen und ganzen um eine Problemregion und ich möchte gerne schauen ob unsere Daten das so wiederspiegeln

 

[Smily]

Die Daten werden weiter als Pivot Tabelle und PowerBI verwurstet

Ahso, OK, da bin ich eher raus . OK, dann war es nur der "einfach" XVERWEIS .

 

[steve1da]

Ja natürlich, aber ist doch kagge, das muss ich ja vor der Formel schon vorbereiten und sich merken , mit XVERWEIS kann ich meine Spalte einfach anklicken.

Nope, das funktioniert auch beim Tippen der Formel. Dann wirds aber nur in dem Infofeld angezeigt.