Dependent Dropdown list With Filter Function

[Nevermore91]

Hello everyone,

I have been trying to create a database that includes dependent dropdown lists from a set of data. The data that I have consists of 2 columns vendors & merchandise. 
Column A | Column B

A.                Apple

A.                Orange

B                 Pen

C                 PC

C                 Laptop

where column A is the vendor list, and column B show the merchandise which the vendor sells.

In the database, I have a list that shows the 3 vendors above (A, B, C). I need another dependent dropdown list that shows the merchandises in column B depending on the vendor. So, if I choose A, Apple and orange will show up only. I tried to use filter, and it works fine but the issue is that I can use it only if I have one row not multiple ones. I tried to use Xlookup but it doesn't return a list/array. 

the row data cannot be modified otherwise I would have used offset and changed the list of vendors to column headers. 

[#2 · Accepted solution]

@Nevermore91 See attached. Some tricks are needed but I trust you will be able to replicate in with your real data.

[#3]

That works so well, but can I have a way where I don't change the row data at all?
thanks a lot tho

[#4]

@Nevermore91 

Sorry but I don't understand. What row data is it that you don't want to change?

[#5]

@Riny_van_Eekelen I don't want to transpose the data. I want to work on column A and B as is without transposing them. Also, in your provided example, I will have an issue if a 4th vendor is added. I will have to update the range in the xlookup from F3 to F4 and go through everything again which does not help unless if there is a workaround that.

[#6]

@Nevermore91 To avoid having to manually add new entries you can point the XLOOKUP at entire columns, but preferably to a large enough range, let's say 10000 rows. Assuming that you will not have more than 10000 vendors. The formula in E will spill down automatically if you add more vendors. Then drag the formula I have in F far enough down.

Change the DV formula to =XLOOKUP(B11,$E$1:$E$10000,$F$1:$F$10000)# and it will work just the same. In case you prefer entire columns us this: =XLOOKUP(B11,$E:$E,$F:$F)#

Still don't understand your problem with transposing data. What does it matter? You can even put the formulas in a different sheet so that they disturb any format/lay-out. But you need some way to create the lists that source the data validation.

[#7]

@Riny_van_Eekelen you are awesome bro. it worked well now. thank you so much.

my issue with the transpose is that the file I'm working on is a general file; multiple users use it and I did not want to add new sheets/format to it. but if that is the only solution to it, then so be it. thanks a lot again.