Looking up one value then returning multiple matches across a row

I have a workbook with two worksheets

Sheet1 column C contains a list of names
Sheet1 column D contains a list of alphanumberic values

I have the below formula in another worksheet which is reading in a value from cell B3, looking this up in Sheet1 column D and returning the value from Sheet1 column C. I want to return all returned values across the row, eg. cell E3, E4, E5, etc. This works fine in the first cell containing the formula. I then copied it across horizontally and get a !NUM error in all the other cells.

=INDEX(Sheet1!$C$2:$C$5000, SMALL(IF($B$3=Sheet1!$D$2:$D$5000, ROW(Sheet1!$D$2:$D$5000)-MIN(ROW(Sheet1!$D$2:$D$5000))+1, ""), COLUMN(B1)))

Can somebody please tell me where I'm going wrong?

Thanks,

I have tried both C+S+E which gives a !value error, and just hitting Enter gives me a data match, but then doesn't work for any other cells. Perhaps there is another issue with the formula because I don't fully understand it, i just copied and modified it from various other posts I found. Thanks.

I've tried that too and still the same output

I can't see an option to upload a spreadsheet, so here is the structure

Sheet1
ROLE NAME (C1) PERMISSION ID (D1)
Name1 3
Name2 3
Name3 3
Name4 3
Ross1 4
Ross2 4
Matt 5
Steve 6
Phil 7
Lucy 8

Then on another sheet I have column B containing values 3, 4, 5, etc. Then column containing the above formula and copied across each row, and also down each column.

Haha, but the paperclip icon from last time has disappeared??

Ok I've found it now, see attached.

Ok sorry, I'll try to explain better...

I have some ID numbers in column B in the Names worksheet.
In a separate worksheet (ROLE_PERMISSION) I have a list of names with ID numbers.
I want to be able to look up each ID number from the first worksheet and return a list of the names which have that ID (one name per cell across columns D, E, F, G). Does that help? I realise my cell references in the formula attached are slightly wrong but fixing these still gives the #NUM! error.... Thanks