Recursive Lookup in excel

Put this in a VBA module

Option Explicit

Function RecursiveLookup(Id As Range, Table As Range)
    Application.Volatile
    Dim i As Long, arTable As Variant
    arTable = Table
    For i = LBound(arTable, 1) To UBound(arTable, 1)
        If arTable(i, 1) = Id Then
            If Len(RecursiveLookup) = 0 Then
                RecursiveLookup = arTable(i, 2)
            Else
                RecursiveLookup = RecursiveLookup + " OR " + arTable(i, 2)
            End If
        End If
    Next i
    If Len(RecursiveLookup) = 0 Then
        RecursiveLookup = "NOT FOUND***"
    End If
End Function

Select all Open in new window

Address the function like this for cell B2, ID in A2, and table in G2:H13

=RecursiveLookup(A2,$G$2:$H$13)

Select all Open in new window