Thanks so much for your reply, apologies for my late response.. I think I may have also made it sound more complex than it needs to be.

As there shouldn't be any duplicates between workbooks (e.g. lets say we have 100 companies and theyre equally split between 4 workbooks, they may have 25 each or could be 30, 30, 30 and 10 but never totalling over 100). So maybe when I paste them into their workbook for them to use I could create a column called location and write the name of the person using that workbook, that way I can just do nested vlookups and bring back that column?

Think that could work.... will have to test and see