Formula for pulling multiple data within a spreadsheet

[xlbaby]

Hi guys,

I have a spreadsheet which has around 1000 rows and 400 columns which has certain numbers. For each corresponding rows, there may not be a corresponding number in the columns i.e. the numbers within the column are spread erratically.

I need a formula where in for each corresponding rows, if there is a corresponding number within a column, that number should populate. I have attached a spreadsheet which shows the data I have and output I need.

I hope I have made it clear. Please let me know if you guys need anything else.

[ChemistB]

See if this Arrayed formula works for you. In C29,

=IFERROR(INDEX($C$3:$U$12, MATCH($B29, $B$3:$B$12,0), SMALL(IF(INDEX($C$3:$U$12, MATCH($B29,$B$3:$B$12,0),)>0, COLUMN($C$3:$U$3)-COLUMN($C$3)+1),COLUMNS($A$1:A$1))),"") copied right and down

See attachment.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.