Excel: Suchergebnisse aus SVERWEIS in alle Zeilen mit gleicher Angabe in anderer Spalte kopieren?

Beispiel - (Excel, Formel, Sverweis)

1 Antwort

Weiß nicht, ob ichs verstanden habe. Was mich irritiert ist, warum Du den Eintrag in Spalte K mal oben, mal in der Mitte des Blocks hast?

Wäre es möglich, die Tabelle (Kopie erst mal!) erst nach Spalte i (steigend), dann nach Spalte K (fallend) und zuletzt nach Spalte J (so, dass Texte ans untere Ende des Blocks kommen, ausprobieren) zu ordnen?

Dann könnte ich mir eine Formel vorstellen, die das schafft. (welche SVerweisformel hast Du eingesetzt? Nicht essentiell). Aber erst wieder heute Nacht.

Ich habe als Matrix eine Verkettung aus verschiedenen Feldern (Bezeichnung, EAN, Bestellnummer) aller Artikel unseres Artikelstamms genutzt. Leider kommen die nötigen Suchbegriffe auch im Fließtext unserer Bezeichnungen vor, daher die Verkettung und die sverweis-Formel unten.

In Spalte J findet der sverweis mal die 1. mal die 2. mal die 3. Angabe (usw.) zu einem Artikel aus Spalte I. Deshalb ist das Ergebnis in unterschiedlichen Zeilen zu einem Artikel.

Den Matrix Bereich habe ich einfach "matrix" genannt und folgende Formel benutzt:

=SVERWEIS("*"&I3&"*";matrix;2;FALSCH)

Sortieren wäre kein Problem, ich muss nur am Ende zu den Artikelnummern aus der Filiale unsere Artikelnummern zugeordnen haben.

0
@PhilEs

ich bin mir zwar immer noch nicht sicher, ob das so gemeint war, aber probier mal nach dem sortieren  [bendef sortieren-Sp.i aufsteigend- Ebene hinzufügen - Spalte k absteigend - Ebene hinzufügen - Sp.J aufsteigend]

in Sp.L (ggf neu einfügen und später woanders hin versetzen), L2 diese Formel:

=WENN(I1<>I2;K2;L1)

War es das oder habe ich da doch was missverstanden?

Wenn Du anschließend wissen willst, was alles unter den beiden Bezeichnungen in i und k in J zu finden ist (in einem mehrzeiligen Feld weiter oben), dann trägst du zB in M1/2:O7 ein:

M1Artikelnummer in i (neue Filiale), O1 Artikelnummer in K (Euer eigener Artikelstamm), eins von beiden über eine Verweisfunktion herzuholen, siehe später.

in N2 die Formel: =index(J:J;Vergleich($M$1;i:i;0))

in N3: =index(J:J;Vergleich($M$1;i:i;0)+1)

in N4: =index(J:J;Vergleich($M$1;i:i;0)+2)

in N5: =index(J:J;Vergleich($M$1;i:i;0)+3)

in N6: =index(J:J;Vergleich($M$1;i:i;0)+4)

ergibt die max. 5 Einträge, allerdings noch ohne Kontrollabfrage. Die wäre:

in M2 die Formel: =index(i:i;Vergleich($M$1;i:i;0))

in M3: =index(i:i;Vergleich($M$1;i:i;0)+1)

in M4: =index(i:i;Vergleich($M$1;i:i;0)+2)

in M5: =index(i:i;Vergleich($M$1;i:i;0)+3)

in M6: =index(i:i;Vergleich($M$1;i:i;0)+4)

gleiches für Bezug auf Spalte O und Kontrollabfrage O möglich

Die Paarung Index/Vergleich ist dem SVerweis in mehrfacher Hinsicht überlegen: mit dem Addenden +1..+4 kannst Du sie 4 Zeilen drunter "besuchen", also abfragen. Ausserdem muss die Suchspalte (Vergleich) nicht links stehen und drittens siehst Du sofort, dass die von index zurückgegebenen Werte aus entweder J:J oder aus i:i stammen. Darüberhinaus kann man ganze Spalten beliebig versetzen, die Formel passt sich an, beim SVerweis bleibt der Spaltenversatz eingefroren.

A propos Spalten versetzen: Das kannst Du natürlich nachträglich jetzt überall da machen, wo es Deine bisherige Struktur stört.

Wenn Du noch Fragen hast oder ich Dich missverstanden habe, melde Dich einfach nochmal!

1
@Iamiam

die Kontrollformel kannst du natürlich auch noch verkomplizieren und dadurch augenfälliger machen:

in M2: =WENN($M$1=INDEX(I:I;VERGLEICH($M$1;$I:$I;0));INDEX(I:I;VERGLEICH($M$1;$I:$I;0)))

in M3: =WENN($M$1=INDEX(I:I;VERGLEICH($M$1;$I:$I;0)+1);INDEX(I:I;VERGLEICH($M$1;$I:$I;0)+1))

usw.

Das meldet dann FALSCH, wenn die Nummer wechselt.

1
@Iamiam

Hab wohl gestern nacht schon halb geschlafen und vergessen, in die Hauptformel Deinen SVerweis einzufügen, also in L2

=WENN(I1<>I2;SVERWEIS("*"&I3&"*";matrix;2;FALSCH);L1)

