Need FULL drop down list using INDEX, INDIRECT and MATCH, not just first match

Hi

Although i can find threads similar to my situation, I can't find one that gives me enough to solve my issue on my own and so could do with someones help please...

Simplest way to explain is giving you the attached example...as i can't post links yet

in essence, there are three drop down lists. The first is a simple list, "style".
The second is a "category" and the Third needs to be a list based on the chosen "category" AND the chosen "style" from the first drop down list.

I've managed to get it working using INDEX, INDIRECT and MATCH (ignore the SUBSTITUTE, that just strips spaces) but it is only returning the first matching cell, not the whole list...and this is my problem; I need the whole list.

Any help would be amazing, thank you in advance!

Your tables are not in a fomat that is friendly with the task you are attempting.

Have a look at the link below, this is the best guide I have found for creating dependent validation lists.

https://www.contextures.com/xlDataVal02.html

Thanks Jason, but now i'm more confused than ever...this is the guide i followed, and as far as i know exactly how my tables, named ranges and list are set up.

that's why I thought my drop down's worked, with the exception it only returns one result instead of all results

You need 2 tables on the kitchen units sheet, 1 for MewsSemi and another for Detatched, they can't be mixed in the same table.

Thanks.

it's given me somewhere to start, but your example fails if i switch to detached (in style) instead of MewsSemi, and i am also concerned splitting it out like that will make ongoing maintenance of the kitchen choices/options more difficult, prone to error and time consuming (even repetitive), which is the only reason i was trying to do it the way i was.

more thought then if it can't be done the original way

That was my error, mis-spelling detached in the table name.

You will not be able to do it with the mixed table, it would need complex formulas that cannot be used in data validation.

The only thing that might work would be to have a third table that fills based on the selections made in the dropdowns.

Something like this.

I've added an extra sheet which uses array formulas (in the green cells) to create a new list based on the options selected in the first 2 dropdowns. These selections are mirrored in B1 and B2 of the extra sheet for clarity.

I've also changed the formula in the range dropdown to =OFFSET(Range,0,0,COUNTIF(Range,"?*"),1) which eliminates the blanks.

Fantastic, thanks