On Tue, 10 Nov 2009 10:17:02 -0800, KBrown
wrote:

I need help with a formula...

I have two columns of data and I need to figure out a way to have Excel
figure out the third column. The data is being imported into a database and
will be creating a tree structure.

The two columns I have are Level (indicates which level in the tree
structure this record needs to be at) and number (a unique number for this
record).

I need a formula (if there is one) that will determine the Parent Record.
So in the example below, I have filled out the Parent Code for several of the
records. The parent for "3: M3.A.1" is "3: M3.A". Is there a way to have a
lookup or vlookup do this work for me?

Level Number Parent Code
1 3: M3.A
2 3: M3.A.1 3: M3.A
3 3: M3.A.1.1 3: M3.A.1
4 3: M3.A.1.1.1 3: M3.A.1.1
4 3: M3.A.1.1.2 3: M3.A.1.1
4 3: M3.A.1.1.3 3: M3.A.1.1
4 3: M3.A.1.1.4 3: M3.A.1.1
4 3: M3.A.1.1.5 3: M3.A.1.1
3 3: M3.A.1.2
4 3: M3.A.1.2.1
4 3: M3.A.1.2.2
3 3: M3.A.1.3
4 3: M3.A.1.3.1
4 3: M3.A.1.3.2
4 3: M3.A.1.3.3
2 3: M3.A.2
3 3: M3.A.2.1
4 3: M3.A.2.1.1
4 3: M3.A.2.1.2
4 3: M3.A.2.1.3


Try the following formula in cell C3:

=INDEX(B$1:B3,MAX((A$1:A2<A3)*(ROW(A$1:A2))))

Note: This is an array formula that must be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Copy down as far as needed.

Hope this helps / Lars-Åke