Repeat VLOOKUP a variable number of times

shawnw

New Member
Joined
Apr 6, 2016
Messages
34
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
 
Need to check a couple of things.

2. in your last post seems to contradict row 7 of post #18. Which is correct and why?

4. in your last post. Could you give a small example of data (& results) that demonstrates that situation?
 
Last edited:
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Ignore all previous sample data and error messages. The table below shows every possibility and function result with psuedo code below.

I am hiding all columns with dummy data (D, E, F, G, I, J, K, L) to make the table easier to follow.

[TABLE="width: 447"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]TYPE[/TD]
[TD]TITLE[/TD]
[TD]PARENT_NAME[/TD]
[TD]CHARTERID[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]E-01958[/TD]
[TD]Feature[/TD]
[TD]Apples[/TD]
[TD]Lemons[/TD]
[TD]E-01847[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]E-01837[/TD]
[TD]Feature[/TD]
[TD]Bananas[/TD]
[TD][/TD]
[TD]Error - Orphaned Feature[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]E-01847[/TD]
[TD]Charter[/TD]
[TD]Grapes[/TD]
[TD][/TD]
[TD]E-01847[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]E-01865[/TD]
[TD]Project[/TD]
[TD]Lemons[/TD]
[TD]Limes[/TD]
[TD]E-01847[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]E-01415[/TD]
[TD]Program[/TD]
[TD]Limes[/TD]
[TD]Pineapples[/TD]
[TD]E-01847[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]E-01168[/TD]
[TD][/TD]
[TD]Pears[/TD]
[TD]Melons[/TD]
[TD]E-01951[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]E-01954[/TD]
[TD]Feature[/TD]
[TD]Kiwis[/TD]
[TD]Lemons[/TD]
[TD]E-01847[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]E-01951[/TD]
[TD]Charter[/TD]
[TD]Melons[/TD]
[TD][/TD]
[TD]E-01951[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]E-01901[/TD]
[TD]Program[/TD]
[TD]Pineapples[/TD]
[TD]Grapes[/TD]
[TD]E-01847[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]E-09123[/TD]
[TD]Project[/TD]
[TD]Raspberries[/TD]
[TD][/TD]
[TD]Error - Orphaned Project[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]E-98123[/TD]
[TD][/TD]
[TD]Strawberries[/TD]
[TD][/TD]
[TD]Error - Orphaned NA[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]E-01872[/TD]
[TD]Program[/TD]
[TD]Tomatoes[/TD]
[TD][/TD]
[TD]Error - Orphaned Program[/TD]
[/TR]
</tbody>[/TABLE]


Explanation of Function Value (Col M):


  1. Headers
  2. Row 2 (Apples)
    • Type for Apples is Feature, not Charter, so find parent.
    • Parent for Apples is Lemons.
    • Type for Lemons is Project, not Charter, so find parent.
    • Parent for Lemons is Limes.
    • Type for Limes is Program, not Charter, so find parent.
    • Parent for Limes is Pineapples.
    • Type for Pineapples is Program, not Charter, so find parent.
    • Parent for Pineapples is Grapes.
    • Type for Grapes is Charter. Success!
    • Function returns E-01847 (ID for Grapes) to M2.
  3. Row 3 (Bananas)
    • Type for Bananas is Feature, not Charter, so find parent.
    • Parent for Bananas is null, so we cannot continue the search.
    • Function returns "Error - Orphaned Feature" to M3.
    • Note: The error here is really "Error - Orphaned " & IFNA([Type],"NA")
  4. Row 4 (Grapes)
    • Type for Grapes is Charter. Success!
    • Function returns E-01847 (ID for Grapes) to M4.
  5. Row 5 (Lemons)
    • Same logic path as R2
    • Function returns E-01847 (ID for Grapes) to M5.
  6. Row 6 (Limes)
    • Same logic path as R2
    • Function returns E-01847 (ID for Grapes) to M6.
  7. Row 7 (Pears)
    • Type for Pears is Program, not Charter, so find parent.
    • Parent for Pears is Melons
    • Type for Melons is Charter. Success!
    • Function returns E-01951 (ID for Melons) to M7.
  8. Row 8 (Kiwis)
    • Same logic path as R2
    • Function returns E-01847 (ID for Grapes) to M8.
  9. Row 9 (Melons)
    • Type for Melons is Charter. Success!
    • Function returns E-01951 (ID for Grapes) to M9.
  10. Row 10 (Pineapples)
    • Same logic path as R2
    • Function returns E-01847 (ID for Grapes) to M10.
  11. Row 11 (Raspberries)
    • Type for Raspberries is Project, not Charter, so find parent.
    • Parent for Raspberries is null, so we cannot continue the search.
    • Function returns "Error - Orphaned Project" to M11.
  12. Row 12 (Strawberries)
    • Type for Strawberries is null not Charter, so find parent.
    • Parent for Strawberries is null, so we cannot continue the search.
    • Function returns "Error - Orphaned NA" to M12.
  13. Row 13 (Tomatoes)
    • Type for Tomatoes is Program, not Charter, so find parent.
    • Parent for Tomatoes is null, so we cannot continue the search.
    • Function returns "Error - Orphaned program" to M13.

