How to find matching level 2 asset for each level using a Parent/child relationship/structure

[broken keyboard]

Hi all,

I am trying to locate the "level 2" asset of each asset number in the list in Column A. A parent/child relationship is shown in Columns F & G. Not sure what the best formula to use here would be, but maybe an array? Not every asset in the hierarchy has an immediate next-level up asset associated. So, for example, an asset at level 7 may have a parent at level 3, then the level 3 asset would have a parent at level 2. The asset at level 2 is the asset I need to return. Any help on the below is much appreciated

Asset Hierarchy Structure.xlsx
	A	B	C	D	E	F	G
1	Asset Number:	Hierarchy Level:	What is the Level 2 asset for this particular asset?			Parent	Child
2	765565	7				3456	347454
3	34535243	7				347454	4745374
4	2323256	7				4745374	747434
5	3495	7				747434	353473
6	94157	7				353473	23523
7	97135	7				353473	235346
8	53398	7				353473	765565
9	23523	6				85654	436346
10	235346	6				436346	34535243
11	78333	6				68343	236224
12	353473	5				236224	78333
13	436346	5				78333	2323256
14	5742	5				8523	86329
15	747434	4				86329	9871
16	236224	4				9871	3495
17	4745374	3				9871	5742
18	9871	3				5742	94157
19	347454	2				9871	97135
20	85654	2				5742	53398
21	68343	2
22	86329	2
23	3456	1
24	8523	1
Asset Hierarchy

 

[KRice]

This isn't pretty, but I think it works. This approach uses help columns H:AC (or AE if you want to shorten a formula) to examine every parent-child relationship and build the multigenerational lineup on a single row. So columns H:L begin with the question "Does the Parent appear as a Child, and if so, what is its Parent?" to go in one direction, with each column to the right referring to its neighbor at left. The formula to do that is pulled to the right until only blanks are generated for all of the rows. Columns M:Q ask a similar question, "Does the Child appear as Parent, and if so, what is it's Child?" to go in the other direction. Again, that formula is pulled to the right until only blanks are generated. Then columns R:AC perform a look up using columns A:B to create a hierarchy mapping for every item in the parent-child multigenerational table. Finally, we return to column A, and for every asset number, we search the multigeneration table (F3:Q21) to find that asset, determine what row in the table it is found (any row where it is found should be fine, but the first is taken), and then we look across that row to find which asset in its family line corresponds to hierarchy level "2". I've separately tabulated the row and column index numbers within F3:Q21 where this occurs, as shown in columns AD:AE, but those same formulas are incorporated in the INDEX formula in column C. If you prefer, the column C formula could be shortened substantially by simply referring to the value in columns AD and AE.

MrExcel20210424.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1						1	2	3	4	5	6	7	8	9	10	11	12	1	2	3	4	5	6	7	8	9	10	11	12
2	Asset Number:	Hierarchy Level:	What is the Level 2 asset for this particular asset?			Parent	Child	Does Parent appear as Child? If so, what is it's Parent?	and again	and again	and again	and again	Does Child appear as Parent? If so, what is it's Child?	and again	and again	and again	and again	Hierarchy Level of Parent	Hierarchy Level of Child											row	col
3	765565	7	347454			3456	347454						4745374	747434	353473	23523		1	2						3	4	5	6		7	5
4	34535243	7	85654			347454	4745374	3456					747434	353473	23523			2	3	1					4	5	6			8	1
5	2323256	7	68343			4745374	747434	347454	3456				353473	23523				3	4	2	1				5	6				10	1
6	3495	7	86329			747434	353473	4745374	347454	3456			23523					4	5	3	2	1			6					13	2
7	94157	7	86329			353473	23523	747434	4745374	347454	3456							5	6	4	3	2	1							16	3
8	97135	7	86329			353473	235346	747434	4745374	347454	3456							5	6	4	3	2	1							18	3
9	53398	7	86329			353473	765565	747434	4745374	347454	3456							5	7	4	3	2	1							19	4
10	23523	6	347454			85654	436346						34535243					2	5						7					1	2
11	235346	6	347454			436346	34535243	85654										5	7	2										6	5
12	78333	6	68343			68343	236224						78333	2E+06				2	4						6	7				10	1
13	353473	5	347454			236224	78333	68343					2323256					4	6	2					7					1	2
14	436346	5	85654			78333	2323256	236224	68343									6	7	4	2									8	1
15	5742	5	86329			8523	86329						9871	3495				1	2						3	7				16	3
16	747434	4	347454			86329	9871	8523					3495					2	3	1					7					1	2
17	236224	4	68343			9871	3495	86329	8523									3	7	2	1									10	1
18	4745374	3	347454			9871	5742	86329	8523				94157					3	5	2	1				7					1	2
19	9871	3	86329			5742	94157	9871	86329	8523								5	7	3	2	1								13	2
20	347454	2	347454			9871	97135	86329	8523									3	7	2	1									1	2
21	85654	2	85654			5742	53398	9871	86329	8523								5	7	3	2	1								8	1
22	68343	2	68343																											10	1
23	86329	2	86329																											13	2
24	3456	1	347454																											1	2
25	8523	1	86329																											13	2
Sheet1

Cell Formulas
Range	Formula
H3:H21	H3	=IFERROR(INDEX($F$3:$F$21,MATCH(F3,$G$3:$G$21,0)),"")
I3:L21	I3	=IFERROR(INDEX($F$3:$F$21,MATCH(H3,$G$3:$G$21,0)),"")
M3:M21	M3	=IFERROR(INDEX($G$3:$G$21,MATCH(G3,$F$3:$F$21,0)),"")
N3:Q21	N3	=IFERROR(INDEX($G$3:$G$21,MATCH(M3,$F$3:$F$21,0)),"")
R3:AC21	R3	=IFERROR(INDEX($B$3:$B$25,MATCH(F3,$A$3:$A$25,0)),"")
AD3:AD25	AD3	=AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$2))/($F$3:$Q$21=A3),1)
AE3:AE25	AE3	=MATCH(2,INDEX($R$3:$AC$21,AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$2))/($F$3:$Q$21=A3),1),),0)
C3:C25	C3	=INDEX($F$3:$Q$21,AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$2))/($F$3:$Q$21=A3),1),MATCH(2,INDEX($R$3:$AC$21,AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$2))/($F$3:$Q$21=A3),1),),0))

 

[broken keyboard]

Hi @KRice , your solution has worked a treat! Thank you very much for the effort in providing this solution.

 

[KRice]

You're welcome. I'm happy to help.