Find header based on cell value

[Akinrotimi]

Hello,

I have a dataset where I want to locate the person with a value in a particular row.

The table below is on range A1:E5


[TABLE="width: 500"]
<tbody>[TR]
[TD]Boyle[/TD]
[TD]Hayle[/TD]
[TD]Coyle[/TD]
[TD]Neli[/TD]
[TD]Coile[/TD]
[/TR]
[TR]
[TD]-175[/TD]
[TD]0[/TD]
[TD]178[/TD]
[TD]139[/TD]
[TD]-142[/TD]
[/TR]
[TR]
[TD]-175[/TD]
[TD]0[/TD]
[TD]178[/TD]
[TD]139[/TD]
[TD]-142[/TD]
[/TR]
[TR]
[TD]-175[/TD]
[TD]0[/TD]
[TD]178[/TD]
[TD]139[/TD]
[TD]-142[/TD]
[/TR]
[TR]
[TD]-175[/TD]
[TD]0[/TD]
[TD]178[/TD]
[TD]139[/TD]
[TD]-142[/TD]
[/TR]
</tbody>[/TABLE]


I want to search for the recipient of any value on any row,for example 139 should be Neli for all the rows in the above example.

Expected result is :
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-175[/TD]
[TD]BOYLE[/TD]
[/TR]
[TR]
[TD]178[/TD]
[TD]COYLE[/TD]
[/TR]
[TR]
[TD]139[/TD]
[TD]NELI[/TD]
[/TR]
[TR]
[TD]-142[/TD]
[TD]COILE[/TD]
[/TR]
</tbody>[/TABLE]

Every help would be appreciated.

Thanks

 

[AhoyNC]

Maybe something like this:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B	C	D	E
1	Boyle	Hayle	Coyle	Neli	Coile
2	-175	0	178	139	-142
3	-175	0	178	139	-142
4	-175	0	178	139	-142
5	-175	0	178	139	-142
6
7
8	-175	Boyle
9	178	Coyle
10	139	Neli
11	-142	Coile
Sheet

 

[Xandria619]

Hi.

[TABLE="width: 500"]
<tbody></tbody>[/TABLE]
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl66, width: 64"]Boyle[/TD]
[TD="class: xl66, width: 64"]Hayle[/TD]
[TD="class: xl66, width: 64"]Coyle[/TD]
[TD="class: xl66, width: 64"]Neli[/TD]
[TD="class: xl66, width: 64"]Coile[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl66, width: 64"]-175[/TD]
[TD="class: xl66, width: 64"]0[/TD]
[TD="class: xl66, width: 64"]178[/TD]
[TD="class: xl66, width: 64"]139[/TD]
[TD="class: xl66, width: 64"]-142[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl66, width: 64"]-175[/TD]
[TD="class: xl66, width: 64"]0[/TD]
[TD="class: xl66, width: 64"]178[/TD]
[TD="class: xl66, width: 64"]139[/TD]
[TD="class: xl66, width: 64"]-142[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl66, width: 64"]-175[/TD]
[TD="class: xl66, width: 64"]0[/TD]
[TD="class: xl66, width: 64"]178[/TD]
[TD="class: xl66, width: 64"]139[/TD]
[TD="class: xl66, width: 64"]-142[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl66, width: 64"]-175[/TD]
[TD="class: xl66, width: 64"]0[/TD]
[TD="class: xl66, width: 64"]178[/TD]
[TD="class: xl66, width: 64"]139[/TD]
[TD="class: xl66, width: 64"]-142[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl66, width: 64"]-175[/TD]
[TD="class: xl65"]Boyle[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl66, width: 64"]0[/TD]
[TD="class: xl65"]Hayle[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD="class: xl66, width: 64"]178[/TD]
[TD="class: xl65"]Coyle[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]11[/TD]
[TD="class: xl66, width: 64"]139[/TD]
[TD="class: xl65"]Neli[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]12[/TD]
[TD="class: xl66, width: 64"]-142[/TD]
[TD="class: xl65"]Coile[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

Code in Cell B9: =INDEX($B$2:$F$2,MATCH(B9,$B$3:$F$3,0)).

This code can be copied down into cells B9:B12.

Hope this helps.

 

[Aladin Akyurek]

If the real data contain duplicate occurrences across columns...

Book1
	A	B	C	D	E	F	G	H	I	J
1	Boyle	Hayle	Coyle	Neli	Coile
2	-175	0	178	139	-142			139	Hayle	Neli
3	-175	0	178	139	-142			178	Coyle
4	-175	139	178	139	-142
5	-175	0	178	139	-142
Sheet1

In I2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($A$1:$E$1,SMALL(IF(FREQUENCY(IF($A$2:$E$5=$H2,MATCH($A$1:$E$1,$A$1:$E$1,0)),TRANSPOSE(COLUMN($A$1:$E$1)-COLUMN($A$1)+1)),TRANSPOSE(COLUMN($A$1:$E$1)-COLUMN($A$1)+1)),COLUMNS($I2:I2))),"")

 

[Akinrotimi]

Thanks AhoyNC

I however noticed that it selects a negative number where there are contras.Foir example if D3 is changed to -178, the result become Neli and not Coyle for 178. Please help adjust.

Thanks

 

[Akinrotimi]

Thanks Aladin

I noticed that even when we swap A2 and C2 (such that A2 is 178 while C2 is -175 for Coyle) for example,the result for row 2 would still be BOYle when it should be Coyle.This is because another row has BOYLE on the an earlier column.

Please help with this slight issue left.

Thanks a lot

 

[Aladin Akyurek]

Thanks Aladin

I noticed that even when we swap A2 and C2 (such that A2 is 178 while C2 is -175 for Coyle) for example,the result for row 2 would still be BOYle when it should be Coyle.This is because another row has BOYLE on the an earlier column.

Please help with this slight issue left.

Thanks a lot

When A2 = 178 and C2 = -175, I get for 178 the following headers: Boyle then Coyle.

Control+shift+enter: Press down the control and the shift keys at the same while you hit the enter key. If this is done successfully, Excel itself puts a pair of { and } aroud the formula in recognition.