Finding Top Cell in list

[lostagaintoo]

I have two sheets, one has products names in a vertical list, and the other has product names in a table (this isn't a table using the excel table)

heading A Heading B Heading C
Product 1 Product 11 Product 31
Product 12 Product 13 Product 23


In this table products can only appear once and names are unique. I want to add the heading name to the first list so that each product in the vertical list has a heading name against it. I can't see a way to do this using my current skill set (vlookup, hlookup and and few more). Any suggestions appreciated.

[Kaper]

this shall work in B2:

Please Login or Register  to view this content.

it is array formula* and I assumed your second table table is in columns D:F (headers in row 1)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F
1	Heading A	Heading B	Heading C	------	Product 1	Heading A
2	Product 1	Product 11	Product 31		Product 12	Heading A
3	Product 12	Product 13	Product 23		Product 11	Heading B
4					Product 13	Heading B
5					Product 31	Heading C
6					Product 23	Heading C

This array formula** entered in F1 and copied down:

=INDEX(A$1:C$1,MAX(IF(A$2:C$3=E1,COLUMN(A$2:C$3)))-COLUMN(A$2)+1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.