List All Numbers

[Mypaperdue]

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1						12	21	13	31	14	41	15	51
2
3						013	578	569	589	015	378	016	278
4						124	245	014	256	237	045	127	389
5						457	689	236	478	126	156	349	056
6						679	023	125	367	459	489	238	167
7
8
9
10
11
12
13
14
15
16

<tbody>
</tbody>

Good afternoon all,
I have a small table I am working with and I ran into a road block and was hoping I can get some assistant please. What I am trying to accomplish here is…. I have cell F1:M1 showing what I call “codes” and below them, I have cell F3:M6 showing different numbers representing each “codes.” What I want to do is input a “code” in cell A1 and have cell A8:A11 display the number that is representing that code. For example, if I were to input “code 14” in cell A1, I want cell A8:A11 to display 015,237,126 and 459.

Thank you in advance.

 

[Marcelo Branco]

Try

A8 copied down till A11
=IF(A$1="","",INDEX($F$3:$M$6,ROWS(A$8:A8),MATCH(A$1,F$1:M$1,0)))

Custom format A8:A11
000

M.

 

[Peter_SSs]

Another option, especialyy if you could use another cell (eg A2)

A8 is copied down
I have assumed F3:M6 are text values. If formatted numeric, then, as Marcelo has suggested, format the result cells the same.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	14					12	21	13	31	14	41	15	51
2	5
3						013	578	569	589	015	378	016	278
4						124	245	014	256	237	045	127	389
5						457	689	236	478	126	156	349	056
6						679	023	125	367	459	489	238	167
7
8	015
9	237
10	126
11	459
Extract Column

Cell Formulas
Range	Formula
A2	=MATCH(A1,F1:M1,0)
A8	=INDEX(F3:M3,A$2)

 

[Mypaperdue]

Try

A8 copied down till A11
=IF(A$1="","",INDEX($F$3:$M$6,ROWS(A$8:A8),MATCH(A$1,F$1:M$1,0)))

Custom format A8:A11
000

M.

Marcelo you always have the right answers. Again you sir are a GENIUS!! Thank you so much

 

[Mypaperdue]

Another option, especialyy if you could use another cell (eg A2)

A8 is copied down
I have assumed F3:M6 are text values. If formatted numeric, then, as Marcelo has suggested, format the result cells the same.

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	14					12	21	13	31	14	41	15	51
2	5
3						013	578	569	589	015	378	016	278
4						124	245	014	256	237	045	127	389
5						457	689	236	478	126	156	349	056
6						679	023	125	367	459	489	238	167
7
8	015
9	237
10	126
11	459

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

Extract Column​

Worksheet Formulas Cell Formula A2 =MATCH(A1,F1:M1,0) A8 =INDEX(F3:M3,A$2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

This also worked. Thank you very much

 

[Marcelo Branco]

Marcelo you always have the right answers. Again you sir are a GENIUS!! Thank you so much

You are very welcome. Glad to help.

M.

 

[Peter_SSs]

This also worked. Thank you very much

You are welcome from me too.

If you didn't want to use the helper cell like I did, you could simplify the INDEX part of Marcelo's formula a bit as follows for A8.

INDEX(F3:M3,MATCH(A$1,F$1:M$1,0))


BTW, best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.