Solved

Recursive Lookup in excel

Posted on 2014-03-11
4
325 Views
Last Modified: 2014-03-26
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
Comment
Question by:CalmSoul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 13

Expert Comment

by:Santosh Gupta
ID: 39921540
pls explain more about "Lookup TABLE", output and type table/sets.
0
 
LVL 5

Author Comment

by:CalmSoul
ID: 39921552
example attached
0
 
LVL 13

Expert Comment

by:Santosh Gupta
ID: 39921553
pls explain the logic
0
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 39921878
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question