Funktion SVERWEIS mit mehreren Resultaten

Ein Nachteil von Sverweis ist, man kriegt immer nur ein Resultat. Meist das Erste:

2017-06-09_16-17-27

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:

2017-06-09_16-18-18

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.

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
Dieser Beitrag wurde unter Excel Funktionen veröffentlicht. Setze ein Lesezeichen auf den Permalink.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.