Incremental reference within nested IFERROR(VLOOKUP... formula

[elah201]

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:

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!

 

[Marcelo Branco]

Maybe a recursive funcion

Sheet2

	A​	B​	C​
1​	Staff number​	Name​	Cost centre​
2​	789​	Bob Jenkins​	Finance​
3​	911​	Mike​	HR​
4​	999​	Richard​	Ops​

Alt+F11 to open the VBEditor
Menu
Insert > Module

Copy the code below and Paste in the right panel

Function FindCentreMng(lEmployee As Long, r1 As Range, r2 As Range)
    Dim lEmplManager As Long, rFound As Range, theMng As Variant
    
    lEmplManager = Application.Index(r2, Application.Match(lEmployee, r1, 0))
            
    theMng = Application.Index(Sheets("Sheet2").Columns(2), _
            Application.Match(lEmplManager, Sheets("Sheet2").Columns(1), 0))
    
    If Not IsError(theMng) Then
        FindCentreMng = theMng
    Else
        FindCentreMng = FindCentreMng(lEmplManager, r1, r2)
    End If
    
End Function

Back to Excel

Sheet1

	A​	B​	C​
1​	Employee​	Manager​	Centre Manager​
2​	123​	456​	Bob Jenkins​
3​	124​	345​	Richard​
4​	456​	789​	Bob Jenkins​
5​	345​	883​	Richard​
6​	883​	999​	Richard​
7​	777​	778​	Mike​
8​	778​	911​	Mike​

Formula in C2 copied down
=FindCentreMng(A2,A:A,B:B)

Hope this helps

M.

 

[elah201]

Perfect, that's exactly what I need. Thanks so much!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.