Formula to pick out certain numbers.

[Eddielion]

Hello everyone. I need a formula to look for all the 1's and 2's (A2:J2) and list the corisponding number(A1:J1). My original spreadsheet has 37 numbers. I'm running on a Windows 10 and I think Excel 2013(office 360).
Row 1 A:J are the numbers that it's looking for.
Row 2 A:J is the number of times it found a certain number.
L2:?2 - is the list of numbers(which are in A1:J1) that met the criteria
L2:?2 Can have from 1 to to how ever many numbers it finds.

A1:J1 --- 1 2 3 4 5 6 7 8 9 10
A2:J2 --- 0 1 1 3 2 3 2 2 1 5
L2:?2 --- 2 3 5 7 8 9 (formula to find numbers )

 

[Ron Coderre]

Using your posted example....I've got two possible solutions, an array-formula, completed by holding CTRL and SHIFT when you press ENTER, and a regular formula. Typically, I can get a regular formula to emulate an array formula with just a little more effort (but not quite in this case)
Array-formula entered in L2 and copied across

=IFERROR(SMALL(IF(ISNUMBER(MATCH($A$2:$J$2,{1;2},0)),$A$1:$J$1),COLUMNS($A:A)),"")

Regular formula:

=IFERROR(SMALL(INDEX(ISNUMBER(MATCH($A$2:$J$2,{1;2},0))*$A$1:$J$1,0),COLUMNS($A:A)+
10-SUM(COUNTIF($A$2:$J$2,{1,2}))),"")

Is that something you can work with?

 

[Peter_SSs]

Here is another option for you. Formula in L2 (standard entry) copied across to U2.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	1	2	3	4	5	6	7	8	9	10
2	0	1	1	3	2	3	2	2	1	5		2	3	5	7	8	9
List Values

 

[Eddielion]

Hello Ron.
Thank you very much. Yes this will work for me, and thank you for the speedy responds.
Eddie

 

[Eddielion]

Hello Peter.
Thank you for your response also. I appreciate the help. Thank you again.
Eddie

 

[Peter_SSs]

Hello Peter.
Thank you for your response also. I appreciate the help. Thank you again.
Eddie

You are very welcome.