List All Numbers

[Mypaperdue]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD]21[/TD]
[TD]13[/TD]
[TD]31[/TD]
[TD]14[/TD]
[TD]41[/TD]
[TD]15[/TD]
[TD]51[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]013[/TD]
[TD]578[/TD]
[TD]569[/TD]
[TD]589[/TD]
[TD]015[/TD]
[TD]378[/TD]
[TD]016[/TD]
[TD]278[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]124[/TD]
[TD]245[/TD]
[TD]014[/TD]
[TD]256[/TD]
[TD]237[/TD]
[TD]045[/TD]
[TD]127[/TD]
[TD]389[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]457[/TD]
[TD]689[/TD]
[TD]236[/TD]
[TD]478[/TD]
[TD]126[/TD]
[TD]156[/TD]
[TD]349[/TD]
[TD]056[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]679[/TD]
[TD]023[/TD]
[TD]125[/TD]
[TD]367[/TD]
[TD]459[/TD]
[TD]489[/TD]
[TD]238[/TD]
[TD]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]41[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]51[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]013[/TD]
[TD="align: center"]578[/TD]
[TD="align: center"]569[/TD]
[TD="align: center"]589[/TD]
[TD="align: center"]015[/TD]
[TD="align: center"]378[/TD]
[TD="align: center"]016[/TD]
[TD="align: center"]278[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]124[/TD]
[TD="align: center"]245[/TD]
[TD="align: center"]014[/TD]
[TD="align: center"]256[/TD]
[TD="align: center"]237[/TD]
[TD="align: center"]045[/TD]
[TD="align: center"]127[/TD]
[TD="align: center"]389[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]457[/TD]
[TD="align: center"]689[/TD]
[TD="align: center"]236[/TD]
[TD="align: center"]478[/TD]
[TD="align: center"]126[/TD]
[TD="align: center"]156[/TD]
[TD="align: center"]349[/TD]
[TD="align: center"]056[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]679[/TD]
[TD="align: center"]023[/TD]
[TD="align: center"]125[/TD]
[TD="align: center"]367[/TD]
[TD="align: center"]459[/TD]
[TD="align: center"]489[/TD]
[TD="align: center"]238[/TD]
[TD="align: center"]167[/TD]

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

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

[TD="align: center"]9[/TD]
[TD="align: center"]237[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]126[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]459[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Extract Column​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=MATCH(A1,F1:M1,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A8[/TH]
[TD="align: left"]=INDEX(F3:M3,A$2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.