Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

Recursive Lookup in excel

I would like to find all values from the lookup and split them by "OR" if there is more than one .. sample attached
Recursive-Lookup---Sheet1.csv
0
CalmSoul
Asked:
CalmSoul
  • 2
1 Solution
 
Santosh GuptaCommented:
pls explain more about "Lookup TABLE", output and type table/sets.
0
 
CalmSoulAuthor Commented:
example attached
0
 
Santosh GuptaCommented:
pls explain the logic
0
 
Ejgil HedegaardCommented:
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

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)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now