Lookup values and out horizontally

[Steve1977]

I've done Array formula's before and got them working, but for whatever reason, it's not working on my current information.
Currently I've two sheets:

Master Sheet - 'Sheet1M'
2nd Sheet - 'Sheet2S'


Sheet1M contains the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]60521235[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]PART01[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]60521236[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]PART02[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]60521237[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]PART03[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]542184[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]PART04[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]542920[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]PART05[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]545069[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]PART06[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]60556032[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]PART07[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2S contains the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD]60521235[/TD]
[TD]REF1[/TD]
[/TR]
[TR]
[TD]60521236[/TD]
[TD]REF2[/TD]
[/TR]
[TR]
[TD]60521236[/TD]
[TD]REF3[/TD]
[/TR]
[TR]
[TD]542184[/TD]
[TD]REF4[/TD]
[/TR]
[TR]
[TD]542920[/TD]
[TD]REF5[/TD]
[/TR]
[TR]
[TD]542920[/TD]
[TD]REF6[/TD]
[/TR]
[TR]
[TD]60556032[/TD]
[TD]REF7[/TD]
[/TR]
</tbody>[/TABLE]


So notice how the second sheet has instances where the number in Column A is duplicated because there's more than one ref in Column B

[TABLE="width: 500"]
<tbody>[TR]
[TD]60521235[/TD]
[TD]PART01[/TD]
[TD]REF1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]60521236[/TD]
[TD]PART02[/TD]
[TD]REF2[/TD]
[TD]REF3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]60521237[/TD]
[TD]PART03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]542184[/TD]
[TD]PART04[/TD]
[TD]REF4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]542920[/TD]
[TD]PART05[/TD]
[TD]REF5[/TD]
[TD]REF6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]545069[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]PART06[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]60556032[/TD]
[TD]PART07[/TD]
[TD]REF7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


When I've done similar things previously this is the Array Formula I have used:
{=INDEX(Sheet2S!$A$2:$B$155508,SMALL(IF(Sheet2S!$A$2:$A$155508=Sheet1M!$A2,ROW(Sheet2S!$A$2:$A$155508)-1),COLUMNS(Sheet1M!$C2)),2)}

But for whatever reason it's simply not working now and I don't know why.

If anyone can help me where I'm going wrong or even seek a resolution via VBA code it would be very much appreciated!

 

[Fluff]

The last part of the formula should be
COLUMNS($A$1:A$1)

 

[Fluff]

Or you could use a non-array formula
=IFERROR(INDEX(Sheet2S!$B$2:$B$8,AGGREGATE(15,6,(ROW(Sheet2S!$B$2:$B$8)-ROW(Sheet2S!$B$2)+1)/(Sheet2S!$A$2:$A$8=$A2),COLUMNS($A$1:A$1))),"")

 

[Steve1977]

Superb Fluff, thank you

 

[Fluff]

You're welcome & thanks for the feedback