Need to Populate Multiple Columns From Separate Sheet

[shak1701]

I am trying to populate 3 columns with manager names found in a separate Excel sheet. These three columns are split according to the seniority of the manager. These are split as 'T1 Manager', 'T2 Manager' and 'T3 Manager'. The usual solution would be to run a lookup on employee name and return the manager they report to. 

My issue is, I have split the managers by tier and need results accordingly. So I need a loop solution or something. So for example if an employees manager is not in T1, then the formula looks in T2 and T3. Also according to seniority of the employee they could have a T1, T2 and T3 manager. 

My data is as such:

Sheet 1 

|Invoice Name | |T1 Manager| | T2 Manager| |T3 Manager|

Daniel Hucker

Steve Jones

Simon Cox

Sheet 2 

|Invoice Name | |T1 Manager| | T2 Manager| |T3 Manager|

Daniel Hucker     Max John        Alan Marx      Joe Bloggs

Steve Jones         Alex Sugar       Alice Red        

Simon Cox                                                        Steve Fox

[#2 · Accepted solution]

@shak1701 

XLOOKUP or VLOOKUP. You have some options.

XLOOKUP:

=TEXT(XLOOKUP(A2,Sheet2!$A$2:$A$4,Sheet2!$B$2:$D$4,""),";;")

VLOOKUP:

=TEXT(VLOOKUP(A2,Sheet2!$A$2:$D$4,{2,3,4},0),";;")

[#3]

Thank you very much.

[#4]

You are welcome!