Return header for column value is found in

[47Problem]

Have found solutions when it is known what row a value is one but what I am looking for is searching 4+ columns in a table to determine the header of the column in which the value resides. Example(s): 9 = "Guest Circuit ID", 7 = "Primary Circuit ID", 2 = "Backup Circuit ID"

Ultimately this would be best if tied into results in a pivot table so assigning a value using Name Manager might next practical step once it is determined how to find value. I thought an index/match would solve this issue but have been unable to make work. Cells will be alphanumeric as well and may include symbols.


[TABLE="class: grid, width: 485"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Primary Circuit ID [/TD]
[TD]Backup Circuit ID [/TD]
[TD]Guest Circuit ID [/TD]
[TD]Primary Router Name [/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

Match can only search one dimension, sumproduct will work here if each term doesn't repeat.

e.g.:

Excel 2010
	A	B	C	D	E	F
1	Primary Circuit ID	Backup Circuit ID	Guest Circuit ID	Primary Router Name
2	1	2	3	4
3	7	8	9	10
4	13	14	15	16
5	19	20	21	22
6
7
8					9	Guest Circuit ID
Sheet5

Cell Formulas
Range	Formula
F8	=INDEX($A$1:$D$1,SUMPRODUCT(($A$2:$D$5=E8)*COLUMN($A$2:$D$5)))

 

[47Problem]

I think that is definitely headed in the direction that I need however 'sumproduct' will not work with alphanumeric will it? I have updated the table in the quoted area below with better references (should have used more realistic values to start with).

Match can only search one dimension, sumproduct will work here if each term doesn't repeat.

e.g.:

Excel 2010

	A	B	C	D	E	F
Primary Circuit ID	Backup Circuit ID	Guest Circuit ID	Primary Router Name
Backup Circuit ID

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

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

[TD="align: center"]2[/TD]
[TD="align: right"]m12-4413[/TD]
[TD="align: right"]i4421[/TD]
[TD="align: right"]ic0302[/TD]
[TD="align: right"]powermusic3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]m12-4417[/TD]
[TD="align: right"]iw252[/TD]
[TD="align: right"]ic2042[/TD]
[TD="align: right"]air4423[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]m12-4419[/TD]
[TD="align: right"]i3312[/TD]
[TD="align: right"]ig2052[/TD]
[TD="align: right"]media0022[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]m12-4412[/TD]
[TD="align: right"]iw9303[/TD]
[TD="align: right"]i-3411[/TD]
[TD="align: right"]media9921[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]i3312[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F8[/TH]
[TD="align: left"]=INDEX($A$1:$D$1,SUMPRODUCT(($A$2:$D$5=E8)*COLUMN($A$2:$D$5)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

'sumproduct' will not work with alphanumeric will it?

It will

 

[Mike Szczesny]

Hi 47Problem,
Can you use this? Hope this helps.
Mike Szczesny

Excel 2012
	A	B	C	D	E	F
1	a	b	c	d		14
2	1	2	3	4
3	5	6	7	8
4	9	10	11	12		b
5	13	14	15	16
Sheet1

Cell Formulas
Range	Formula
F4	{=INDEX($A$1:$D$1,,SMALL(IF($A$2:$D$5=$F$1,COLUMN($A$1:$D$1)-COLUMN($A$1)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Nishant94]

Excel 2016
	A	B	C	D
1	Primary Circuit ID	Backup Circuit ID	Guest Circuit ID	Primary Router Name
2	1	2	3	4
3	7	8	9	10
4	13	14	15	16
5	19	20	21	22
6
7
8	Lookup Value	21
9	Result	Guest Circuit ID
10
Sheet4

Cell Formulas
Range	Formula
B9	=INDEX($A$1:$D$1,SUMPRODUCT(--EXACT(B8,$A$2:$D$5)*(COLUMN($A$2:$D$2)-COLUMN($A$2)+1)))