VBA - 2 lookup range with 2 conditions

[davidhasselhoff]

Hello!

I'm using the following function to lookup a value based on two conditions. It works perfectly if I try to find only one value. However, I would like it to give me a range of corresponding values if there are more than one.

Does anybody here know how I could do so?
I didn't invent this code and hardly understand it, so I can't imagine a way to do this myself.

Public Function BLOOKUP(first_lookup_value As Variant, _
    first_col As Range, _
    second_lookup_value As Variant, _
    second_col As Range, _
    return_col As Range, _
    Optional NA_value As Variant = CVErr(xlErrNA)) As Variant
     
     'Like  Vlookup, but can take two input values
     
    Application.Volatile (False)
    Dim CriteriaOffset As Long
    Dim ReutrnOffset As Long
    Dim cell As Range
     
    If first_col.Parent Is second_col.Parent = False Then Exit Function
    If first_col.Parent Is return_col.Parent = False Then Exit Function
     
    BLOOKUP = NA_value
     
    Set first_col = Intersect(first_col, first_col.Parent.UsedRange)
    If first_col Is Nothing Then Exit Function
     
    Set second_col = Intersect(second_col, second_col.Parent.UsedRange)
    If second_col Is Nothing Then Exit Function
     
    CriteriaOffset = second_col.Column - first_col.Column
    ReutrnOffset = return_col.Column - first_col.Column
     
    For Each cell In Union(first_col.Cells(1), first_col.Columns(1))
        If UCase(cell) = UCase(first_lookup_value) Then
            If UCase(cell.Offset(0, CriteriaOffset)) = UCase(second_lookup_value) Then
                BLOOKUP = cell.Offset(0, ReutrnOffset)
                Exit For
            End If
        End If
    Next cell
     
End Function

Any help would be greatly appreciated!

Marc

 

[davidhasselhoff]

how do i delete this? i posted it twice