Live-Forum - Die aktuellen Beiträge
Datum
Titel
08.10.2025 18:39:44
Hilfe beim VBA-Code
08.10.2025 12:36:22
Hinweis für Word auf dem MAC
08.10.2025 12:35:32
Datumsabhängige Einträge in Zellen. Bedingter Formatierung.
08.10.2025 11:44:01
Faktor Uhrzeit hinzu fügen
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1324to1328
1324to1328
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Sverweis mit 2 Treffern

Forumthread: Sverweis mit 2 Treffern

Sverweis mit 2 Treffern
16.08.2013 12:28:25
Nico
Hallo zusammen!
Ich kämpfe gerade mit einem Sverweis und hoffe, ihr könnt mir helfen. Mein Problem ist Folgendes:
Ich suche per Sverweis in einer Liste nach einem Begriff in Spalte A und möchte den dazugehörigen Wert in Spalte B erhalten. Leider konnt der Suchbegriff 2x in der Liste vor und ich benötige den Wert des zweiten Treffers. Aber leider liefert der Sverweis ja immer nur den ersten Treffer. Da sich die Zusammensetzung der Liste häufiger mal verschiebt, kann ich auch nicht einfach die Matrix anpassen.
Gibt es z.B. die Möglichkeit, dass der Sverweis am Ende der Liste mit der Suche beginnt?
Ich hoffe, jemand von euch hat eine Idee!
Danke schonmal!
Nico

Anzeige

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Sverweis von unten
16.08.2013 12:30:48
unten
wäre eine alternative Suchanfrage gewesen bei der du im Forum sicherlich fündig geworden wärest Nico.
Aber hier mal ein Ansatz 
=VERWEIS(9;1/((A2:A999="B")*(A1:A998="A"));B2:B99)
Grüße

AW: Sverweis von unten
16.08.2013 12:45:14
unten
Hi JACKD,
vielen Dank für die schnelle Antwort. Ich hatte zuerst im Forum gesucht, habe aber nichts passendes gefunden.
Leider verstehe ich die Syntax der Formel nicht. Ich möchte in Spalte A nach dem zweiten Treffer für "TI" suchen. Wärst du so nett mir zu erklären, wie ich die Formel anwenden kann?
Danke & Grüße,
Nico

Anzeige
AW: Sverweis von unten
16.08.2013 12:53:17
unten
Hallo Nico
So geht es in etwa.
Du solltest nur beachten das nicht das 2. Ergebnis gesucht wird sondern das letzte (sverweis von unten halt)



Tabelle1
 ABCDEFG
1WasAusgeben         
2TIZeile 1  SuchkriteriumTIZeile 7 
3TOZeile 2         
4TÖZeile 3         
5TOZeile 4         
6TUZeile 5         
7TUZeile 6         
8TIZeile 7         
9TÖZeile 8         
10             
11             

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
F2=VERWEIS(9;1/(A1:A998=E2) ;B1:B98)   =LOOKUP(9,1/(R[-1]C[-5]:R[996]C[-5]=RC[-1]) ,R[-1]C[-4]:R[96]C[-4])

XHTML Tabelle für Darstellung in Foren,
einschl. Bedingter Formatierung ab Version 2007
XHTML-Version 11.26


Anzeige
Formellösung mit doppel-Index
16.08.2013 12:57:25
Klaus
Hi Nico,
wenn es zwei Werte gibt und du den zweiten suchst, kann es so gehen (Für A1:A10000)
=INDEX(B:B;VERGLEICH("TI";INDEX(A:A;VERGLEICH("TI";A:A;)+1):A10000;)+VERGLEICH("TI";A:A;))
Sucht den ersten Wert TI, erstellt eine Matrix ab der ersten Fundstelle TI (+1 Zeile) bis 10000, sucht innerhalb dieser Matrix erneut nach TI.
1.Fundstelle + 2.Fundstelle ergibt Zeile der 2.Fundstelle in der Gesamtmatrix, per INDEX wird in B:B auf diese Zeile verwiesen.
Grüße,
Klaus M.vdT.
Anzeige
;

Forumthreads zu verwandten Themen

Suchen, Sverweis Vergleichen über einen Bereich
KLE

Hallo zusammen,
..habe mal wieder eine Blockade im Hirn und suche hier Unterstützung, Anregungen und/oder Ideen...
Folgende Herausforderung (Musterdatei liegt hier bei):
Ich habe eine Adressen-Liste und muss es aus statistischen Gründen in Gruppen zusammenfassen. Soll heißen, alle "...
18.08.2008 08:39:00
Thread-ID: 1002501
5 Beiträge
SVERWEIS, Bezug indirekt eingeben
Peter

