Find value along a row and return header name

[alecambo]

Hello,

I need a method to find the value that's in the first column (tar2x) along its row and then return the name of the column header where it is located to a new cell (the column "Values" in this example).
For instance, the value 785 is found in the column named "5", so I would like to add that 5 to the column "Values".

tar2x

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

Values

785​

779​

1140​

1253​

667​

785​

907​

1254​

654​

778​

1141​

1257​

661​

893​

656​

778​

1133​

1265​

784​

896​

1013​

5

900​

786​

1261​

782​

898​

1139​

1262​

653​

786​

1261​

665​

783​

898​

1022​

1254​

899​

1025​

1263​

774​

900​

1134​

19

897​

897​

1134​

1262​

658​

773​

1013​

1135​

1258​

1263​

665​

775​

894​

1021​

1147​

1256​

662​

785​

897​

1017​

1133​

1

1013​

663​

779​

1146​

1265​

1261​

657​

896​

1013​

1260​

773​

1018​

1143​

655​

774​

905​

1016​

1142​

1256​

899​

1146​

8

1024​

665​

775​

906​

1133​

785​

895​

1024​

1136​

1264​

1146​

1265​

782​

900​

1139​

775​

1139​

656​

773​

907​

1259​

7

*I must add that the colors and the bold text have no significance, it's just to make visualization easier

Then, I would like to search the returned values among a header row and return the value that's inside the cell where the value and the header name match.
For example, when I search for the value 5, I want to get the value for column named 5 (same row), in this case, a 0. For the value 19 it would be 0, for the value 1 it would be 1 and so on.

Values

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

Return

5

1​

1​

0​

0​

0​

0​

1​

0​

0​

0​

0​

1​

1​

1​

1​

0​

1​

1​

0​

1​

0

19

1​

1​

1​

0​

0​

1​

0​

0​

1​

1​

1​

0​

0​

1​

1​

0​

0​

1​

0​

0​

0

1

1​

0​

0​

1​

0​

1​

0​

1​

0​

0​

0​

0​

0​

1​

0​

1​

1​

1​

1​

1​

1

8

1​

0​

1​

0​

0​

1​

1​

0​

0​

0​

1​

1​

0​

1​

1​

0​

1​

1​

0​

0​

0

7

1​

0​

1​

1​

1​

1​

0​

0​

1​

0​

0​

0​

1​

1​

1​

0​

1​

0​

0​

0​

0

Kind regards,
alecambo

 

[Z51]

For the Values, you can use:

=MATCH(A2,B2:U2,0)

For the Return, use:

=INDEX(W2:AP2,1,MATCH(V2,W$1:AP$1,0))

Adjust to suit your actual ranges, of course.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1	tar2x	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	Values	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	Return
2	785	779	1140	1253	667	785	907	1254	654	778	1141	1257	661	893	656	778	1133	1265	784	896	1013	5	1	1	0	0	0	0	1	0	0	0	0	1	1	1	1	0	1	1	0	1	0
3	900	786	1261	782	898	1139	1262	653	786	1261	665	783	898	1022	1254	899	1025	1263	774	900	1134	19	1	1	1	0	0	1	0	0	1	1	1	0	0	1	1	0	0	1	0	0	0
4	897	897	1134	1262	658	773	1013	1135	1258	1263	665	775	894	1021	1147	1256	662	785	897	1017	1133	1	1	0	0	1	0	1	0	1	0	0	0	0	0	1	0	1	1	1	1	1	1
5	1013	663	779	1146	1265	1261	657	896	1013	1260	773	1018	1143	655	774	905	1016	1142	1256	899	1146	8	1	0	1	0	0	1	1	0	0	0	1	1	0	1	1	0	1	1	0	0	0
6	1024	665	775	906	1133	785	895	1024	1136	1264	1146	1265	782	900	1139	775	1139	656	773	907	1259	7	1	0	1	1	1	1	0	0	1	0	0	0	1	1	1	0	1	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
V2:V6	V2	=MATCH(A2,B2:U2,0)
AQ2:AQ6	AQ2	=INDEX(W2:AP2,1,MATCH(V2,W$1:AP$1,0))

 

[gargamalebarbosa]

Try this one.

 

[Fluff]

For the Return you could also use

=INDEX(W2:AP2,,V2)

 

[alecambo]

Thanks everybody, your solutions work great!

 

[Fluff]

Glad we could help & thanks for the feedback.