How to search for data on one sheet and pull it over to another.

Hello, I know it may be something really simple, but I cannot figure this one out. We use a spreadsheet to manage all our learners in the centre. We have a main page which contains their username, name, end dates, etc. I want separate sheets for different programmes, for example, Numeracy, Literacy, Workskills and IT. I would like some sort of formula (or maybe VBA code) that will look at a column on the main sheet and see what programme their are on and pull there name over to the relevant sheet but still leaving the details on the main page aswell.

I have attached a small spreadsheet explaining what I am after.

Thank you in advance for any help with this problem.

Sam

Hello Sam try this in C3 in maths sheet copy and paste below
=IFERROR(INDEX(Learners!$C$4:$C$6,SMALL(IF(LEFT(Learners!$E$4:$E$6,FIND(" ",Learners!$E$4:$E$6)-1)="Maths",ROW($E$4:$E$6)-ROW($E$4)+1),ROWS($A$1:A1))),"") and hold control and shift then hit enter and copy down

for Employ sheet use the same formula just change "Maths" to "Employ"

Click cell press f2 then hold control and shift together then hit enter(this is confirmation of array)
Once formula is entered as array you will find curly braces {} surrounding your formula which cannot be entered manually

Find the attached

Drag the formula down(around 30-40 rows) it will show empty cells but when you put maths in first sheet it will pick the name automatically

Hello Sam that can be done but you need to adjust the formula. if you dont mind Can you post a sample book again to just have a look with around 10-15 rows of data to work.