Microsoft Excel

Herbers Excel/VBA-Archiv

Verweis mit 2 Kriterien

Betrifft: Verweis mit 2 Kriterien von: Neo
Geschrieben am: 01.10.2014 13:47:41

Moin Moin,
ich habe ein kleines Problem:
Ich habe eine Liste in der Aufträge stehen mit den Spalten: Kunde, Artikel, Menge, usw. und eine zweite in der alle Kunden und Artikel mit Zugeordneten Etikettennummern stehen.
Ich möchte jetzt ähnlich einem SVERWEIS mit den Kriterien Kunde und Artikel nach der Etikettennummer suchen und diese den Aufträgen zuordnen.
Habe jetzt drei Formel getestet die gut funktionieren wenn ich sie Testweise nur im zweiten Tabellenblatt nutze. Sobald die Formel in Blatt1 auf Blatt2 zugreifen soll funktioniert es nicht.

1:


=INDEX($C:$C;VERGLEICH(I1&J1;$A$1:$A$999&$B$1:$B$999;0))

2:

=VERWEIS(2;1/(A2:A999=I1)/(B2:B999=J1);C2:C999)

3:

=VERWEIS(2;1/(A:A&B:B=I1&J1);C:C)

Übergreifende Formel1:

=INDEX(Zusatzinfo!C:C;VERGLEICH(A2&F2;Zusatzinfo!A2:A999&Zusatzinfo!F2:F999;0))

Übergreifende Formel2:

=VERWEIS(2;1/(Zusatzinfo!$A$2:$A$999=A3)/(Zusatzinfo!$B$2:$B$999=Aufträge!F3);Zusatzinfo!$C$2:$C$999)  

Benutze ich die richtigen Formel?
Gibt es andere Wege?
Geht das überhaupt?

Ich freue mich auf eure Antworten!

  

Betrifft: was funktioniert nicht ? ... von: neopa C (paneo)
Geschrieben am: 01.10.2014 14:13:34

Hallo,

... denn beide Formeln sollten auf den ersten Blick gesehen auch Tabellenblatt übergreifend ein korrektes Ergebnis ergeben (wenn ein solches auch vorliegt), wobei die INDEX() natürlich als MATRIX-Formel auch einzugeben ist und im ungünstigsten Fall noch etwas erweitert werden muss.

Wenn es wie bei Dir nicht der Fall ist, könnte evtl. eine Zirkelbezug die Ursache sein.

Gruß Werner
.. , - ...


  

Betrifft: AW: was funktioniert nicht ? ... von: Neo
Geschrieben am: 01.10.2014 14:27:01

Danke für die Antwort!

Excel spuckt entweder #NV oder falsche Werte aus.

Gruß Bernd


  

Betrifft: eine kleine Arbeitsmappe wäre hilfreich ... von: neopa C (paneo)
Geschrieben am: 01.10.2014 14:35:52

Hallo Bernd,

... reduziere Deine Datei in einer Kopie auf das notwendige und stelle diese hier ein, damit Dein Problem auch nachvollziehbar ist.


Gruß Werner
.. , - ...


  

Betrifft: Mit einem ganz einfachen Test kann man ... von: Luc:-?
Geschrieben am: 01.10.2014 15:13:55

…das nachgestalten, Neo(pa),
ganz ohne eine BspDatei zu benötigen, denn der „Übeltäter“ ist schnell dingfest gemacht → in ÜgFml1 ist es Zusatzinfo!C:C, wodurch die Zählung in C1 beginnt, obwohl relevante Daten wohl erst ab C2 stehen. Dadurch verschiebt sich das Ergebnis nach oben.
Im Falle von ÜgFml2 liegt der Fehler wohl eher nicht in der Fml, falls die Daten der Fml entsprechen. Im Test entsprd nachgestellt fktioniert sie jedenfalls erwartungsgemäß.
Gruß, Luc :-?


  

Betrifft: ich hatte nur Formel 2 kurz gecheckt ... von: neopa C (paneo)
Geschrieben am: 01.10.2014 15:21:12

Hallo Luc,

... und blind angenommen, dass Zusatzinfo!$C$2:$C$999 auch in der ersten Formel verwendet wird.


Gruß Werner
.. , - ...


  

Betrifft: AW: ich hatte nur Formel 2 kurz gecheckt ... von: Neo
Geschrieben am: 02.10.2014 07:41:32

Moin,
habe jetzt eine reduzierte Datei angelegt.
Bin gespannt auf eure Analysen!
https://www.herber.de/bbs/user/92938.xlsx

Gruß Bernd


  

Betrifft: Äpfel sind keine Birnen ... von: neopa C (paneo)
Geschrieben am: 02.10.2014 11:24:43

Hallo Bernd,

... Deine INDEX(()-Formel vergleicht mit ...&Zusatzinfo!$F$2:$F$999. Die zu vergleichenden Daten liegen aber nicht in Spalte F sondern in B und Deine VERWEIS()-Formel vergleicht ab Zeile 2 mit Daten die für Zeile 7 relevant sind.

Na und dann gibt es auch noch Daten-Kombinationen die Du suchst, die aber (momentan) noch nicht in Zusatzinfo!A:B vorhanden sind. Letzteres kannst Du in den Formeln, wie bereits geschrieben mit WENNFEHLER() abfangen.

Deine beiden Formeln würde ich nun aber wie folgt schreiben. Wo sich dann das Ergebnis 0 ergibt, hast Du einfach noch keine Datenwerte in Zusatzinfo!C:C zu stehen.

In L2:

{=WENNFEHLER(INDEX(Zusatzinfo!C:C;VERGLEICH(A2&F2;Zusatzinfo!A$1:A$999&Zusatzinfo!B$1:B$999;0));"")}

und in M2:
=WENNFEHLER(VERWEIS(9;1/(Zusatzinfo!A$1:A$999=A2)/(Zusatzinfo!B$1:B$999=F2);Zusatzinfo!C:C);"")


Gruß Werner
.. , - ...


  

Betrifft: AW: Äpfel sind keine Birnen ... von: Neo
Geschrieben am: 02.10.2014 13:25:40

Vielen Dank funktioniert Super.
Gerade die Verweisformel mit "(9;1/..." teil habe ich nicht verstanden.

Schönen Gruß aus dem sonnigen Paderborn
Bernd


  

Betrifft: zum (vielleicht) besseren Verständnis ... von: neopa C (paneo)
Geschrieben am: 02.10.2014 14:18:48

Hallo Bernd,

... lies dazu u.a. mal in diesem Thread hier: http://www.online-excel.de/fom/fo_read.php?f=1&bzh=78249&h=78168#a123x


Gruß Werner
.. , - ...


 

Beiträge aus den Excel-Beispielen zum Thema "Verweis mit 2 Kriterien"