Auto-populate contacts list with multiple choices

[Lydia915]

Help Me - Contact List 

I need help with auto-populating my contact list. I have tabs for master consultants and trades lists. Then, a tab for the working project list. I am trying to auto-populate fields on the working project list.

The problem lies where I have multiple contacts in a specific field, such as electrical engineer or flooring company. With the formula that I am using currently, it will only auto-populate the first person on the list. I'd like the option of being able to choose from the multiple company choices I have, but only when necessary. I like that I can auto-populate "architect" with a single button, as there is only one architect that we work with. But I don't necessarily want to add dependent drop down lists to every row if I don't have to. I hope that makes sense.

I am really new to excel, and searching Google and Youtube is failing me at this point, please help me! Thanks.

[#2]

@Lydia915 

It's difficult to combine a formula and a variable drop-down list; it would require VBA code.

Here is a solution with a data validation drop-down in column C and formulas in column D (spilling to E and F)

[#3]

@HansVogelaar 

Thank you for your solution. I was hoping to avoid multiple dependent drop down lists, but it seems that may be best way to solve this. 

I did forget to mention that there is a possibility that a company may have multiple contacts. For example, the same flooring company could have a project lead and the principal. Because of this possibility, I am assuming that I will require 3 drop down lists (category, company name, and contact name).

With this situation in mind, it's a lot of drop down lists to choose from. Although, it is better than typing it out each time or copy/paste, as that leaves more room for error. I am just trying to make it as easy as possible for out team, and am not entirely convinced that selecting from 3 drop down lists to get the full information per contact is the right way to go. 

Any suggestions for alternative solutions?

[#4]

https://techcommunity.microsoft.com/t5/excel/drop-down-list-selection/m-p/4023603#M216614