(ginge natürlich auch mit Index/Vergleich:

=WENN(I1<>I2;index(die Holspalte vom SVerweis:K:K?;Vergleich("*"&I3&"*";die Suchspalte vom SVerweis:i:i?;FALSCH));L1)
muss aber nicht sein.

Da Vergleich die Finde-Zellnummer im (einspaltigen) Suchbereich wiedergibt und Index in seinem Bereich auf diese Zellnummer referenziert, ist es wichtig, dass beide Bereiche in der gleichen Zeile beginnen. Aber am einfachsten eben i:i oder K:K

1
@Iamiam

Vielen, vielen Dank für deine Mühe!

Die erste Formel 

=WENN(I1<>I2;K2;L1)"

reichte mir schon vollkommen. Allerdings musste ich dazu erst die Spalte K absteigend und anschließend die Spalte I aufsteigend sortieren, damit die Nummern in Spalte I jeweils als Paket untereinander stehen und die Ergebnisse in Spalte K immer in der ersten Zeile des jeweiligen Blocks stehen.

Jetzt bekomme ich die Liste endlich mal vom Tisch :)

1

Sverweis mit variirenden String

Hallo, ich bräuchte mal Eure Hilfe: Ich habe eine Tabelle mit ca. 100.000 Artikelnummern. Da gegen kommt eine etwa genau so große Tabelle allerdings mit dem folgendem Unterschied: Beispiel Tabelle 1 steht Artikel Nummer 8792654 Tabelle 2 steht Artikel Nummer 879.26-54 oder 0879.26-54 In gleicher Weise unterscheiden sich Alle Artikel so. Wie kann ich den Sverweis eingeben, dass er dennoch eine Übereinstimmung findet.

Vielen Dank vorab

...zur Frage

Kann ich zwei Tabellen mit SVerweis anhand der Mitgliedsnummer abgleichen und als Ergebnis alle neuen Mitglieder bekommen?

...zur Frage

Microsoft Excel "Verweis" Funktion funktioniert nicht?

Hallo Liebe Community,

ich habe eine Liste mit Artikeln und anderen Daten, diese wird autoamtisch vom System ausgespuckt aber leider habe ich dabei keine Möglichkeit die EAN Nummern der Artikel als extra Spalte hinzuzufügen. Daher wollte ich die Spalte manuell hinzufügen, da ich aber nicht immer alle Artikel habe und vor allem nicht immer die Sortierung, habe ich mich dafür mal auf Google informiert. Dabei bin ich auf die Excel Funktion "Verweis" gestoßen.

Kurz gefasst, habe ich eine zweite Excel Datei erstellt, bei der alle Artikelnummern und die EAN Nummern in Spalten A und B zugeordnet sind. Wenn ich nun aber in der ursprünglichen Datei die Funktion verwende, kommen dabei teilweise falsche EAN Nummern raus. Datei A hat die Artikelnummern und Bestelldaten und dort möchte ich die Spalte für EAN Nummern hinzufügen, dabei sind die Artikelnummern in Spalte C und die EAN soll in Spalte D. Datei B hat die Artikelnummern in Spalte A und die EAN Nummern in Spalte B. Nun habe ich in Spalte D in Datei A folgendes eingetragen:

=VERWEIS(C2;'{vorhergehender Pfad}[ean-datenbank.xlsx]Mappe'!A2:A395;'{vorhergehender Pfad}[ean-datenbank.xlsx]Mappe'!B2:B395)

(Mappe ist die Bezeichnung des Arbeitsblattes innerhalb der Datei)

Bei dem Feld D2 erhalte ich die korrekt EAN Nummer für die Artikelnummer aus C2, wenn ich aber die Formel in alle folgenden D Felder kopiere und natürlich vorne C2 entsprechend zu C3, C4, usw. ändere, dann erhalte ich scheinbar zufällige Werte aus Datei B Spalte B.

Habe ich vielleicht bei der Formel etwas falsch gemacht oder handelt es sich hier um ein anderes Problem bzw. verwende ich vielleicht sogar die falsche Formel bzw. Funktion? Vielen Dank für eure Antworten bereits im Voraus!

Mfg Eisman333

...zur Frage

Excel - Kopieren einer Zellen in gefilterter Tabelle

Hallo,

ich habe eine Excel-Tabelle erstellt, in der aufgrund einer SVERWEIS-Formel in einer Spalte einige Werte #NV ergeben. Diese habe ich per Filter ausgeblendet. In der nächsten Spalte will ich 2 Werte multiplizieren und kopiere die Formel runter. Er kopiert die Formel aber leider auch in die durch den Filter eigentlich ausgeblendeten Zeilen mit #NV. Wie kann ich das verhindern, dass er die Zeilen mit #NV, die er überspringen soll, trotzdem mit der kopierten Formel befüllt?

Danke

...zur Frage

Google Spreadsheet: Die Funktion 'SVERWEIS' funktioniert nicht. Warum?

Hallo,

ich benutze die folgende Formel in meiner Google-Tabelle (in Zelle H16): =SVERWEIS("";F7:F18;7;FALSCH).

Ich möchte, dass im Bereich F7:F18 die erste Zelle ohne Inhalt gefunden wird und dann die entsprechende Zeile der 7ten Spalte wiedergegeben wird.

Alles, was ich bekomme, ist #REF!. Allerdings stimmt die Referenz, den im Bereich F7-F18 gibt es nur einen einzigen Eintrag, nämlich ein Datum, dass in F7 steht.

Was mache ich falsch?

Danke im Voraus.

...zur Frage

Wie lautet die Formel wenn ich die SVERWEIS Funktion mit der SUCHEN Funktion verknüpfen will?

Es geht darum, dass in den Zellen in der die Sverveis Formel sucht, links vor der Zahl ein Buchstaben ist. Sverweis soll nun aber die Zeile auch finden, wenn nur die Zahl eingegeben wird.

...zur Frage

Was möchtest Du wissen?