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

 

[AlphaFrog]

Try something like this...

=VLOOKUP(VLOOKUP(A2,'CHARTER HIERARCHY'!A:F,6,0),'CHARTER HIERARCHY'!A:F,6,0)

Based on your description and example, it seems you always want to do two lookups as opposed a variable number of times.

 

[FDibbins]

How on earth do you get that logic?
lol

Perhaps if you used the same terms in your description as in your table, it would help?

 

[shawnw]

I did use consistent terms. I guess you found it too confusing. I'm querying the same table N times (working my way up the hierarchy) until I retrieve a record where Type = Charter.

 

[AliGW]

On the lookup table, Golf is listed as a program in column C, not a charter, so I don't see how that relates to charter.

 

[shawnw]

But you apparently understand my intention.

 

[AliGW]

I think so, but because the data in the lookup table doesn't seem to match what you say you are doing (i.e. finding a charter entry for Golf), I am questioning my understanding. Can you see why?

 

[shawnw]

Yes. So I guess I'm asking you to overlook what you recognized as a typo and see if you can help nonetheless. I offer one thousand apologies for the mistake.

 

[AliGW]

OK, so just to be absolutely clear, would you please post the lookup table as it should appear?

Have you tried AlphaFrog's suggestion? Does it work for you?

 

[Peter_SSs]

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

The above and your two examples seem to agree with AlphaFrog's interpretation that each time you would need two lookups. However, the following indicates to me that there might be a varying number of lookups required - perhaps more or less than two.

I'm querying the same table N times (working my way up the hierarchy) until I retrieve a record where Type = Charter.

Can you confirm whether or not there could be more or less than two lookups required and if so provide an example of such situation(s)?

If AlphaFrog's suggestion does not work for you (apart from the "No Charter Found" situation), again could you provide example(s)?