Thank you again for your patience and our thoughtful (and thought-provoking) help. It is sincerely appreciated.

Thanks!
 
Upvote 0
I really need help on this! I just can't seem to write a function that doesn't cause Excel to crash.
 
Upvote 0
The table below shows every possibility ...
Before I spend time considering any code, I would like to test the above statement. I can think of other possibilities and I would like you to either ..

- Confirm that these situations are impossible with your sheet, or

- Explain what result these circumstances should bring if they are possible.


Example 1 (Infinite loop)
Could this occur in your table, and if so, what should go in each column M cell?


Excel 2010 32 bit
ABCHM
1IDTYPETITLEPARENT_NAMECHARTERID
11E-Test1ProjectBlueberriesLychees
12E-Test2ProjectLycheesBlueberries
Test



Example 2 (No Charter and no orphaned item)
Suppose the whole table only had these three rows. There is no Charter result and no orphaned items (at least as I understand your orphaned items)
Is this possible and if so what goes in column M?


Excel 2010 32 bit
ABCHM
1IDTYPETITLEPARENT_NAMECHARTERID
2E-Test3FeatureStrawberriesOranges
3E-Test4ProgramMandarinsGrapefruit
4E-Test5ProjectOrangesMandarins
Test



BTW, I will be away for a few days shortly and may not have access to Excel or the forum. If not resolved beforehand, I will look again after my return.
 
Upvote 0
Great questions.

Example 1
An infinite loop is not possible, because the external database that produces this file does not allow that. It is not necessary to consider this scenario, although I imagine there is a way to determine whether a particular ID has already been checked and, if so, produce an error. I wouldn't bother.

Example 2
Yes, this is possible. In this case, all rows (E-Test3, E-Test4, and E-Test5) would all return "Error - Orphan " & [Type]

Thanks for your help.
 
Upvote 0
Try this.
Rich (BB code):
Function Charter(sTitle As String, rTable As Range) As String
  Dim d1 As Object, d2 As Object
  Dim a As Variant
  Dim i As Long
  Dim sTmp As String, sLast As String
  
  Const lIDCol As Long = 1      '<-Column within data table containing ID
  Const lTypecol As Long = 2    '<-Column within data table containing Type
  Const lTitleCol As Long = 3   '<-Column within data table containing Title
  Const lParentCol As Long = 8  '<-Column within data table containing Parent Name
  Const MaxLoops As Long = 50   '<-Safety to ensure infinite loop doesn't occur
  
  Set d1 = CreateObject("Scripting.Dictionary")
  d1.CompareMode = 1
  Set d2 = CreateObject("Scripting.Dictionary")
  d2.CompareMode = 1
  a = rTable.Value
  For i = 1 To UBound(a)
    d1(a(i, lTitleCol)) = a(i, lIDCol) & "|" & IIf(a(i, lTypecol) = Empty, "NA", a(i, lTypecol))
    d2(a(i, lTitleCol)) = a(i, lParentCol)
  Next i
  sTmp = sTitle
  Do
    i = i + 1
    sLast = sTmp
    sTmp = d2(sTmp)
  Loop While d2.exists(sTmp) And i < MaxLoops
  If i = MaxLoops Or Split(d1(sLast), "|")(1) <> "Charter" Then
    Charter = "Error - Orphaned " & Split(d1(sTitle), "|")(1)
  Else
    Charter = Split(d1(sLast), "|")(0)
  End If
