Repeat VLOOKUP a variable number of times

[shawnw]

I am looking for a formula....

I have one worksheet which contains the following columns:

CHARTER HIERARCHY
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]Title[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Owner[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]Parent Title[/TD]
[TD="align: center"]Strategy[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]001[/TD]
[TD]Project[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD]Golf[/TD]
[TD]Strat 1[/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD]002[/TD]
[TD]Program[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD]Delta[/TD]
[TD]Strat 1[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]003[/TD]
[TD]Project[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD]Bravo[/TD]
[TD]Strat 2[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]004[/TD]
[TD]Charter[/TD]
[TD]Bill[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD]Strat 2[/TD]
[/TR]
[TR]
[TD]Echo[/TD]
[TD]005[/TD]
[TD]Project[/TD]
[TD]Mary[/TD]
[TD]Active[/TD]
[TD]Golf[/TD]
[TD]Strat 1[/TD]
[/TR]
[TR]
[TD]Foxtrot[/TD]
[TD]006[/TD]
[TD]Charter[/TD]
[TD]Mary[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD]Strat 3[/TD]
[/TR]
[TR]
[TD]Golf[/TD]
[TD]007[/TD]
[TD]Program[/TD]
[TD]Mary[/TD]
[TD]Active[/TD]
[TD]Foxtrot[/TD]
[TD]Strat 2[/TD]
[/TR]
</tbody>[/TABLE]

This worksheet pulls data from another system. It is essentially a flat file version of a relational database. As you can see, each project's parent is a program and each program's parent is a charter. Charters do not have parents.

• Alpha (project) rolls up into Golf (program) which rolls up into Foxtrot (charter).
• Charlie (project) rolls up into Bravo (program) which rolls up into Delta (charter).

I have a second worksheet which contains the following columns:

PROJECT DETAILS
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]Title[/TD]
[TD="align: center"]Charter Lookup[/TD]
[TD="align: center"]Capitalizable[/TD]
[TD="align: center"]In Service Date[/TD]
[/TR]
[TR]
[TD]Alpha[/TD]
[TD]formula goes here[/TD]
[TD]Yes[/TD]
[TD]Jan 1[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]formula goes here[/TD]
[TD]No[/TD]
[TD]Feb 1[/TD]
[/TR]
[TR]
[TD]Echo[/TD]
[TD]formula goes here[/TD]
[TD]Yes[/TD]
[TD]Jan 15[/TD]
[/TR]
</tbody>[/TABLE]

The "Title" column refers to the same data in both worksheets.

There is a legitimate reason that this data is in two separate worksheets, as opposed to just added "Capitalizable" and "In Service Date" to the CHARTER HIERARCHY worksheet.

In the Project Details worksheet there is a column called "Charter Lookup" (Col B). What I am trying to do is a VLOOKUP from the Charter Hierarchy worksheet to find the parent of the project. But I want to go all the way to the charter level, skipping projects and programs.

For example:
=VLookup("Alpha",CHARTER HIERARCHY, 6)

This should find "Golf" in the Charter Hierarchy worksheet. The problem is that Golf is not a charter. It's a program. So now I need to do another VLookup on the same table, like this:

=VLookup("Golf",CHARTER HIERARCHY, 6)

This should return "Foxtrot," which is a charter. So now I want the cell value in the Charter Lookup field to equate to "Foxtrot."

Basically, I need to keep polling the table until I find a result of type Charter. Then I need to return that result. If there is not result of type charter, then I can return "No Charter Found."

I hope this makes sense.

Thanks,

Shawn

 

[Nishant94]

If you are not flexible with a VBA Solution, then ignore this otherwise you can use this :

Press Alt+F11 and then press Alt+I+M to insert module and paste the following code:

Function doublelook(lookup As range, range_t As range, col_num As Integer) As String

    Dim chek As Boolean, x As String, y As String
    
    chek = False
    
    x = Application.WorksheetFunction.VLookup(lookup, range_t, col_num, 0)
    
    Do Until chek = True
    
    y = Application.WorksheetFunction.VLookup(x, range_t, 3, 0)
    
    If y = "Charter" Then
    
        chek = True
        doublelook = x
        
    Else
        
        y = Application.WorksheetFunction.VLookup(x, range_t, col_num, 0)
        x = y
        
    End If
    
    Loop


End Function

and then:

Unknown
	A	B	C	D	E	F	G
1	Title	ID	Type	Owner	Status	Parent Title	Strategy
2	Alpha	1	Project	Bill	Active	Golf	Strat 1
3	Bravo	2	Program	Bill	Active	Delta	Strat 1
4	Charlie	3	Project	Bill	Active	Bravo	Strat 2
5	Delta	4	Charter	Bill	Active		Strat 2
6	Echo	5	Project	Mary	Active	Golf	Strat 1
7	Foxtrot	6	Charter	Mary	Active		Strat 3
8	Golf	7	Program	Mary	Active	Foxtrot	Strat 2
9
10
11
12	Title	Charter Lookup	Capitalizable	In Service Date
13	Alpha	Foxtrot	Yes	Jan-01
14	Charlie	Delta	No	Feb-01
15	Echo	Foxtrot	Yes	Jan-15
16
17
Sheet2

