formula to copy information

I need the location tab to be populated with the information from the data tab

e.g P1- pv02, 12

needs to be populated into the location tab work ref and team ID

How would it work for the following

Data table
location ID Work Ref
P1 FD
P1 FC
P1 FS

Location Table

Location ID Work Ref (pick up both FD FC, F5)
P1

See example

Same idea as post #2

B2 =VLOOKUP(A2,Data!A:E,4,0)
C2 =VLOOKUP(A2,Data!A:E,5,0)

Output will be:
Prime Ref Work Ref Team Ref
P1 PV02, G4 W231, W232

So in the location tab, the P1- should pick up both PV02 and G4 so on....

@ 63falcondude

The formula only picks up the one value as opposed to two e.g. P1 should pick up W231 and W232

If you want to do this through formulas, you will have to allocate extra cells for the 2nd, 3rd, etc matches.

Your current layout with the expectation of multiple results in the same cell separated by commas isn't going to happen.

I have not too fussed if the commas are not in between to separate. Is there still no way of doing it then?

Can you provide me with an example of how this will be done with the extra cells

Sure. Also, please update your profile to show the current version of Excel that you are using.
Right now, it shows 2003 but you uploaded a .xlsx which shows that you have at least 2007.

Try this in Location B2:
=IFERROR(INDEX(Data!$D:$D,SMALL(IF(Data!$A$2:$A$6=$A2,ROW(Data!$A$2:$A$6)),COLUMNS($A:A))),"") Ctrl Shift Error
Drag the formula to the right as far as needed.

Then put this in let's say I2:
=IFERROR(INDEX(Data!$E:$E,SMALL(IF(Data!$A$2:$A$6=$A2,ROW(Data!$A$2:$A$6)),COLUMNS($A:A))),"") Ctrl Shift Error
Drag the formula to the right as far as needed.

Drag both formulas down as far as needed.

Only still picks up the one value

I am using Office 2016