End Function


Excel 2010 32 bit
ABCHM
1IDTYPETITLEPARENT_NAMECHARTER
2E-01958FeatureApplesLemonsE-01847
3E-01837FeatureBananasError - Orphaned Feature
4E-01847CharterGrapesE-01847
5E-01865ProjectLemonsLimesE-01847
6E-01415ProgramLimesPineapplesE-01847
7E-01168PearsMelonsE-01951
8E-01954FeatureKiwisLemonsE-01847
9E-01951CharterMelonsE-01951
10E-01901ProgramPineapplesGrapesE-01847
11E-09123ProjectRaspberriesError - Orphaned Project
12E-98123StrawberriesError - Orphaned NA
13E-01872ProgramTomatoesError - Orphaned Program
14E-Test3FeatureStarfruitOrangesError - Orphaned Feature
15E-Test4ProgramMandarinsGrapefruitError - Orphaned Program
16E-Test5ProjectOrangesMandarinsError - Orphaned Project
CHARTER HIERARCHY
Cell Formulas
RangeFormula
M2=Charter(C2,$A$2:$H$16)
 
Last edited:
Upvote 0
Peter,

I hope you enjoyed the long weekend.

Unfortunately, this does not work. It returns all zeroes ad then throws a circular reference warning. In "watching" the objects, I see the expression "a" always has a null value, which I guess means rTable.Value always has a null value.

Is there a way I can share the actual XLS?

Thanks


Try this.
Rich (BB code):
Function Charter(sTitle As String, rTable As Range) As String
  Dim d1 As Object, d2 As Object
  Dim a As Variant
  Dim i As Long
  Dim sTmp As String, sLast As String
  
  Const lIDCol As Long = 1      '<-Column within data table containing ID
  Const lTypecol As Long = 2    '<-Column within data table containing Type
  Const lTitleCol As Long = 3   '<-Column within data table containing Title
  Const lParentCol As Long = 8  '<-Column within data table containing Parent Name
  Const MaxLoops As Long = 50   '<-Safety to ensure infinite loop doesn't occur
  
  Set d1 = CreateObject("Scripting.Dictionary")
  d1.CompareMode = 1
  Set d2 = CreateObject("Scripting.Dictionary")
  d2.CompareMode = 1
  a = rTable.Value
  For i = 1 To UBound(a)
    d1(a(i, lTitleCol)) = a(i, lIDCol) & "|" & IIf(a(i, lTypecol) = Empty, "NA", a(i, lTypecol))
    d2(a(i, lTitleCol)) = a(i, lParentCol)
  Next i
  sTmp = sTitle
  Do
    i = i + 1
    sLast = sTmp
    sTmp = d2(sTmp)
  Loop While d2.exists(sTmp) And i < MaxLoops
  If i = MaxLoops Or Split(d1(sLast), "|")(1) <> "Charter" Then
    Charter = "Error - Orphaned " & Split(d1(sTitle), "|")(1)
  Else
    Charter = Split(d1(sLast), "|")(0)
  End If
End Function

