Hi, I've tried searching on this issue but can't find exactly what I'm looking for, so was wondering if anyone would be able to help me.
Currently I have a file containing 2 sheets.
Sheet 1 contains a list of staff numbers of employees and their managers:
<tbody>
</tbody>
Sheet 2 contains a list of cost centre managers (including their staff numbers):
<tbody>
</tbody>
Not all managers are cost centre managers, so an employee's cost centre manager could be 2, 3 or more levels above them in the organisational hierarchy.
What I need is a formula to be added to Sheet 1 which will return the cost centre manager for each employee, i.e. to look up the employee's manager's staff number against the list on Sheet 2, and if there is no match, to look up THEIR manager's staff number, and so on until there is a cost centre manager match. In this example I would expect to see the following result:
<tbody>
</tbody>
So far the best method I have come up with is to add an additional sheet to build out the organisational hierarchy using VLOOKUP:
<tbody>
</tbody>
Then I add a nested IFERROR/VLOOKUP formula to Sheet 1, which takes the employee's staff number, looks up their manager in the Hierarchy sheet, and then looks THAT up against the cost centre manager list. If there is no match it repeats the process, moving one column to the right on the Hierarchy sheet each time:
=IFERROR((VLOOKUP(VLOOKUP(A2,Hierarchy!A2:C5,2,FALSE),Cost centre managers!A2:B6,2,FALSE)),IFERROR((VLOOKUP(VLOOKUP(A2,Hierarchy!A2:C5,3,FALSE),Cost centre managers!A2:B6,2,FALSE)),IFERROR.....
My questions are as follows:
- Is there a way I can simplify the formula above so that if there is no match it will increment the Hierarchy lookup column reference (in bold above), without me having to write this out manually? I think this could involve COLUMN and/or OFFSET but I can't quite figure out exactly how to use them here.
- Better still, is there a way I can remove the need for the additional Hierarchy sheet altogether, and have all of the workings contained within Sheet 1?
Thanks in advance for any light you can shed on the matter!
Currently I have a file containing 2 sheets.
Sheet 1 contains a list of staff numbers of employees and their managers:
Employee | Manager |
123 | 456 |
456 | 789 |
<tbody>
</tbody>
Sheet 2 contains a list of cost centre managers (including their staff numbers):
Staff number | Name | Cost centre |
789 | Bob Jenkins | Finance |
<tbody>
</tbody>
Not all managers are cost centre managers, so an employee's cost centre manager could be 2, 3 or more levels above them in the organisational hierarchy.
What I need is a formula to be added to Sheet 1 which will return the cost centre manager for each employee, i.e. to look up the employee's manager's staff number against the list on Sheet 2, and if there is no match, to look up THEIR manager's staff number, and so on until there is a cost centre manager match. In this example I would expect to see the following result:
Employee | Manager | Cost centre manager |
123 | 456 | Bob Jenkins |
456 | 789 | Bob Jenkins |
<tbody>
</tbody>
So far the best method I have come up with is to add an additional sheet to build out the organisational hierarchy using VLOOKUP:
Staff number | Manager Level 1 | Manager Level 2 |
123 | 456 | 789 |
<tbody>
</tbody>
Then I add a nested IFERROR/VLOOKUP formula to Sheet 1, which takes the employee's staff number, looks up their manager in the Hierarchy sheet, and then looks THAT up against the cost centre manager list. If there is no match it repeats the process, moving one column to the right on the Hierarchy sheet each time:
=IFERROR((VLOOKUP(VLOOKUP(A2,Hierarchy!A2:C5,2,FALSE),Cost centre managers!A2:B6,2,FALSE)),IFERROR((VLOOKUP(VLOOKUP(A2,Hierarchy!A2:C5,3,FALSE),Cost centre managers!A2:B6,2,FALSE)),IFERROR.....
My questions are as follows:
- Is there a way I can simplify the formula above so that if there is no match it will increment the Hierarchy lookup column reference (in bold above), without me having to write this out manually? I think this could involve COLUMN and/or OFFSET but I can't quite figure out exactly how to use them here.
- Better still, is there a way I can remove the need for the additional Hierarchy sheet altogether, and have all of the workings contained within Sheet 1?
Thanks in advance for any light you can shed on the matter!