Need Help Populating Columns Please!

**BeetleJuice1701** · 11-07-2022, 05:12 PM

Managers Test.xlsx Test1.xlsx

As you can see from the attachment, I have the columns T1 Manager, T2 Manager and T3 Manager empty in the "Test" doc. T1 through to T3 correspond to seniority of the manager and in turn the seniority of the person in "Invoice Name" which dictates the level of management they report to. So for example a junior employee reports to T1, T2 and T3 management. Whereas a more superior employee will report to T2 and T3 only. I need a solution that will pick loop from T1 through T3 in the "Manager" doc returning text where necessary.

I look forward to your responses.

**Pete_UK** · 11-07-2022, 09:19 PM

Assuming both files are open, you can use this formula in cell D2 of the Test file:

=VLOOKUP($A2,'[Managers Test.xlsx]Sheet1'!$B$3:$E$5,COLUMNS($C:D),0)&""

Copy across into E2:F2, then copy the three formulae down as required.

Hope this helps.

Pete

**sandy666** · 11-07-2022, 09:19 PM

you didn't show expected result but maybe this is what you want

Invoice Name	Area	Department	T1 Manager	T2 Manager	T3 Manager
Daniel Hucker	Sainsburys Depots	Zone Manager		Dan Hucker
Simon Reid	Retail FM Field Operations	Zone Fabric Specialist	Paul Whiting	Robert Mcgeachie	Noel Farrell
Steven Jenkins	Retail FM Field Operations	Zone Fabric Specialist	Paul Whiting	Robert Mcgeachie	Noel Farrell

**BeetleJuice1701** · 11-07-2022, 09:26 PM

Hi Sandy, this s exactly what I'm after. Please can you share the solution?

**BeetleJuice1701** · 11-07-2022, 09:27 PM

Originally Posted by Pete_UK

Assuming both files are open, you can use this formula in cell D2 of the Test file:

=VLOOKUP($A2,'[Managers Test.xlsx]Sheet1'!$B$3:$E$5,COLUMNS($C:D),0)&""

Copy across into E2:F2, then copy the three formulae down as required.

Hope this helps.

Pete

Thanks Pete, I'll try this.

**sandy666** · 11-07-2022, 09:33 PM

sure, this is a Power Query solution

Please Login or Register  to view this content.

first you'll need to "import" table from Manager Test file to Test1 file, the rest is above
with Table1 (2) in file Test1 you need to change path to the Manager Test file to your own