Dependent dropdown list problem

Hello,
I created a dependent dropdown list which connect from 1 cell to another using data which contain information on Admin1 ~ Province, Admin2 ~ District, Admin3 ~ SubDistrict and Admin4 ~ Village.
I put all the Admin list in separate worksheet, called "admin"

So far its worked using current formula that I put in Data Validation.
Admin1 =OFFSET(admin!$A$1;1;0;COUNTA(admin!A:A)-1;1)
Admin2 =OFFSET(admin!$F$1;MATCH(B2;admin!$F:$F;0)-1;1;COUNTIF(admin!$F:$F;B2);1)
Admin3 =OFFSET(admin!$L$1;MATCH(C2;admin!$L:$L;0)-1;1;COUNTIF(admin!$L:$L;C2);1)
Admin4 =OFFSET(admin!$R$1;MATCH(D2;admin!$R:$R;0)-1;1;COUNTIF(admin!$R:$R;D2);1)

But recently I found that if Admin3 name in different Admin2 has same name, then List of Admin4 in selected Admin3 will contain list from the first mentioned Admin3.

Case:
Admin4 list from Kalimantan Barat > Landak > Meranti will refer to Admin4 list from Sumatera Utara > Asahan > Meranti as it is coded/mentioned first, instead of Meranti in Landak, Kalimantan Barat.

How to solve this problem?

Lookups are designed to find the first instance of a word. The easiest way would be to identify the duplicate names and place a number after the name. Meranti1, Meranti2, etc. Another way would be to place the lists in different columns and adjust your formulas.

Originally Posted by Squeaky

Lookups are designed to find the first instance of a word. The easiest way would be to identify the duplicate names and place a number after the name. Meranti1, Meranti2, etc. Another way would be to place the lists in different columns and adjust your formulas.

I can't change the name, as its Administrative name.

You have many duplicate items in each list. Orahili has 5 separate entries. What I would do would be to duplicate your admin page, name it admin2, and then set up a formula that counts the instances of names. If the instance is more than 1 then it combines the main name with the sub name. For instance on the ADMIN2 tab, in column m, in m2 put the formula IF(COUNTIF(admin!M:M,admin!M2)>1,admin!L2&" "&admin!M2,admin!M2) If the count is 1 then it just uses the sub name. If the count is more than 1, it combines the category name and the sub-name. So if you go look at the name Telek Dalam you will find Asahan Teluk Dalam and Nias Selatan Telek Dalam. Use these names in your "lookups" so they will find the appropriate sub list. Since they use the actual names in your Admin list, nothing is modified except how the lookup is performed.

I have revised the reference list in "admin" worksheet, and seems one problem is solved and still leave 1 problem in cell who are using VLOOKUP formula.

Screen Shot 2020-10-07 at 2.01.44 PM.png

How to solve the issue in column H?
Current formula for column H only rely on column N and O in worksheet admin, is it possible to consider column M too, to avoid same name in column N (which will cause the mentioned first record will show as result).

Since column B only has two choices, one way would be to put an if statement that looks for one of the choices, then sets the vlookup for that choice. Your "N" list has 414 lines, and the "Kalimantan Barat" starts on line 278. SO if you put
this formula in H2 and drag it down, IF(B2="Kalimantan Barat",VLOOKUP(D2,admin!$N$278:$O$1000,2,FALSE),VLOOKUP(D2,admin!N:O,2,FALSE)) it will start its lookup starting with row 278 when Kalimantan Barat is selected in Admin1. This is a specific mapping of the lookup. It also specifies the size of the range as opposed to all of columns N, O. If your range size changes a lot this may not work well for you. There are ways to dynamically determine the range size but that is getting more complex.