Ein Nachteil von Sverweis ist, man kriegt immer nur ein Resultat. Meist das Erste:
Excel sucht in der definierten Tabelle und liefert das erste Resultat (auf jeden Fall wenn am Schluss die 0 steht, andere Varianten benutze ich kaum).
Was aber, wenn die Zieltabelle mehrere Resultate hätte? Wie kriege ich alle Treffer?
Die Lösung bietet die Funktion SVERWEIS2:
Als Resultat werden semikolongetrennt sämtliche Suchtreffer geliefert. Die Eingabe der Formel funktioniert gleich wie SVERWEIS, mit dem Unterschied, dass die 0 am Schluss nicht notwendig ist. Mit Dank an Gwe, der diese Funktion bei uns implementiert hat.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
Public Function SVERWEIS2(ByVal Suchkriterium As Variant, _ ByVal Bereich As Range, ByVal Spaltenindex As Integer) As String Dim arr() As Variant Dim ws As Worksheet Dim rng As Range Dim str As String Debug.Print Application.Caller.Worksheet.Index Set ws = Bereich.Parent Set rng = ws.UsedRange Debug.Print rng.Address Set Bereich = Intersect(Bereich, rng) Debug.Print Bereich.Address arr = ws.Range(Bereich.Address) str = "" For i = LBound(arr) To UBound(arr) If Suchkriterium = arr(i, 1) And Suchkriterium <> "" And Not (IsEmpty(Suchkriterium)) And arr(i, 1) <> "" And Not (IsEmpty(arr(i, 1))) And Not (IsEmpty(arr(i, Spaltenindex))) Then Debug.Print arr(i, Spaltenindex) If str <> "" Then str = str & ";" & arr(i, Spaltenindex) Else str = arr(i, Spaltenindex) End If End If Next i If str <> "" Then SVERWEIS2 = str Else SVERWEIS2 = "#NV" End If End Function |