Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1120to1124
1120to1124
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

SVERWEIS für zweite Zeile

Forumthread: SVERWEIS für zweite Zeile

SVERWEIS für zweite Zeile
Horst
Hallo Excel-Gemeinde!
Ist es möglich, vom SVerweis für ein und dasselbe Suchkriterium mehrere Rückgabewerte zu bekommen?
Beispiel: In Spalte A steht das Datum, in Spalte B steht das gesuchte Kriterium. Nun ist das Problem, dass das gleiche Datum z.b. der 25.11.09 in Spalte A mehrmals vorkommen kann und in Spalte B für denselben Tag unterschiedliche Kriterien stehen. Die SVerweis-Funktion gibt mir nur jeweils die erste Zeile von Spalte B zurück. Wie erreiche ich, dass auch in der zweiter und dritten Zeile der Datumsspalte gesucht wird?
Besten Dank für Eure Vorschläge!
Anzeige
AW: SVERWEIS für zweite Zeile
29.11.2009 21:11:30
Horst
Danke Hary!
Das hab' ich auch gerade entdeckt, bin gerade am experimentieren. Wenn jemand aber auf Anhieb die Lösung weiß, bitte reinstellen!
Anzeige
AW: SVERWEIS für zweite Zeile
29.11.2009 21:29:09
hary
Hallo
Tabellenblattname: Tabelle1
 

A

B

C

D

E

1

01.11.2009

a

gesucht

01.11.2009

a

2

02.11.2009

a

 

 

b

3

03.11.2009

f

 

 

c

4

04.11.2009

a

 

 

 

5

05.11.2009

a

 

 

 

6

06.11.2009

a

 

 

 

7

07.11.2009

a

 

 

 

8

08.11.2009

e

 

 

 

9

09.11.2009

a

 

 

 

10

01.11.2009

b

 

 

 

11

02.11.2009

a

 

 

 

12

03.11.2009

d

 

 

 

13

04.11.2009

a

 

 

 

14

01.11.2009

c

 

 

 


Benutzte Formeln:
E1: =INDEX(B:B;VERGLEICH(D1;A:A;))
E2: =WENN(MAX(ISTNV(VERGLEICH(B$1:B$99;E$1:E1;))*(A$1:A$99=$D$1))=0;"";INDEX(B:B;MIN(WENN(ISTNV(VERGLEICH(B$1:B$99;E$1:E1;))*(A$1:A$99=$D$1);ZEILE($1:$99)))))
E3: =WENN(MAX(ISTNV(VERGLEICH(B$1:B$99;E$1:E2;))*(A$1:A$99=$D$1))=0;"";INDEX(B:B;MIN(WENN(ISTNV(VERGLEICH(B$1:B$99;E$1:E2;))*(A$1:A$99=$D$1);ZEILE($1:$99)))))
E4: =WENN(MAX(ISTNV(VERGLEICH(B$1:B$99;E$1:E3;))*(A$1:A$99=$D$1))=0;"";INDEX(B:B;MIN(WENN(ISTNV(VERGLEICH(B$1:B$99;E$1:E3;))*(A$1:A$99=$D$1);ZEILE($1:$99)))))
Ansatz reicht?
Gruss hary
Anzeige
ab E2 Matrix Formel strg+shift+enter owT
29.11.2009 21:30:36
hary
.
AW: ab E2 Matrix Formel strg+shift+enter owT
29.11.2009 21:54:33
Horst
Danke Hary!
Hab' die Matrixformel bereits implementiert, läuft soweit alles prima. Mein Problem ist nur, dass ich knapp 40 verschiedene Suchkriterien habe und das gleiche Datum bis zu fünfmal vorkommen kann. Muss ich jetzt für das jeweilige Suchkriterium in Spalte 1,2,3,4,5 jeweils 'einzeln' suchen oder gibt es eine Formel, die mir mit einem Mal (sozusagen untereinander) alle Suchkriterien eines bestimmten Datums ausgibt.
Anzeige
AW: SVERWEIS für zweite Zeile
29.11.2009 21:48:18
Andre´
Hallo Horst,
hier noch eine Möglichkeit:

Die Datei https://www.herber.de/bbs/user/66267.xls wurde aus Datenschutzgründen gelöscht


