How to make dropdownt list of unique items dependent on another dropdown list?

This is one way.

With unique Shop names as headers in P:S this array-entered formula returns lists of unique Items associated with each Shop. Array-enter in P4 fill across and down until you get blanks.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Then with K5 the active cell go to Data Validation > Allow: List and put this formula in Source:

Formula:

Please Login or Register  to view this content.

By the way j_analyst I failed to mention, welcome to the forum.

This is another way. Personally I like it better. Additionally one side effect is that it alphabetizes the Items. See what you think.

The setup and drop down formula are the same as in post #2.

The formula for the lookup table is still array-entered.

Formula:

Please Login or Register  to view this content.

Another way would be to make a second filtered list and use an array formula. You'd then use data validation on K5 and list its input range as 04-08

O3 = "Filtered List 2"
04 = {=INDEX($G$4:$H$22,SMALL(IF($G$4:$H$22=$K$4,ROW($G$4:$G$22)),ROW(1:1))-3,2)}
05 = {=INDEX($G$4:$H$22,SMALL(IF($G$4:$H$22=$K$4,ROW($G$4:$G$22)),ROW(2:2))-3,2)}
06 = {=INDEX($G$4:$H$22,SMALL(IF($G$4:$H$22=$K$4,ROW($G$4:$G$22)),ROW(3:3))-3,2)}
07 = {=INDEX($G$4:$H$22,SMALL(IF($G$4:$H$22=$K$4,ROW($G$4:$G$22)),ROW(4:4))-3,2)}
08 = {=INDEX($G$4:$H$22,SMALL(IF($G$4:$H$22=$K$4,ROW($G$4:$G$22)),ROW(5:5))-3,2)}

Originally Posted by j_analyst

Thank you very much for your reply. I tried to replicate your process. And I found a trouble. When I enter the formula into array I get "glue" on every position. I think its because number in this part ROWS($4:4) remains constant even though it should change accordingly.

That happens when you array enter over the entire range at once.

You have to remedy that first.

Pre-select all the problem output.
With U4 the active cell hit the F2 function key.
While in edit mode hold down just Ctrl while hitting enter.
You should see all blank cells ... the effect of IFERROR.

Now select just U4.
Go into edit mode.
Array enter that single cell.
Fill down and across.

Thank you very much I was able to aplicate your process to my bigger project and it works perfectly.
EDIT: Solved

You are welcome. I am glad to hear it does the job. Thank you for the feedback, marking your thread SOLVED and of course the rep.