Linking excel workbooks

Occasional Contributor

I have a report that I need to do monthly where one column will include staff members' names a random number of times.  I want to autofill the adjacent column with the name of their line manager.  How do I do this please?  (I have a table of the corresponding information, there are hundreds of staff members.)  Many thanks

4 Replies



You can try INDEX and MATCH as shown in the screenshot. Alternatives could be VLOOKUP or - if you work with Excel 2016 or later - XLOOKUP. Another alternative could be Power Query.

staff member.JPG 


Thank you, your screenshot looks like exactly what I need.  I am receiving errors with the formula but will keep trying it, I'm on excel version 2210 with microsoft 365.

best response confirmed by Hans Vogelaar (MVP)



With Office 365 you can apply XLOOKUP.

staff member.JPG

Thank you so much for your time! That works perfectly.