Excel 2010 32 bit
ABCHM
IDTYPETITLEPARENT_NAMECHARTER
E-01958FeatureApplesLemonsE-01847
E-01837FeatureBananasError - Orphaned Feature
E-01847CharterGrapesE-01847
E-01865ProjectLemonsLimesE-01847
E-01415ProgramLimesPineapplesE-01847
E-01168PearsMelonsE-01951
E-01954FeatureKiwisLemonsE-01847
E-01951CharterMelonsE-01951
E-01901ProgramPineapplesGrapesE-01847
E-09123ProjectRaspberriesError - Orphaned Project
E-98123StrawberriesError - Orphaned NA
E-01872ProgramTomatoesError - Orphaned Program
E-Test3FeatureStarfruitOrangesError - Orphaned Feature
E-Test4ProgramMandarinsGrapefruitError - Orphaned Program
E-Test5ProjectOrangesMandarinsError - Orphaned Project

<colgroup><col style="******* 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

</tbody>
CHARTER HIERARCHY

[TABLE="******* 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="******* 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="******* 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="******* 10px, bgcolor: #DAE7F5"]M2[/TH]
[TD="align: left"]=Charter(C2,$A$2:$H$16)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Firstly, it is better not to quote whole longish posts. If you need to quote at all, just quote relevant short bits of it. The long quotes make the post and thread harder to read & navigate.

If a(..,..) is holding a null value when your table has 'proper' values, then you are probably not using the combination of code and formula correctly. You can see from what I posted that the results are being produced.
For the sample I used, my formula in the first cell was

=Charter(C2,$A$2:$H$16)

The C2 points to the particular TITLE of interest.

The $A$2:$H$16 needs to point to the range that holds the data from the top left ID number to the bottom right PARENT_NAME. For my sample, that is a table containing 8 columns (A-H).
Of those columns, only 4 are relevant to the calculation and those relevant columns are the 1st, 2nd, 3rd and 8th columns. The relevant columns are identified in the code by the following code lines

Rich (BB code):
Const lIDCol As Long = 1      '<-Column within data table containing ID
Const lTypecol As Long = 2    '<-Column within data table containing Type
Const lTitleCol As Long = 3   '<-Column within data table containing Title
Const lParentCol As Long = 8  '<-Column within data table containing Parent Name

Have you made any relevant adjustments to suit your data and layout?

If you are still having trouble, then the next thing I would try is to set up a sheet with identical data and layout to post #26 and see if you can re-produce the results there. You haven't said anything about those results so I am assuming that they are correct for that data.

It is possible to upload a sample file to a file-share site and provide a link here in your thread but in general I would prefer to try to solve the problem without that.
 
Upvote 0
Thank you for the tip.

I got it to work (partially) by removing references to named tables. there are sporadic errors, including the last row.

I appreciate the broad benefit to avoiding attachments, but using a dummy data table is proving problematic. Here is the real data:

https://drive.google.com/open?id=0B7CDe77cNqwNTEx4ZTNNQ1RtQlU

I know you have invested time in this and may be getting frustrated. I have also invested time and may be getting fired if I can't figure it out! Please... take one more look.

Thank you!
 
Upvote 0
.. errors, including the last row.
The error in the last row is because you are referencing an item on row 393 when the reference range only goes to 392

=Charter(C393,$A$2:$H$392)


One of the reasons that I don't much like looking at actual files, is that they are not familiar to me and often do not reflect what has been given in the thread. This is a typical example of that.
The first 3 column titles are like your sample column titles. There is no title that even remotely resembles the sample column H title so I don't know if your column H is the relevant one to be considering.

Assuming it is, then there is a further issue in that in your sample, items in column H only ever appear once in column C. Example, Lemons appears multiple times in column H but only once in column C so when looking it up in column C there is no confusion. In your actual data "OnBoarding of Confirmations to Salesforce", as just one example, is in column H but occurs twice in column C, each with different 'ID', different 'Type' and different 'column H'. How would we know which one to use?

The summary is that as far as I can see, your actual data is not too much like your sample, so probably not surprising that the function is not performing as expected.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top