Reference to a parent cell

[tomoonot]

I am trying to find a way how to reference all the B types to the first A type above.

The formula I have only works if the B is directly below an A, but there are cases, where there are multiple B-types per A-type.
In the Link column

Is there any way to make this work? Literally spent the whole day on this, trying to use chatgpt, but that didnt really work..

Please and thank you!

 

[shinigamilight]

=IF(A2="B",IF(B1="",C1,B1),"")

 

[Peter_SSs]

Welcome to the MrExcel board!

Not sure ..

• if you want to stick to the table nomenclature or
• if it is possible to have a sequence like I have in A6:A8

.. but here is another option in case either of the above applies

tomoonot.xlsm
	A	B	C
1	Type	Name	Link
2	A	Name A1
3	B		Name A1
4	C
5	A	Name A2
6	B		Name A2
7	C
8	B		Name A2
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=LET(r,INDEX([Name],1):[@Name],IF([@Type]="B",XLOOKUP("?*",r,r,,2,-1),""))

 

[tomoonot]

Thank you both!

Both solution work, but, the considerations from Peter_SSs were on point as it works with the column headers.

I would also like to do another slightly similar thing, been trying to figure that out half a day today but no luck again...

Having Type "0" above type "A" in hierarchy and each type having an ID somewhere to the left in a column "General ID",
would it be possible to create a table on another sheet that would contain a list of all unique names (minus blank) and their type 0 parent and the ID of both (the Type 0s have another ID or name in "Type_0_ID") (only type A has a name in the name column) and each type A only belongs to one type 0, but again type 0 can have multiple type A children?

Basically trying to figure out how to find a parent, this time not from B->A but from A->0
Tried modifying the formula with based on the types, but for this purpose it didnt really work.

Thank you

 

[Peter_SSs]

Thank you both!

Both solution work,

You're welcome. Glad we could help.

I would also like to do another slightly similar thing, ...

Could we have a small set of dummy sample data and the expected results with XL2BB (so we can easily copy to test with your data) and explain again in relation to that?

 

[tomoonot]

I figured it out in a way, but now stuck on something else.

Trying to find column header based on a cell in a named table, i've tried a lot of different stuff, but not sure if the function I am using cannot search through the whole table, or what is wrong.
Tried both INDEX,MATCH and XLOOKUP

=XLOOKUP("TDV07*",Table7,Table7[#Headers],,2,1)
> #VALUE! error

=XLOOKUP("TDV07*",Table7[Column_C],Table7[Column_D],,2,1)
> returns a cell when specified in which column to look and from which to return
when I try to change the return column into Table7[#Headers], I'm getting the same error

Can XLOOKUP search through the whole table, and find the first occurrence of the search term and return the header? if not, is there any other way?

Thank you!

 

[Peter_SSs]

This appears to be quite a different question so I suggest that you start a new thread to ask (& provide sample data and expected results to help clarify).

 

[tomoonot]

I've made a new post. Thank you!