Lookup in Multiple Workbooks and Return Value

[JosueDG]

Hello everyone!

I have the following scenario:

• One main workbook (lets call it A)
• Five additional workbooks (lets call them 1,2,3,4,& 5)

A has the following header:
Associate Rev 1 Coaching 1 Rev 2 Coaching 2

1,2,3,4,& 5 have the following header:
Associate Coaching 1 Coaching 2 Rev 1 Rev 2


What I need:
From A,search 1,2,3,4,& 5 for the same Associate and place Rev 1, Rev 2, Coaching 1, and Coaching 2 in its respective places in A. The associate names can move from one workbook to another during any given time which is why I am thinking a simple "=[1.xslx]Sheet1!" will not work. I have tried a number of VLOOKUP combination's with no luck.