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:

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

<tbody>
</tbody>

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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Dropdowns​

Worksheet Formulas Cell 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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

This worked. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback