How can i create 3 dependent drop down list with the 3rd drop down depending on 1st and 2nd list?

[CelineFoong]

Here is my list of data. For Dropdown3, I will want the dropdown to be dependent on BOTH Dropdown1 and Dropdown2. Kindly note that some of the values within Order Information/Shipping Information under Proforma Invoice/Draft Shipping docs/Final Shipping docs can be the same and it can be different too . As such I will need the 3rd dropdown list to be dependent on BOTH Dropdown 1 and Dropdown2

Dropdown1	Dropdown2	Dropdown3
Proforma Invoice	Order information	Customer requirement/basic information
Proforma Invoice	Order information	Payment terms
Proforma Invoice	Order information	PO number / Material information
Proforma Invoice	Order information	Price
Proforma Invoice	Order information	Quantity
Proforma Invoice	Order information	Vendor information
Proforma Invoice	Shipping information	Date discrepancy
Draft shipping docs	Order information	Customer requirement/basic information
Draft shipping docs	Order information	Payment terms
Draft shipping docs	Order information	PO number / Material information
Draft shipping docs	Order information	Price
Draft shipping docs	Order information	Quantity
Draft shipping docs	Order information	Vendor information
Draft shipping docs	Shipping information	Carton information
Draft shipping docs	Shipping information	Container information
Draft shipping docs	Shipping information	Date discrepancy
Final shipping docs	Order information	Customer requirement/basic information
Final shipping docs	Order information	Payment terms
Final shipping docs	Order information	PO number / Material information
Final shipping docs	Order information	Price
Final shipping docs	Order information	Quantity
Final shipping docs	Order information	Vendor information
Final shipping docs	Shipping information	Carton information
Final shipping docs	Shipping information	Container information
Final shipping docs	Shipping information	Date discrepancy
Final shipping docs	Shipping information	EEM - GAC/VSD accuracy

[#2 · Accepted solution]

@CelineFoong 

See the attached sample workbook.

Select C2 on the Data Entry sheet and click Data Validation on the Data tab of the ribbon to see the formula I used. It is

=OFFSET(Lists!$C$1,MATCH(1,(Lists!$A$2:$A$27=A2)*(Lists!$B$2:$B$27=B2),0),0,COUNTIFS(Lists!$A$2:$A$27,A2,Lists!$B$2:$B$27,B2),1)

[#3]

@Hans Vogelaar 

Appreciate your great help and reply.

I tried at my end using the formula you provided and it works  

Thank you for the great help , this formula really save alot of time in my work

Happy Weekend

Celine

[#4]

@Hans Vogelaar 

It looks like it is exactly I have been looking for, but for some reason your Dropdown3 is not working.

Could you please let me know how to fix it?

Thanks a lot!

[#5]

@ChristianDeinzer 

It looks like data validation has to be refreshed when the workbook is opened.

The attached new version is now a macro-enabled workbook; you may have to allow macros when you open it. I hope this works for you.

[#6]

@Hans Vogelaar 

Thanks for your help and reply.

I am looking not to use VBA.

Please have a look on the file and see if you can help me.

https://1drv.ms/x/s!AtcEIJC2JdswgzXG9djJJgczPkYX?e=7slZzb

Thanks!!

[#7]

@ChristianDeinzer 

Unfortunately, we cannot use functions such as SORT or UNIQUE directly in data validation. I don't see a solution without using VBA.

[#8]

No problem!
Thanks again!