Looking up Multiple Pieces of Data to Identify a Value

Hi All,

I am new to this role and have inherited a report for our organisation, where we have Permanent and External employees.

The report is for members of the Executive team who are responsible for areas known as Management Groups, so I'm trying to present the data as simply as possible (pivot tables and pivot graphs)

The report has a column which lists permanent employees' Management Groups, however for External employees, this field is blank.

I would like some help in creating a formula that sits on the end of the table and performs 2 tasks:

1. Identifies the Direct Report of the External employee

2. Identifies the Direct Report's Management Group

3. Documents the employee's Management Group (regardless of whether they are permanent or an external employee)

ie:
"Look up which Management Group the employee's Direct Report is in, and place it in this (new) Cell"

Many thanks for your help.

Adelaidian