Cell Formulas
Range	Formula
B13	=doublelook(A13,$A$2:$G$8,6)

Make sure you save the workbook as ".xlsm"

 

[shawnw]

It may require one lookup. Or two. Or more.

As per the title of the post, I have to keep looking a VARIABLE number of times until the Type field equals Charter or is null.

The XLS is at work. I can review the original post and make an update, if needed, on Monday. That's also when I can try any ideas that get posted here.

It seems people understand the goal. It's a formula based recursive search.

Thanks.

 

[AliGW]

I don't think it's going to be feasible with a formula. Is there a reason you can't use VBA?

 

[shawnw]

I guess VBA is fine if it fires automatically whenever the data changes.

 

[Nishant94]

Obviously it will update automatically if you follow the steps in post #11

 

[Peter_SSs]

It may require ... two ... Or more.

If more is possible then it would mean the following isn't strictly correct, or there are 'Types' other than just Project, Program and Charter, hence the pressing for confirmation about the possible number.

each project's parent is a program and each program's parent is a charter.

In any case, if there can be more than 2 or 3 lookups possible, then I agree that a standard formula approach is not feasible and would also suggest a user-defined function. My suggestion is shown below and should handle any number of lookups and also the situation you suggested could happen:

If there is not result of type charter, then I can return "No Charter Found."

To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Note that I have built the relevant columns from the 'CHARTER HIERARCHY' table (columns 1, 3 & 6) into the code. If they could ever be different, the 'Const' lines in the code could be altered or those columns could be passed to the function as extra arguments instead.

Function Charter(sTitle As String, rTable As Range) As String
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  Dim sTmp As String
  
  Const lTitleCol As Long = 1   '<-Column within data table containing Title
  Const lTypecol As Long = 3    '<-Column within data table containing Type
  Const lParentCol As Long = 6  '<-Column within data table containing Parent Title
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = rTable.Value
  For i = 1 To UBound(a)
    d(a(i, lTitleCol)) = a(i, lTypecol) & "|" & a(i, lParentCol)
  Next i
  Do Until Len(Charter) > 0
    If d.exists(sTitle) Then
      sTmp = Split(d(sTitle), "|")(1)
      If d.exists(sTmp) Then
        If Split(d(sTmp), "|")(0) <> "Charter" Then
          d(sTitle) = d(sTmp)
        Else
          Charter = sTmp
        End If
      Else
        Charter = "No Charter Found"
      End If
    Else
      Charter = "No Charter Found"
    End If
  Loop
End Function

Sample data table - I've added a few extras for different circumstances.

Excel Workbook
	A	B	C	D	E	F
1	Title	ID	Type	Owner	Status	Parent Title
2	Alpha	1	Project	Bill	Active	Golf
3	Bravo	2	Program	Bill	Active	Delta
4	Charlie	3	Project	Bill	Active	Bravo
5	Delta	4	Charter	Bill	Active
6	Echo	5	Project	Mary	Active	Golf
7	Foxtrot	6	Charter	Mary	Active
8	Golf	7	Program	Mary	Active	Foxtrot
9	Hotel	8	Program	Tom	Active	Kilo
10	India	9	Project	Tom	Active	Alpha
11	Juliet	10	Program	Tom	Active	Foxtrot
CHARTER HIERARCHY

Formula in B2 copied down. Column C added manually for info only.

Excel Workbook
	A	B	C
1	Title	Charter	No of Lookups
2	Alpha	Foxtrot	2
3	Charlie	Delta	2
4	Echo	Foxtrot	2
5	Hotel	No Charter Found	N/A
6	India	Foxtrot	3
7	Kilo	No Charter Found	N/A
8	Juliet	Foxtrot	1
PROJECT DETAILS

 

[shawnw]

I am convinced this does not require VBA and can be done in a formula. I am working through it, but it's getting too complicated for me. How can I upload an actual XLS? Thanks.

 

[shawnw]

I thought it might be helpful if showed the actual table structure rather than a simplified version. This mock-up shows a bunch of extra columns that I originally excluded. They're not relevant, so I filled them with dummy data here. The column arrangement is accurate.