Guten Tag
Nachfolgende Formel bezieht sich auf die Zelle X19 und funktioniert soweit gut.
=SVERWEIS(X19;eDaten;SPALTE(eTitel);FALSCH)
Nun möchte ich den Bezug nicht fix eingeben, sondern auf Spalte D referenzieren. Dort steht als Formelauswertung in Zelle D19 "X19".
Wie kann ich...
10.09.2008 13:16:28
Thread-ID: 1008766
3 Beiträge
SVERWEIS-INDEX-VERGLEICH o. so etwas
ludicla

Hallo Excel Profis,
ich hab ein Problem mit Daten aus einer externen Datei die immer in unterschiedlichen
Gliederungen nach Excel kopiert wird.
Im Anhang ein Muster.
Die Daten und die Stationen werden immer mehr darum brauche ich eine verkürzte Formel.
https://www.herber.de/...
14.10.2008 10:37:15
Thread-ID: 1016258
3 Beiträge
Fehler in Formel SVERWEIS WENN
David

Hallo zusammen,
ich finde den Fehler in folgender Formel nicht:
Er markiert mir den letzten SVERWEIS bei der Fehlersuche.
Gibt es nen Limit für Anzahl SVERWEISE in einer Formel?
Sind doch auch nur 6 WENN-Funktionen.......benutze Excel 2002.
=WENN(D6="Clamp A Euro 9/16";SVERW...
16.10.2008 12:59:00
Thread-ID: 1016838
10 Beiträge
SVERWEIS mit INDIREKT
Franz

Hallo alle,
ich benötige euere Hilfe.
Etwas stimmt in der Formel nicht, doch was?
=SVERWEIS(A1;INDIREKT(B1&C1&D1);2;Falsch) ergibt #BEZUG!
Erklärung zu Suchmatrix:
B1&C1&D1 = aus drei Texten zusammengesetzter Name für einen benannten Bereich im Workbook.
...
16.10.2008 15:04:00
Thread-ID: 1016885
10 Beiträge
Wert der letzten Zeile in Bereich SVerweis
Elmar

Hallo,
vielleicht kann mir jemand helfen, wie ich in VBA dieser Formel ==ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Haupttabelle!R1C1:R65000C7,2,FALSE)"== beibringe, die 65000 durch die letzte belegte Zeile in einer Tabelle zu ersetzen. Die Abfrage mache ich mit ***iRow = Cells(Rows.Count, 3...
28.10.2008 20:45:00
Thread-ID: 1020084
5 Beiträge
SVERWEIS findet Kriterium nicht
Stephan

Hallo zusammen,
ich stehe mal wieder vor einem Rätsel. Mit folgendem SVERWEIS hole ich mir Werte aus einer anderen, geschlossenen Datei. Das kuriose daran ist, mal funktioniert das und mal nicht, obwohl das Suchkriterium (AuftragsNr) als Zahl formatiert und vorhanden ist. Woran könnte das l...
05.11.2008 16:14:47
Thread-ID: 1021972
9 Beiträge
Anzeige
Problem mit sicherlich einfachem SVerweis
Jessi

Hallo zusammen,
möchte zum ersten mal einen SVerweis verwenden und bekomme eine Fehlermeldung.
Ist zum Berechnung von einem Kraftstoffverbrauch bezogen auf eine Strecke die von Station A zu Station B läuft.

Wo liegt mein Fehler? Habe diese Verweise sehr häufig in die Liste einz...
07.11.2008 07:51:00
Thread-ID: 1022490
4 Beiträge
Anzeige
SVerweis + zusätzlichen Text in der Zelle
Benny

Hallo Zusammen!
Sollte dieses Problem lösen! Kann mir jemand helfen?
Habe einen SVerweis und möchte die Zelle weiter befüllen. Also mir soll es möglich sein einfach in die Zelle zu klicken und weiter zu schreiben.
Bitte helft mir ;-)
Gruß Benny
07.11.2008 13:39:13
Thread-ID: 1022658
5 Beiträge
Wert in SVERWEIS autom. anpassen
Ron

