How to use a VLookup or a INDEX Match to return a value thats in random cells in column

Hi there,

I'm on MS 365

I have a formula problem I am struggling to make sense of.

I have attached a sheet which illustrates a basic view of the problem.

On the tab 'Raw Data' I have data coming in form an external source which can not be manipulated, it has to stay as it or it breaks the data source. in that sheet there is a table.
Column A - Is an ID number specific to a sales person
Column B - Is the place that person is based
Column C - Is how long they have worked
Column D - Is that sales persons client
Column E - Is their sales value
Column F - is the name of the sales person on the same row as their ID in column A

on the second tab 'Worksheet; I have the same table but this time removing the duplicates to only show one instance of the place of work, years active and clients as these are all duplicated on tab 'Raw Data'.
In column E I need to retrieve the sales person specific to each of those lines, however in raw data the name of the sales person is often in a random position, as such Vlookup or Index match hasnt worked as it looks for the first matching value, which is often a blank.

I have a third tab which is a reference table of all the sales people and their ID's.

what formula can I use to find the correct sales person based on the information I have in "Worksheet" as it is.

Thank you!

Apologies I'm not great with forums, and I thought I needed to explain myself better, didn't realise I needed to close it sorry.