To be clear, I need to calculate column M ("CharterID"). This is an alpha-numeric field, just like column a ("ID. In fact, the value in M must be a value in A.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]TYPE[/TD]
[TD]TITLE[/TD]
[TD]STATUS[/TD]
[TD]OWNER[/TD]
[TD]THEME[/TD]
[TD]PATH[/TD]
[TD]PARENT_NAME[/TD]
[TD]CAPFLAG[/TD]
[TD]SVCDATE[/TD]
[TD]COUNT[/TD]
[TD]PERCENT[/TD]
[TD]CHARTERID[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]E-01958[/TD]
[TD]Feature[/TD]
[TD]Apples[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Lemons[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Formula equates to E-01847[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]E-01837[/TD]
[TD]Feature[/TD]
[TD]Bananas[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD][/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Formula equates to Null[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]E-01847[/TD]
[TD]Charter[/TD]
[TD]Grapes[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD][/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Formula equates to E-01847[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]E-01865[/TD]
[TD]Project[/TD]
[TD]Lemons[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Limes[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Formula equates to E-01847[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]E-01415[/TD]
[TD]Program[/TD]
[TD]Limes[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Pineapples[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Formula equates to E-01847[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]E-01168[/TD]
[TD][/TD]
[TD]Pears[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Melons[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Formula equates to E-01951[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]E-01954[/TD]
[TD]Feature[/TD]
[TD]Kiwis[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Lemons[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Formula equates to E-01847[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]E-01951[/TD]
[TD]Charter[/TD]
[TD]Melons[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD][/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Formula equates to E-01951[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]E-01901[/TD]
[TD]Program[/TD]
[TD]Pineapples[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Grapes[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]dummy[/TD]
[TD]Formula equates to E-01847[/TD]
[/TR]
</tbody>[/TABLE]

Basically, I need to query row by row, as follows:

Check the value in Col B ("Type").
If Type = "Charter," then [CharterID] = [ID]. Examples are in rows 4 and 9.
If Type = null, then CharterID = null. Example is in row 7.
If Type = Feature or Project (or really anything else) then
Get the Parent_Name in Col H. This is a text field.
Find the row where that Parent_Name (from current Row N) matches the Title in Col C.
Return the ID (Col A) of that other row.
Now check that ID to see if it is a type Charter.
If Yes, then set the Charter ID ion Row N to the ID of the matching row.
For example:
in row 2 the Type = Feature. So I look at the Parent_Name and find "Lemons."
I then look for "Lemons" in Col C and find it in row 5.
The Type value of row 5 is "Project," so it is not a charter. I need to keep looking.
I now check the Parent_Name in Row 5 and find "Limes."
I then look for "Limes" in Col C and find it in row 6.
The Type value of row 6 is "Program," so it is not a charter. I need to keep looking.
I now check the Parent_Name in Row 6 and find "Pineapples."
I then look for "Pineapples" in Col C and find it in row 10. This is still not a charter.
I now check the Parent_Name in Row 10 and find "Grapes."
I then look for "Grapes" in Col C and find it in row 4. This is a charter.
So Lemons (a project in row 2) sent me to Limes (a program in row 6) sent me to Pineapples (a program in row 10) sent me to Grapes (a charter in row 4)
The ID for grapes is E-01847, so this value becomes the CharterID in col M.

Another example may take fewer look-ups or may include other types, such as feature.
The point is that I have to keep checking the table until I find a charter or... importantly... until I find a null in either the Type field or the Parent_Name field.

Thanks!

 

[Peter_SSs]

I am convinced this does not require VBA and can be done in a formula.

A formula cannot "indefinitely loop until result found". To do a series of VLOOKUPs for example, the VLOOKUPs would need to be nested. So if 2 lookups might be required, you just need 2 nested lookups. If you might need 20 lookups to get to a result the you would need 20 nested lookups. If you might need 1000 ...
Hence my earlier question about there possibly being more than 2 lookups. Your response was

It may require one lookup. Or two. Or more.

Unfortunately, "more" is undefined - it could be 3 or 20 or 1000 etc. Clearly we cannot nest a huge number of lookups in a formula.

Can you put an absolute upper limit on how many successive lookups could be required to get to a result?

I'm also confused about this from your previous post.

If Type = null, then CharterID = null. Example is in row 7.

Did you really mean this?

If Parent_Name = null, then CharterID = null. Example is in row 3.

 

[shawnw]

Peter -

First, thanks. I appreciate your time and help.

No upper limit. Upon reflection, you are right - a function is best. I will never be 100% sure of the number of lookups. I did try to modify your earlier function, but my edits brought Excel to a crawl and then crashed the app.

I don't know why my posts are so sloppy. Exhaustion, I guess. This should help...

The lookup process stops under these conditions:

1. Type = "Charter"
   • This is success. The function should return the ID.
2. Type = NULL
   • This is a failure, because we did not find the charter and we have no data to re-query the table with.
   • Function returns: "Error - Null Type" (so I can flag with conditional formatting).
3. Type <> Charter and Parent_Name = NULL.
   • This is also a failure, because we did not find the charter and we have no data to re-query the table with.
   • Function returns: "Error - Null Parent" (so I can flag with conditional formatting).
4. It is unlikely, but possible that there is more than one match on Parent_Name. basically, we find a row where Type <> Charter, so we need to lookup by Parent_Name. If we find more than one match, we actually can't be sure we found the right one. In this case, I guess it would be ideal to return "Error - Multiple Matches."

One last thing to stress is that this table of data may be sorted or re-sorted any number of ways. I have had bad results with VLookup with the source table gets sorted. That's why I tried to use INDEX and MATCH instead. That's when Excel started crashing.

Thanks!