Hallo Excelfreunde
Ich habe folgende Frage. In meinem SVERWEIS habe ich als letzte Zelle denn Eintrag $L$15
=SVERWEIS(A65;$J$2:$L$15;3;0)
Jetzt kann es aber sein, dass die vorhandene Menge grösser oder kleiner wird. Das heisst ich müsste diesen Wert automatisch anpassen können. Wie...
12.11.2008 10:04:00
Thread-ID: 1023730
7 Beiträge
2 Makro-Sverweise kombiniert
alex

Hallo zusammen,
ich hätte eine Frage bzgl. Sverweis:
Code:
With Range("M16")
.Formula = "=VLookup(N16," & _
"'Z:\Basisdaten\[Basis.xls]Tabelle1'!K5:L200, 2, False)"
.Formula = .Value
End With
bewirkt: schaue mit "N16"; in anderer Datei (Basis.xls);...
20.11.2008 12:13:00
Thread-ID: 1026372
8 Beiträge
SVERWEIS mit mehreren Einträgen
Tom

Hallo,
Ich führe ein Transaktionsjournal, welches ich auswerten möchte. Folgende Spalten habe ich zur Verfügung:
Datum
Text
Betrag
Pro Datum kann es mehrere Einträge geben.
Ich möchte nun eine konsolidierte Tabelle erstellen, in welcher pro Datum alle Texte und der Gesa...
19.01.2009 17:01:00
Thread-ID: 1041567
3 Beiträge
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Sverweis für mehrere Treffer in Excel nutzen

Schritt-für-Schritt-Anleitung

Um den Sverweis für den zweiten Treffer in Excel zu nutzen, kannst du folgende Schritte befolgen:

  1. Daten vorbereiten: Stelle sicher, dass deine Daten in der Tabelle ordentlich sortiert sind. In Spalte A sollten die Suchbegriffe stehen, während in Spalte B die zugehörigen Werte zu finden sind.

  2. Formel eingeben: Verwende die folgende Formel, um den zweiten Treffer zu ermitteln:

    =INDEX(B:B;VERGLEICH("Suchbegriff";INDEX(A:A;VERGLEICH("Suchbegriff";A:A;)+1):A10000;)+VERGLEICH("Suchbegriff";A:A;))

    Ersetze "Suchbegriff" durch den Begriff, nach dem du suchst.

  3. Ergebnisse überprüfen: Die Formel gibt dir den Wert des zweiten Treffers zurück, falls dieser existiert.

Häufige Fehler und Lösungen

  • Fehler: #NV zurückgegeben
    Lösung: Stelle sicher, dass der Suchbegriff auch tatsächlich mehr als einmal in der Liste vorkommt. Wenn er nur einmal vorhanden ist, kann kein zweiter Treffer gefunden werden.

  • Fehler: Falsches Ergebnis
    Lösung: Überprüfe die Bereiche in deiner Formel. Achte darauf, dass du die richtigen Zellbereiche angibst.

Alternative Methoden

  1. VERWEIS von unten: Eine andere Möglichkeit, um den letzten Treffer zu finden:

    =VERWEIS(9;1/((A2:A999="Suchbegriff")*(A1:A998="Suchbegriff"));B2:B99)

  2. Sverweis in R: Wenn du mit R arbeitest, kannst du den sverweis auch darin implementieren. Hier ist ein Beispiel:

    result <- tail(data[data$A == "Suchbegriff", "B"], 1)

Praktische Beispiele

Angenommen, du hast die folgende Tabelle:

A B
TI Zeile 1
TO Zeile 2
TI Zeile 3
TI Zeile 4

Um den zweiten Treffer für "TI" zu finden, würdest du die oben genannte Formel verwenden, und das Ergebnis wäre "Zeile 3".

Tipps für Profis

  • Dynamische Bereiche: Verwende dynamische Bereiche, um deine Formeln flexibler zu gestalten. Damit kannst du die Anzahl der Datenzeilen leicht anpassen.

  • Fehlerüberprüfung: Nutze die Funktion WENNFEHLER, um mögliche Fehler in deinen Formeln zu maskieren:

    =WENNFEHLER(INDEX(...); "Kein Treffer")

FAQ: Häufige Fragen

1. Wie kann ich alle Treffer auflisten?
Verwende die Funktion FILTER in Kombination mit INDEX, um alle Treffer in einer neuen Liste anzuzeigen.

2. Funktioniert dies auch in älteren Excel-Versionen?
Die oben genannten Methoden sind ab Excel 2010 verfügbar. Beachte, dass einige Funktionen in älteren Versionen möglicherweise nicht vorhanden sind.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige