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

A	B	C	D	E	F	G
Title	ID	Type	Owner	Status	Parent Title	Strategy
Alpha	001	Project	Bill	Active	Golf	Strat 1
Bravo	002	Program	Bill	Active	Delta	Strat 1
Charlie	003	Project	Bill	Active	Bravo	Strat 2
Delta	004	Charter	Bill	Active		Strat 2
Echo	005	Project	Mary	Active	Golf	Strat 1
Foxtrot	006	Charter	Mary	Active		Strat 3
Golf	007	Program	Mary	Active	Foxtrot	Strat 2

<tbody>
</tbody>

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

A	B	C	D
Title	Charter Lookup	Capitalizable	In Service Date
Alpha	formula goes here	Yes	Jan 1
Charlie	formula goes here	No	Feb 1
Echo	formula goes here	Yes	Jan 15

<tbody>
</tbody>

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

 

[Peter_SSs]

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?

 

[shawnw]

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.

	A	B	C	H	M
1	ID	TYPE	TITLE	PARENT_NAME	CHARTERID
2	E-01958	Feature	Apples	Lemons	E-01847
3	E-01837	Feature	Bananas		Error - Orphaned Feature
4	E-01847	Charter	Grapes		E-01847
5	E-01865	Project	Lemons	Limes	E-01847
6	E-01415	Program	Limes	Pineapples	E-01847
7	E-01168		Pears	Melons	E-01951
8	E-01954	Feature	Kiwis	Lemons	E-01847
9	E-01951	Charter	Melons		E-01951
10	E-01901	Program	Pineapples	Grapes	E-01847
11	E-09123	Project	Raspberries		Error - Orphaned Project
12	E-98123		Strawberries		Error - Orphaned NA
13	E-01872	Program	Tomatoes		Error - Orphaned Program

<tbody>
</tbody>

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!

 

[shawnw]

I really need help on this! I just can't seem to write a function that doesn't cause Excel to crash.

 

[Peter_SSs]

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
	A	B	C	H	M
1	ID	TYPE	TITLE	PARENT_NAME	CHARTERID
11	E-Test1	Project	Blueberries	Lychees
12	E-Test2	Project	Lychees	Blueberries
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
	A	B	C	H	M
1	ID	TYPE	TITLE	PARENT_NAME	CHARTERID
2	E-Test3	Feature	Strawberries	Oranges
3	E-Test4	Program	Mandarins	Grapefruit
4	E-Test5	Project	Oranges	Mandarins
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.

 

[shawnw]

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.

 

[Peter_SSs]

Try this.

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
	A	B	C	H	M
1	ID	TYPE	TITLE	PARENT_NAME	CHARTER
2	E-01958	Feature	Apples	Lemons	E-01847
3	E-01837	Feature	Bananas		Error - Orphaned Feature
4	E-01847	Charter	Grapes		E-01847
5	E-01865	Project	Lemons	Limes	E-01847
6	E-01415	Program	Limes	Pineapples	E-01847
7	E-01168		Pears	Melons	E-01951
8	E-01954	Feature	Kiwis	Lemons	E-01847
9	E-01951	Charter	Melons		E-01951
10	E-01901	Program	Pineapples	Grapes	E-01847
11	E-09123	Project	Raspberries		Error - Orphaned Project
12	E-98123		Strawberries		Error - Orphaned NA
13	E-01872	Program	Tomatoes		Error - Orphaned Program
14	E-Test3	Feature	Starfruit	Oranges	Error - Orphaned Feature
15	E-Test4	Program	Mandarins	Grapefruit	Error - Orphaned Program
16	E-Test5	Project	Oranges	Mandarins	Error - Orphaned Project
CHARTER HIERARCHY

Cell Formulas
Range	Formula
M2	=Charter(C2,$A$2:$H$16)

 

[shawnw]

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.

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

	A	B	C	H	M
1	ID	TYPE	TITLE	PARENT_NAME	CHARTER
2	E-01958	Feature	Apples	Lemons	E-01847
3	E-01837	Feature	Bananas		Error - Orphaned Feature
4	E-01847	Charter	Grapes		E-01847
5	E-01865	Project	Lemons	Limes	E-01847
6	E-01415	Program	Limes	Pineapples	E-01847
7	E-01168		Pears	Melons	E-01951
8	E-01954	Feature	Kiwis	Lemons	E-01847
9	E-01951	Charter	Melons		E-01951
10	E-01901	Program	Pineapples	Grapes	E-01847
11	E-09123	Project	Raspberries		Error - Orphaned Project
12	E-98123		Strawberries		Error - Orphaned NA
13	E-01872	Program	Tomatoes		Error - Orphaned Program
14	E-Test3	Feature	Starfruit	Oranges	Error - Orphaned Feature
15	E-Test4	Program	Mandarins	Grapefruit	Error - Orphaned Program
16	E-Test5	Project	Oranges	Mandarins	Error - Orphaned Project

<colgroup><col style="******* 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

CHARTER HIERARCHY​

Worksheet Formulas Cell Formula M2 =Charter(C2,$A$2:$H$16) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Peter_SSs]

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

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.

 

[shawnw]

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!

 

[Peter_SSs]

.. 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.