XVLookup

makro.vbs

Function XVLookup(value As Range, dataSet As Range, returnCol As Integer, Optional indexValue As Integer = 0) As Variant
Dim val As String
Dim found As Integer
val = value.Cells

For counter = 1 To dataSet.Rows.Count
    Dim cellval As String
    cellval = dataSet.Cells(counter, 1)
    If cellval = val Then
        found = found + 1
        If indexValue = found Then
            cellval = dataSet.Cells(counter, returnCol)
            XVLookup = cellval
            Exit Function
        End If
    End If
    XVLookup = "#NA"
Next counter

End Function

Function XVLookupAll(value As Range, dataSet As Range, returnCol As Integer) As Variant
Dim val As String
Dim found As Integer
val = value.Cells
Dim result As String

For counter = 1 To dataSet.Rows.Count
    'Row = dataSet.Row
    Dim cellval As String
    cellval = dataSet.Cells(counter, 1)
    If cellval = val Then
        cellval = dataSet.Cells(counter, returnCol)
        If Len(result) > 0 Then
            result = result & ","
        End If
        result = result & cellval
    End If
Next counter
XVLookupAll = result
End Function

Function XVLookupCount(value As Range, dataSet As Range) As Variant
Dim val As String
Dim found As Integer
val = value.Cells

For counter = 1 To dataSet.Rows.Count
    'Row = dataSet.Row
    Dim cellval As String
    cellval = dataSet.Cells(counter, 1)
    If cellval = val Then
        found = found + 1
    End If
Next counter
XVLookupCount = found
End Function