Drop List of One Column Based on Matches in a Second Column

[Revision]

Hi!

I'm brand new here and newish to Excel. I have a question that has to do with creating drop down lists from data in two columns.

I have a master list of light fixtures that are referenced by type in column1 in (includes: 2x4 Troffer, 2x2 Troffer, Stip Fixture.....). In column 2 I have the lumen output of the fixture (2000, 3000, 4000, 16000......). In column 3-10 I have fixture specific information.

On a new sheet i have a drop down list filled with the available fixture types. I'd like a second drop down list that is populated by all of the lumen values that match the fixture type. For instance a 2x4 troffer may have 2000, 3000 and 4000 lumen options but a strip fixture may have 5000, 6000, and 7000 lumen options. So if I select 2x4 troffer in a list I'd like the second list to have 2000, 3000, 4000 as options. I need to pull this data from two columns. There is a snip of master fixture list below. In the real life sheet the information is in column A and column I.

The end goal is to select fixture type and lumen output from two drop down lists and return fixture specific information based on the two selections.

If anyone could shine some light (hehe) on how to accomplish this it would be greatly appreciated!

Capture.PNG

[Estevaoba]

Hi!
Please attach a sample workbook (no confidential data) with enough representative data for your search.
To attach a file, tap Go Advanced, then Manage attachments, Upload files from your computer, Upload.

[Revision]

I edited the post and added the attachment. It's a major work in progress so please no judging too hard.

[Estevaoba]

In new sheet "Test", I created two dynamic drop lists:

In B2, you type part (or all) of fixture type title and items containing your typing will be listed.
Once an item is selected, C2 will list corresponding lumen values.
Once both B2 and C2 are selected, item full information will show in row 6.

Not sure this is what you are trying to accomplish, so please check if it will work.

Godspeed!

[Revision]

Thank you for your reply! It's not quite what I'm looking to do. I'm going to try and explain my thought process here. There may be better ways to do it but this is what I've come up with so far.

1. Excel File 1: Master fixture list with lots of fixture types, models and lumen outputs.
2. Excel File 2: LED tool that has all of the math. (the one I attached)
3. Link Excel 1 into Excel 2.
4. Enter desired fixture type and lumen package into excel 2 --> searches excel 1 for all model numbers and lumen packages that match --> output model to a dropdown list.
5. Once fixture model is selected from dropdown list --> return all power, cost, etc. values of the model number.

I have it working but the issue is getting the lumen package selection into a dropdown list. I have a table filled with fixture types that a drop list is made from. The hinge point is getting the single dropdown list to populate with all of the available lumen package's that correspond to that fixture type. I'm going to have multiple line items so i don't think a dynamic table will work.

If you look at the tab "new fixtures" row 3 will have "2x4 troffer" and row 4 could have "4' strip". Row 5, 6 and 7 could have "high bay". So what I'd like to do is have a data validation equation/function which goes out and finds all lumen packages without populating a separate dynamic table each time.

Is that something that I can do? Though while writing this I did think that I could have the the lumen packages populate horizontally across the fixture type table and make a dynamic list from that...

[Estevaoba]

Please build manually a mockup table of what you have in mind will look like.
Then I'll start playing with formulae.