Find the heading for the first value in a looked up row

[SoniaL]

Hi there,

I'm trying to get a heading for a row where there's a value, where I also need to lookup which row to search in.

This visual may be helpful. I am trying to populate the yellow cells in the top table with the headings from the bottom table
So logically the steps are:
1) Using the "Lookup Value", find the corresponding row in the second table
2) With that row, find the first OR next non-blank cell
3) Then return the heading of that column

Is this possible to do in a formula?

Thanks

Sonia

 

[RasGhul]

Maybe this, I'm not sure if this is what your goal is?

Book1
	A	B	C	D	E	F	G	H
1		Heading Name 1	Heading Name 2	Heading Name 3
2	Lookup Value 1
3	Lookup Value 2	Return for Lookup 1	Heading 6
4	Lookup Value 3
5	Lookup Value 4
6	Lookup Value 5	Col 5 Heading
7
8		Heading Name 1	Heading Name 2	Heading Name 3	Return for Lookup 1	Heading 5	Heading 6	Col 5 Heading
9	Lookup Value 1
10	Lookup Value 2				5		8
11	Lookup Value 3
12	Lookup Value 4
13	Lookup Value 5							a
14	Lookup Value 6
15	Lookup Value 7
16
Sheet1

Cell Formulas
Range	Formula
B2:D6	B2	=IFERROR(INDEX($B$8:$H$8,SMALL(IF(($B$9:$H$15<>"")*($A$9:$A$15=$A2),COLUMN($B$8:$H$8)-COLUMN($B$8)+1),COLUMNS($B2:B2))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Another option

=FILTER($B$8:$H$8,FILTER($B$9:$H$15,$A$9:$A$15=A2)<>"","")

 

[SoniaL]

Maybe this, I'm not sure if this is what your goal is?

Book1
	A	B	C	D	E	F	G	H
1		Heading Name 1	Heading Name 2	Heading Name 3
2	Lookup Value 1
3	Lookup Value 2	Return for Lookup 1	Heading 6
4	Lookup Value 3
5	Lookup Value 4
6	Lookup Value 5	Col 5 Heading
7
8		Heading Name 1	Heading Name 2	Heading Name 3	Return for Lookup 1	Heading 5	Heading 6	Col 5 Heading
9	Lookup Value 1
10	Lookup Value 2				5		8
11	Lookup Value 3
12	Lookup Value 4
13	Lookup Value 5							a
14	Lookup Value 6
15	Lookup Value 7
16
Sheet1

Cell Formulas
Range	Formula
B2:D6	B2	=IFERROR(INDEX($B$8:$H$8,SMALL(IF(($B$9:$H$15<>"")*($A$9:$A$15=$A2),COLUMN($B$8:$H$8)-COLUMN($B$8)+1),COLUMNS($B2:B2))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Yes! Exactly this!!! Thank you!!