SOLVED

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

@Clare1487 

=INDEX($E$2:$E$27,MATCH(A2,$D$2:$D$27,0))

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 

@Quadruple_Pawn 

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)
Solution

@Clare1487 

=XLOOKUP(A2,$D$2:$D$81,$E$2:$E$81)

With Office 365 you can apply XLOOKUP.

staff member.JPG

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