I am a supporter of St. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation to St. Joseph's, please.

Information on
St. Joseph's

Donation Link

Some LOOKUP()-Variants which I found useful:

Function vlookupall$(strSearch As String, rngRange As Range, lngLookupCol As Long)
'Vlookupall searches in first column of rngRange for strSearch and returns corresponding
'values of column lngLookupCol if strSearch was found. All corr. values are collected and
'returned in one string (result of function).
Dim i As Long
If lngLookupCol > rngRange.Columns.Count Then
    vlookupall = CVErr(xlErrValue)
    Exit Function
End If
vlookupall = ""
For i = 1 To rngRange.Rows.Count
    If rngRange(i, 1).Text = strSearch Then
        vlookupall = vlookupall & rngRange(i, lngLookupCol).Text & "; "
    End If
Next i
End Function

Function lookup2(vSV As Variant, vSA As Variant, vRA As Variant) As Variant
'Similar to lookup() but it looks up the biggest value in vSA which is less-equal than vSV
'vSA has to be sorted, lowest first!!
'Remember that lookup() looks up the smallest value in the search-array which is
'greater-equal than search-value.
Dim i As Long
i = 1
Do While i <= vSA.Count
    If vSV <= vSA(i) Then
        lookup2 = vRA(i)
        Exit Function
    End If
    i = i + 1
Loop
lookup2 = "OUT OF RANGE"
End Function