Return column header after xlookup or index match

[Agent Carmichael]

Not sure what the most direct route for this is. This is a simplified version of a file a coworker maintains. There are hundreds of more names, and I'm trying to find their birthday. I have a list of names kept on a separate sheet, sorted differently. Name will be a unique identifier and an exact match (no duplicates). The yellow highlighted cells are the location where the formula/date should be inserted. I can get the location of the "Birthday" text, but the nested formulas always give me trouble.

 

[DanteAmor]

Try:

DANTE AMOR
	A	B	C	D	E	F	G	H	I
1	Name	Color	State	W	01/01/1992	02/01/1992	03/01/1992	04/01/1992
2	John						Birthday
3	David				Birthday
4	Mark							Birthday
5
6
7	Name
8	David	01/01/1992
9	Mark	04/01/1992
10	John	03/01/1992
11
Hoja8

Cell Formulas
Range	Formula
B8:B10	B8	=SUMPRODUCT(($E$2:$H$4="Birthday")*($A$2:$A$4=A8)*($E$1:$H$1))

 

[Jeffrey Mahoney]

Please try this

Book3
	A	B	C	D	E	F	G	H	I
1	Name	Fav Color	State	Weight	1/1/1992	1/2/1992	1/3/1992	1/4/1992	1/5/1992
2	John						Birthday
3	David				Birthday
4	Mark							Birthday
5
6
7	Name	Birthday
8	David	1/1/1992
9	Mark	1/4/1992
10	John	1/3/1992
Sheet1

Cell Formulas
Range	Formula
B8:B10	B8	=INDEX($E$1:$I$1,,MATCH("Birthday",INDEX($E$2:$I$4,MATCH(A8,$A$2:$A$4,0),),0))

 

[Jeffrey Mahoney]

Oh Dante, I should have known