Formula to provide heading names for a specific cell in a table

[Willie77]

I have a table A1:F6 with 5 rows and 5 columns of data and with column headings (B1:F1) and row headings (A2:A6). I want to find the heading names for cell D5. All heading names are unique.
What formula can I use to find the column heading name (D1) for the value in cell D5?
What formula can I use to find the row heading name (A5) for the value in cell D5?

 

[Scott Huish]

Is there a bit more to this?
If it's always D5, then =D1 will give you your column heading and =A5 will give you your row heading

 

[AhoyNC]

Try:
This assumes the value in I2 will be unique.

Book1
	A	B	C	D	E	F	G	H	I
1		col1	col2	col3	col4	col5		Ite m	Formula
2	Row 1	15	33	11	14	90		Cell 05	94
3	Row 2	56	51	83	58	45		Col Heading	col3
4	Row 3	22	22	92	36	36		Row Heading	Row 4
5	Row 4	62	13	94	77	12
6	Row 5	18	63	55	46	34
Sheet1

Cell Formulas
Range	Formula
I3	I3	=INDEX($B$1:$F$1,SUMPRODUCT(($B$2:$F$6=$I$2)*(COLUMN($B$1:$F$1)-COLUMN($B$1)+1)))
I4	I4	=INDEX($A$2:$A$6,SUMPRODUCT(($B$2:$F$6=$I$2)*(ROW($A$2:$A$6)-ROW(A2)+1)))

 

[Scott Huish]

If you have to find that value anywhere in the table:

Book2
	A	B	C	D	E	F	G	H	I
1		col1	col2	col3	col4	col5		Ite m	Formula
2	Row 1	15	33	11	14	90		Cell 05	94
3	Row 2	56	51	83	58	45		Col Heading	col3
4	Row 3	22	22	92	36	36		Row Heading	Row 4
5	Row 4	62	13	94	77	12
6	Row 5	18	63	55	46	34
Sheet1

Cell Formulas
Range	Formula
I3	I3	=TEXTJOIN("",TRUE,IF(B2:F6=I2,B1:F1,""))
I4	I4	=TEXTJOIN("",TRUE,IF(B2:F6=I2,A2:A6,""))

 

[Willie77]

Try:
This assumes the value in I2 will be unique.

Book1
	A	B	C	D	E	F	G	H	I
1		col1	col2	col3	col4	col5		Ite m	Formula
2	Row 1	15	33	11	14	90		Cell 05	94
3	Row 2	56	51	83	58	45		Col Heading	col3
4	Row 3	22	22	92	36	36		Row Heading	Row 4
5	Row 4	62	13	94	77	12
6	Row 5	18	63	55	46	34
Sheet1

Cell Formulas
Range	Formula
I3	I3	=INDEX($B$1:$F$1,SUMPRODUCT(($B$2:$F$6=$I$2)*(COLUMN($B$1:$F$1)-COLUMN($B$1)+1)))
I4	I4	=INDEX($A$2:$A$6,SUMPRODUCT(($B$2:$F$6=$I$2)*(ROW($A$2:$A$6)-ROW(A2)+1)))

I2 is not unique. There can be duplicates in the table.

 

[Willie77]

If you have to find that value anywhere in the table:

Book2
	A	B	C	D	E	F	G	H	I
1		col1	col2	col3	col4	col5		Ite m	Formula
2	Row 1	15	33	11	14	90		Cell 05	94
3	Row 2	56	51	83	58	45		Col Heading	col3
4	Row 3	22	22	92	36	36		Row Heading	Row 4
5	Row 4	62	13	94	77	12
6	Row 5	18	63	55	46	34
Sheet1

Cell Formulas
Range	Formula
I3	I3	=TEXTJOIN("",TRUE,IF(B2:F6=I2,B1:F1,""))
I4	I4	=TEXTJOIN("",TRUE,IF(B2:F6=I2,A2:A6,""))

There can be duplicate numbers in the table. I want a column/row heading name just for that one specific cell.

 

[Scott Huish]

I gave you that in Post #2

 

[Willie77]

I gave you that in Post #2

Hi Scott. When I want to find the heading name of a cell with a duplicate number in my table, then the output is as in my attached table. I want the heading names of a specific cell with or without duplicate numbers.