Lookup when the data is in multiple columns (or "If"?)

[SDV]

Hi,

Our employee file has the hierarchy of supervisors, from the CEO down to the first level supervisors, across multiple columns. I have 60+ leaders, representing 30 teams, and supported by 10 HR business partners. I have to map the regional leaders based on the the sequence of supervisors.

These leaders could be at level 2 (Col D in my file) or level 3, 4, 5, 6 or 7.

I need the leaders' names in one column, and I can map those names to the teams they represent, and then map those teams to HR business partners. There can be multiple leaders' names in the same row. For example, I have 2 employees in Sales, 1 having "Gar" as her supervisor, and another having a supervisor in a different region. "Gar" is a regional leader, but the other supervisor is not, so the second employee would roll up to "Bur", who is "Gar"'s supervisor.

To make things worse, these levels change every few months, when any new supervisor is hired or resigns and pops up in the middle of the columns.

I did a lot of manual mapping, but can't maintain the file accurately that way. I've tried my best with Index, Match and Choose, and finally settled on Nested Ifs (Thanks Chandoo, picked that from your blog!). It's not dynamic, so there's still a ton of manual maintenance to do, and I've run out of Ifs to Nest.

What do you folks suggest? Changing the system is not an option, and I have over 3000 employees to map, every month, and I need it this way for the metrics I publish. I would love to create a dynamic formula, but without a single value to lookup or a single column to look in, I'm stuck.

Sorry for the long, complicated question. I look forward to hearing from the gurus here!

 

[NARAYANK991]

Hi ,

I have not understood what you want ; can you give at least a few examples of what kind of output you expect given the data you have ?

All I can make out is the presence of two tables ; how these two tables are related to each other , and what is to be done with these two tables is not clear.

Narayan

 

[SDV]

Hi Narayan,

Sorry, let me try again. The file shows my solution using nested Ifs.

In the Database table, Emp ID, name, and the supervisors, from level 1 to 7- are pulled from a report. I fit the employee to a team and leader, and enter that data in Leader, team and HRBP columns.

The second, smaller table is a list of leaders, teams and HRBPs- the categories I have to fit the raw data into.

I am looking for a formula I can use to identify the leader to whom each employee rolls up.

 

[NARAYANK991]

Hi ,

The logic you have implemented using all the IFs is not clear to me.

What is clear is that the second table can be used to get the Team and the HRBP , once the Regional Leader is known.

See the attached file for this , and confirm whether this is OK. The cells showing the #N/A error values are doing so because the Regional Leader value is not present in the second table ; once you insert the values in that table , the #N/A error values will disappear.

Narayan

 

[SDV]

Thank you for your time and patience!

I need a formula for Regional Leader in my first table (I can definitely use your solution for the Team and HRBP columns). I calculate this information, based on the data in columns C to I.





The If logic is something like this:
If the last level supervisor is a regional leader, pick their name; If not, and the supervisor one level above them is a regional leader, pick their name.
In excel terms-> If (supervisor= regional leader, supervisor, If(supervisor1= regional leader, supervisor 1)).

Is there a way to modify your solution, to look at the list of regional leaders in the small table first? If any match is found in the row for the first employee, show me that match, then move to the next row?

 

[NARAYANK991]

Hi ,

The logic is still not clear.

In the first row , the entries in Supervisor Levels 2 , 3 and 4 are all regional leaders ; so why is it that the entry under Supervisor Level 3 has been chosen as the Regional Leader ?

Narayan

 

[SDV]

Yes, that is a mistake, I should have selected the Level 4 leader. I just pulled together some dummy data, and did not check the file thoroughly. This is the problem with my formula being fixed to a particular value in a particular cell.

 

[SDV]

I'm uploading the file again. I've left the output columns blank (though we have a solution for Team and HRBP). I've added a new column showing what the regional leaders column should display (the output I want).

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[SDV]

This is very close to what I'm looking for. Let me test it and get back to you soon.

 

[SDV]

Hi Narayan,

It works perfectly for me in the sample file, but in the larger database, there is a #REF! error if I consider more than 4 levels in the Index range.

Also what does the "-2" do?

 

[SDV]

Could you help me understand the formula please? I can tweak it for my database.

 

[NARAYANK991]

Hi ,

I am not able to understand the following :

there is a #REF! error if I consider more than 4 levels in the Index range.

Which formula are you talking about , and what is the change you are trying to make ?

The -2 is because the Supervisor Level 1 is in column C ; subtracting 2 from this column number (3) gives us 1 , which will be the starting column.

If your table has the Supervisor Levels starting from column F , then you would subtract 5.

If you can upload a sample file which has the same data layout as your working file , giving a working solution is easier , than if you have to take a suggested solution and then tweak it to make it work in your file.

Narayan

 

[SDV]

Hi Narayan,

Thank you, thank you, thank you, thank you, thank you!! I copied the formula for regional leaders, and changed the "-2" to "-20", since my Supervisor Level 1 starts from Col R. It works beautifully!!

This is a flexible solution to a problem I have been trying to fix for months now. I cannot thank you enough for making my life so much easier!