CODEWITHSUNDEEP
excelvlookup
Mantas191
Mantas191

Reputation: 3

Excel - VLookup to return values in dropdown list

I am currently trying to display some manager/employee names based on business unit.

Each Business Unit could have multiple managers and multiple employees.

My question is can VLookup or any other method return a drop down list to select a certain manager/employee based on the Business unit selected?

Please see image below to see the layout and expected output.

I am hoping to use 3 drop down menus which when the business unit is selected to be able to auto populate first employee and manager in the list but also be able to have drop down menu for both to select other employees/managers etc.

Thank you.

screenshots

screenshots

screenshots

Upvotes: 0

Views: 281

Answers (1)

Jario
Jario

Reputation: 42

Please find my Excel-sheet in which I modulated a possible solution via the following link (shared on OneDrive): https://cronos-my.sharepoint.com/:f:/g/personal/oortsja_cronos_be/EuUIF6pW95xGtcA0gQjwtIkB_x4LCc8oWks9VwoVTfrhJA?e=7fO6Dz

To summarize how I got to this solution:

I made different tables based on the data you provided (Business Unit > Manager > Employee). Using Name manager (see example), I gave those tables specific names that relate to their respective Business Unit > Manager > Employee.

Using =INDIRECT(), I reference those tables based on the names I gave them. E.g. table Ireland (Business Unit) contains values "John" and "Keith". Based on that output, using =INDIRECT(), "John" for example references table John (Manager) which contains the value "Mary" (Employee)

Basically, in my solution the key is using =INDIRECT() referencing multiple tables, VLOOKUP doesn't suit your needs in this specific case.

Upvotes: 0

Related Questions