Help with Dependant Validation

[notricksnowikipedia]

Hello,

I'm trying to create a data validation list that searches through a table and includes only certain items. The table has a "Role" column, a "Firm" Column, an a "Contact" column. I would want a validation list with "Firm" options that includes all firms in the table for a specific "Role". Then a second dependent validation list that looks at the "Firm" selection and then references the table for all contacts at that firm.

I've attached a sample excel file that sort of shows what i'm trying to do. Is there a way to do this without a bunch of helper tables and named ranges? ideally it would just be a Filter() statement inside of the data validation formula, but that doesn't seem to work.

Sample.xlsx

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[notricksnowikipedia]

Sure thing. I edited my original post to include an attachment.

[AliGW]

In F3 copied down:

=TRANSPOSE(FILTER($N$3:$N$19,$M$3:$M$19=TRIM(B3)))

TRIM because you have some trailing spaces in this table.

You need to have enough columns empty to the right of the table to accommodate the maximum number of results - these columns can be hidden.

DV rule for C3:C9:

=F3#

In D3 copied down:

=IFERROR(FILTER($O$3:$O$19,($M$3:$M$19=B3)*($N$3:$N$19=C3)),"")