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

Boyle	Hayle	Coyle	Neli	Coile
-175	0	178	139	-142
-175	0	178	139	-142
-175	0	178	139	-142
-175	0	178	139	-142

<tbody>
</tbody>

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 :

-175	BOYLE
178	COYLE
139	NELI
-142	COILE

<tbody>
</tbody>

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.

<tbody></tbody>

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 0 Hayle 10 178 Coyle 11 139 Neli 12 -142 Coile <colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody> </tbody>

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

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.