Dropdown List with Multi-Criteria in Excel

Drop Dow List with Multi-Criteria in Excel
There is no easy way to do this, but if you know this, it will be easier for you. Below is the list of provinces in Cambodia and the Operational Districts (OD).

You want to select a province, and then OD name can be selected like below, don't you?

There are many steps as below:

Step 1: Select all provinces => Naming cells (2) =>

Step 2:  Naming all ODs /Criteria​ by different short name.

Step 3: Create table for dropdown List in new worksheet.

Step 4: Naming the formula

·         => Formulas => Define Name =OD => using the follow formula.

=IF(List!C5=OD!$B$1,BMC,IF(List!C5=OD!$C$1,BTB,IF(List!C5=OD!$D$1,KgCham,IF(List!C5=OD!$E$1,KgChhnang,IF(List!C5=OD!$F$1,KgSpeu,IF(List!C5=OD!$G$1,KgThom,IF(List!C5=OD!$H$1,KPT,IF(List!C5=OD!$I$1,Kandal,IF(List!C5=OD!$J$1,KK,IF(List!C5=OD!$K$1,KEP,IF(List!C5=OD!$L$1,Kratie,IF(List!C5=OD!$M$1,MDK,IF(List!C5=OD!$N$1,OMC,IF(List!C5=OD!$O$1,Pailin,IF(List!C5=OD!$P$1,PP,IF(List!C5=OD!$Q$1,PVH,IF(List!C5=OD!$R$1,PV,IF(List!C5=OD!$S$1,PST,IF(List!C5=OD!$T$1,RTK,IF(List!C5=OD!$U$1,SR,IF(List!C5=OD!$V$1,SHV,IF(List!C5=OD!$W$1,ST,IF(List!C5=OD!$X$1,SV,IF(List!C5=OD!$Y$1,Takeo)))))))))))))))))))))))) 

Step 5: Data Validation for Province column

·         Select Cells (under Provinces )=> Data => Data Validation => Data Validation => Setting => Data Validation =List => Source = Province

·         Select a cell (under ODs )=> Data => Data Validation => Data Validation => Setting => Data Validation =List => Source =OD

Note: please select only a cell under ODs (D5) for data validation, otherwise, it will not work well. Then you can copy (D5) and paste to other below cells.

=> Download Exercise