MFG Andre
AW: SVERWEIS für zweite Zeile
29.11.2009 22:08:43
Horst
Danke André!
Die VBA-Funktion is ja Weltklasse! :-)) Kann man die auch so umschreiben, dass statt "a,b,c" die Suchkriterien in Spalten nebeneinander (ohne Beistrich) geschrieben werden; also so wie in der darunter angegebenen Formel?
Anzeige
AW: SVERWEIS für zweite Zeile
29.11.2009 22:32:56
Horst
Perfekt, das ist es was ich meine:
Wie müsste ich den VBA-Code ändern, dass er die Kriterien nicht durch Beistrich trennt, sondern jeweils in neue Spalten schreibt wie in 66269.xls?
Public Function SVERWEIS2(Kriterium As String, Bereich As Range, SuchSpalte As Integer,  _
ErgebnissSpalte As Integer, Optional Trenner As String = ", ") As String
Dim arrTmp
Dim L As Long
arrTmp = Bereich
For L = 1 To UBound(arrTmp)
If arrTmp(L, SuchSpalte) = Kriterium Then _
If InStr(1, SVERWEIS2, arrTmp(L, ErgebnissSpalte)) = 0 Then _
SVERWEIS2 = SVERWEIS2 & arrTmp(L, ErgebnissSpalte) & Trenner
Next
SVERWEIS2 = Left(SVERWEIS2, Len(SVERWEIS2) - Len(Trenner))
End Function

Anzeige
kann Dir nicht weiter helfen oT.
29.11.2009 22:47:14
Andre´
AW: kann Dir nicht weiter helfen oT.
29.11.2009 22:56:04
Horst
Trotzdem besten Dank! Im Prinzip müsste in der zweiten Zeile des Codes statt dem "," nur angegeben werden, dass in eine neue Spalte geschrieben wird. Die Frage ist nur, wie der VBA-Befehl dazu lautet?
Da diese udFkt nicht von mir ist, enthält...
30.11.2009 02:02:04
mir
...sie diese Fktionalität nicht, Horst... ;-)
Du kannst das integrieren, indem du in der Wertzuweisungszeile noch ein Split(..., ", ") um den Zuweisungswert legst. Das Ergebnis ist dann immer ein 1zeiliger Vektor (Feld, Array, ggf aus nur 1em Wert), der per Matrixfml-Schreibweise auf die markierten Zellen einer Zeile aufgeteilt wdn kann. Ggf kannst du auch eine 2.udFkt, die nur das erledigt, um die 1. „herumlegen“ [Alle Recherchen → Splitt als Bsp].
Gruß Luc :-?
Anzeige
;

Forumthreads zu verwandten Themen

Sverweis - nicht ersten Wert
Fabio

