Formula to pull up column/row head based on cell in data set

[cee_real]

Hello,

I'm looking for 2 formulas to identify:

1. Column header for selected cell.
2. Row header for selected cell.

Example:[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Company D[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[/TR]
</tbody>[/TABLE]

If the value selected is 10, I want cell F1 to return column (Mar) and cell G1 to return row (Company C).

 

[theBardd]

Try

=INDEX(B1:E1,MIN(IF(B2:E5=10,COLUMN(B1:E1)-MIN(COLUMN(B1:E1))+1,"")))

=INDEX(A2:A5,MIN(IF(B2:E5=10,ROW(A2:A5)-MIN(ROW(A2:A5))+1,"")))

both array formulas

 

[Fluff]

How about, put the value you want to find in F1

Book1
	A	B	C	D	E	F	G
1		Jan	Feb	Mar	Apr	10	Feb
2	Company A	1	2	3	4		Company C
3	Company B	5	6	7	8
4	Company C	9	10	11	12
5	Company D	13	14	15	16
Dropdowns

Cell Formulas
Range	Formula
G1	=INDEX(B1:E1,SUMPRODUCT((B2:E5=F1)*COLUMN(B2:E5))-COLUMN(B2:E5)+1)
G2	=INDEX(A2:A5,SUMPRODUCT((B2:E5=F1)*ROW(B2:E5))-ROW(B2:E5)+1)

 

[cee_real]

How about, put the value you want to find in F1

	A	B	C	D	E	F	G
Jan	Feb	Mar	Apr	Feb
Company A	Company C
Company B
Company C
Company D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Dropdowns​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1[/TH]
[TD="align: left"]=INDEX(B1:E1,SUMPRODUCT((B2:E5=F1)*COLUMN(B2:E5))-COLUMN(B2:E5)+1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=INDEX(A2:A5,SUMPRODUCT((B2:E5=F1)*ROW(B2:E5))-ROW(B2:E5)+1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This worked. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback