Dynamic lookup using data from multiple dropdowns

[scaffdog845]

Hello. I have a workbook with three sheets, Sub List, Approved Contractors and Legend. On my subs sheet, I have two dropdowns in A2:A7 and C2:C7. Column A dictates what type of sub contractor is needed and column C provides a list of appropriate contractors. I have named ranges that I would like to do lookups in to populate D:G based on the chosen contractors. The named range of the contractor being looked up would be the type craft name immediately followed by a number. In the attached example I supplied the expected results in D3:G3 based on "National Surface Cleaning" being the chosen sub in C3. I thought I could make something work using Indirect and Match along with Concat and text to add A2 and the number 2 together for determining the named range, but everything I try seems to error out.

[AliGW]

Are you still using Excel 2007? If not, please update your forum profile. Thanks.

https://trumpexcel.com/dependent-dro...list-in-excel/
https://exceljet.net/dependent-dropdown-lists
https://www.youtube.com/watch?v=7mo4COng7Sg
https://www.contextures.com/xldataval02.html
https://www.ablebits.com/office-addi...op-down-excel/

... and others.

[scaffdog845]

My profile is updated AliGW.

[AliGW]

Thanks.

This question has been asked multiple times here - if you search the forums you will find many threads on dynamic and dependent drop-downs. I have provided five links to online tutorials - there are many more.

[Glenn Kennedy]

There was something wrong with the DD's in column C. Now fixed. It's better to have the corresponding DDs in the same row. Fixed.

Then use, in D2, copied across and down:

=IFERROR(VLOOKUP($C2,'Approved Contractors'!$A:$E,1+COLUMNS($D2:D2),FALSE),"")


amend in column G:

=IFERROR(VLOOKUP($C2,'Approved Contractors'!$A:$E,1+COLUMNS($D2:G2),FALSE)&"","")

to get rid of annoying 0s.

[scaffdog845]

Thanks AliGW i found what I was trying to do in the second link y9ou provided.

[AliGW]

Glad to help. I think Glenn found your mistakes, too.

You may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who helped.

[scaffdog845]

Thanks a lot Glenn. These formulas work great!

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.