Sehr häufig werden in Foren zwei Fragen zum SVerweis gestellt. Einmal geht es darum, weshalb es nicht möglich ist, eine Suche nach links auszuführen. (Der SVerweis funktioniert nur von links nach rechts).
In der zweiten Frage geht es darum, ob nicht auch mehrere Treffer angezeigt werden können. (Der SVerweis zeigt immer nur den 1. Treffer aus der Suchmatrix an).
Dieser Problematik hatte sich Ransi *) vor einiger Zeit angenommen, und eine benutzerdefinierte Funktion geschrieben, die beide Möglichkeiten zur Verfügung stellt. Nun gibt es natürlich im Netz einige Formellösungen, die die eine oder die andere Möglichkeit ausführen können. Der Charme von Ransis Lösung ist aber der, dass beides gleichzeitig möglich ist.
An dieser Stelle einen herzlichen Dank an Ransi, dass er mir erlaubt hat, diese Funktion den Besuchern meiner Seite vorzustellen.
Option Explicit
Public Function SVERWEIS2(Kriterium As String, _
Bereich As Range, _
SuchSpalte As Integer, _
ErgebnisSpalte As Integer, _
Optional Unikate As Boolean = True, _
Optional Trenner As String = ", ") As String
'***********************************************
'Autor: Ransi
'***********************************************
Dim arrTmp
Dim L As Long
Dim Mydic As Object
arrTmp = Bereich
Set Mydic = CreateObject("Scripting.Dictionary")
If Unikate = True Then
For L = 1 To UBound(arrTmp)
If arrTmp(L, SuchSpalte) = Kriterium Then Mydic(arrTmp(L, ErgebnisSpalte)) = 0
Next
SVERWEIS2 = Join(Mydic.keys, Trenner)
Else:
For L = 1 To UBound(arrTmp)
If arrTmp(L, SuchSpalte) = Kriterium Then Mydic(L) = arrTmp(L, ErgebnisSpalte)
Next
SVERWEIS2 = Join(Mydic.items, Trenner)
End If
End Function
|
Wie man eine benutzerdefinierte Funktion speichert und benutzt, habe ich unter Funktionen beschrieben. Nach dem Aufrufen der Funktion wird dieser Funktionsassistent angezeigt:
Es gibt insgesamt sechs Parameter, von denen zwei optional sind.
| Name des Arguments | Beschreibung | erwartete Eingabe |
| Kriterium | Danach wird in der Suchspalte gesucht | Beliebiger Wert |
| Bereich | Der Tabellenbereich in dem die Suche stattfindet | Range der Listenmatrix |
| Suchspalte | Die Spalte, in der nach den Kriterien gesucht werden soll. | Numerischer Wert |
| Ergebnisspalte | Aus dieser Spalte werden die gefundenen Begriffe zurück gegeben. | Numerischer Wert |
| Unikate (optional) | Wenn Begriffe in der Ergebnisspalte mehrfach vorkommen, werde sie je nach Parameter einmal, oder mehrfach angezeigt. | Wahr oder Falsch |
| Trenner (optional) | Trennzeichen zwischen den Ergebnissen. Standard ist ein Komma. | Beliebiges Trennzeichen |
*) Ransi ist in vielen Excelforen präsent, und fällt dort durch seine kompetenten Antworten auf, obwohl er behauptet, nur über Kenntnisse für den Hausgebrauch zu verfügen.