Hallo zusammen,
ich habe in der einen Tabelle Namen stehen - mit einem zugeordneten Sport-Tainer. In einer anderen Tabelle habe ich die Namen/Trainer aus 2010 stehen. Nun will ich
-> die Trainer aus 2010 den Trainern 2011 zuordnen
-> das mache ich mit SVerweis (SVERWEIS(A2;[p...
22.03.2011 10:14:36
Thread-ID: 1206764
3 Beiträge
sverweis aber mehrere werte ausgeben
Mert

Hallo Kollegen,
ich habe einige Foren angeschaut und versucht zu verstehen. Aber habe es nicht ganz geblickt um ehrlich zu sein.
Ich habe eine Tabelle mit typen und die werden zu etwas zugeordnet. Es kommt auch vor, dass ein Typ mehrere Zuordnungen hat.
Wie mach ich das?
Ich hab...
27.05.2019 16:50:14
Thread-ID: 1694960
17 Beiträge
Mit SVERWEIS auf zweiten Wert zugreifen
Jan S.
Hallo zusammen,
ich hatte schon eimal hier gefragt, wie ich mit SVERWEIS auf den zweiten Wert zugreife.
Damals wurde mir folgender Link gegeben:

http://www.excelformeln.de/formeln.html?welcher=28

Mein Problem ist nun, dass die Lösung dort bei mir nicht funktioniert.
Ich habe...
27.10.2003 16:27:08
Thread-ID: 329398
2 Beiträge
Anzeige
Daten auslesen, aber nicht aus der ersten Spalte
Edwin

Hallo,
mit folgendem VBA-Code lese ich Daten aus einer Datei.
Klappt soweit!
Allerdings wird hier die erste Spalte ausgelesen.
Was muss ich umschreiben, damit die zweite Spalte gelesen wird?
Gruß
Edwin
Sub LeseDaten() 'Lese Daten aus Datendatei Const ersteSchreibzel...
17.12.2004 14:13:34
Thread-ID: 534866
9 Beiträge
Bei Sverweis nicht nur erstes Ergebniss wiedergebe
sascha76er

Hallo,
ich möchte bei einem Sverweis nicht nur das erste Ergebniss wiedergeben bekommen, sondern wenn mehrere Treffer dem Suchergebniss diese als Summe wiedergeben.
Ist das möglich?
Gruß
Sascha
24.06.2008 18:32:00
Thread-ID: 988442
6 Beiträge
nicht erste oder Letzte Zelle einer Spalte sondern vorherige
Jan
Hi, ich suche eine Formel die mir din Wert der vorherige Zelle eine spalte wieder gibt , bisher habe ich nur Formeln gefunden die mir den Wert der ersten nicht leere oder letzten nicht leere Zelle wiedergeben , kann mir einer helfen ?

Mit freundlichen Grüßen
Jan
22.01.2024 13:16:58
Thread-ID: 1961492
9 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

SVERWEIS für die zweite Zeile in Excel anwenden

Schritt-für-Schritt-Anleitung

  1. Daten vorbereiten: Stelle sicher, dass Deine Daten in einer Excel-Tabelle organisiert sind, z.B. mit den Datumswerten in Spalte A und den zugehörigen Kriterien in Spalte B.

  2. Formel für den SVERWEIS anpassen: Um mehrere Rückgabewerte für dasselbe Suchkriterium zu erhalten, kannst Du die INDEX- und VERGLEICH-Funktionen verwenden.

    Beispiel:

    =INDEX(B:B;VERGLEICH(D1;A:A;0))

  3. Matrixformel erstellen: Um alle Werte für ein bestimmtes Datum zu erhalten, kannst Du die folgende Matrixformel verwenden. Setze diese Formel in den ersten Zellen der Ergebnisse:

    =WENN(MAX(ISTNV(VERGLEICH(B$1:B$99;E$1:E1;))*(A$1:A$99=$D$1))=0;"";INDEX(B:B;MIN(WENN(ISTNV(VERGLEICH(B$1:B$99;E$1:E1;))*(A$1:A$99=$D$1);ZEILE($1:$99)))))

    Drücke STRG + UMSCHALT + ENTER, um die Formel als Matrixformel zu bestätigen.

  4. Ergebnisse überprüfen: Überprüfe die Zellen, in denen Du die Formeln eingefügt hast, um sicherzustellen, dass die richtigen Werte angezeigt werden.

Häufige Fehler und Lösungen

  • Fehler: #NV oder #WERT!: Dies passiert häufig, wenn das Suchkriterium nicht in der ersten Spalte gefunden wird. Stelle sicher, dass Du den SVERWEIS nicht in der ersten Spalte suchst, sondern die richtigen Bereiche angibst.

  • Fehler: Zu viele Rückgaben: Wenn Du mehr Werte zurückgibst als erwartet, überprüfe Deine Formel auf richtige Bedingungen und Anzahlen.

Alternative Methoden

  • VBA-Funktion erstellen: Du kannst auch eine benutzerdefinierte Funktion in VBA schreiben, um mehrere Rückgabewerte für den SVERWEIS zu erhalten. Eine einfache VBA-Funktion könnte so aussehen:

    Public Function SVERWEIS2(Kriterium As String, Bereich As Range, SuchSpalte As Integer, ErgebnissSpalte As Integer) As String
   Dim arrTmp
   Dim L As Long
   arrTmp = Bereich
   For L = 1 To UBound(arrTmp)
       If arrTmp(L, SuchSpalte) = Kriterium Then
           SVERWEIS2 = SVERWEIS2 & arrTmp(L, ErgebnissSpalte) & ", "
       End If
   Next
   SVERWEIS2 = Left(SVERWEIS2, Len(SVERWEIS2) - 2) ' Entferne das letzte Komma
End Function

  • XLOOKUP (Excel 365): Wenn Du Excel 365 verwendest, kannst Du die neue XLOOKUP-Funktion nutzen, die flexibler ist als SVERWEIS.

Praktische Beispiele

Angenommen, Du hast folgende Daten:

A B
01.11.2009 a
01.11.2009 b
02.11.2009 a
02.11.2009 c

Um alle Kriterien für den 01.11.2009 zu finden, kannst Du die oben beschriebene Formel oder die VBA-Funktion verwenden.

Tipps für Profis

  • Nutze die WENNFEHLER-Funktion, um die Ausgabe der Formeln zu verbessern:

    =WENNFEHLER(INDEX(...); "Nicht gefunden")

  • Vermeide die Verwendung von SVERWEIS bei großen Datenmengen, da die Berechnungen langsamer sein können. INDEX und VERGLEICH sind oft effizienter.

FAQ: Häufige Fragen

1. Wie kann ich den SVERWEIS für eine andere Spalte verwenden? Du musst die Suchspalte und die Ergebnisspalte in Deiner Formel entsprechend anpassen. Der SVERWEIS funktioniert nur, wenn die Suchspalte die erste Spalte im angegebenen Bereich ist.

2. Kann ich mehrere Rückgabewerte in einer Zeile anzeigen? Ja, indem Du eine Matrixformel verwendest oder eine VBA-Funktion erstellst, die die Werte in einer Zeile anzeigt.

3. Was tun, wenn meine Daten nicht sortiert sind? Der SVERWEIS funktioniert auch mit unsortierten Daten, solange er in der richtigen Spalte sucht. Achte darauf, dass die Suchkriterien korrekt definiert sind.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige