Build a state-to-district dependent dropdown in Excel

Where this sheet is used

Dealer onboarding forms, field visit logs, branch expansion trackers, and warranty registration sheets all ask for state and district. Free typing produces "Gurgaon", "Gurugram", "GGN" and "Gurugram " in the same column, and every regional report after that needs cleaning. A dependent dropdown fixes the problem where it starts.

Step 1: get and prepare the lookup list

The authoritative list of districts is the Local Government Directory of the Ministry of Panchayati Raj, which provides state and district downloads along with LGD codes. Keep the LGD code in a third column if your systems will ever exchange data with government portals; names change, codes are meant to stay.

A downloaded or inherited list is rarely ready to use as-is. The OFFSET formula depends on three things being true: no stray spaces, no duplicate pairs, and every state's rows contiguous. The script below prepares a small illustrative sample, which is deliberately incomplete, and then simulates the Excel formula to prove the list works:

import pandas as pd

# Illustrative, incomplete sample of a state-district master. Use the official list for real work.
master = pd.DataFrame({
    "state": ["Uttar Pradesh", "Haryana", "Uttar Pradesh", "Delhi", "Haryana",
              "uttar pradesh ", "Uttar Pradesh", "Delhi", "Haryana"],
    "district": ["Ghaziabad", "Gurugram", "Gautam Buddha Nagar", "East Delhi", "Faridabad",
                 "Lucknow", "Ghaziabad", "New Delhi", "Gurugram "],
})

# 1. Clean and de-duplicate, then sort by state and district.
#    The OFFSET formula in the sheet only works if each state's rows are contiguous.
lists = (master.apply(lambda c: c.str.strip())
               .assign(state=lambda d: d["state"].str.title())
               .drop_duplicates()
               .sort_values(["state", "district"], ignore_index=True))
print(lists.to_string())

# 2. Simulate the validation formula for one state, exactly as Excel evaluates it:
#    =OFFSET(Lists!$B$1, MATCH(A2, Lists!$A:$A, 0) - 1, 0, COUNTIF(Lists!$A:$A, A2), 1)
col_a = ["state"] + lists["state"].tolist()          # sheet column A, header in row 1
col_b = ["district"] + lists["district"].tolist()

def dropdown_for(state):
    if state not in col_a:
        return []
    start = col_a.index(state)                       # MATCH(...)-1 as a 0-based offset from B1
    height = col_a.count(state)                      # COUNTIF(...)
    return col_b[start:start + height]

print("Haryana ->", dropdown_for("Haryana"))
assert dropdown_for("Haryana") == ["Faridabad", "Gurugram"]
assert dropdown_for("Uttar Pradesh") == ["Gautam Buddha Nagar", "Ghaziabad", "Lucknow"]

# 3. Audit rows typed before the dropdowns existed.
entries = pd.DataFrame({
    "row": [2, 3, 4, 5, 6],
    "state": ["Uttar Pradesh", "Haryana", "Delhi", "Uttar Pradesh", "Haryana"],
    "district": ["Ghaziabad", "Ghaziabad", "New Delhi", "Noida", "Gurgaon"],
})
valid = set(map(tuple, lists[["state", "district"]].to_numpy()))
entries["ok"] = [(s, d) in valid for s, d in zip(entries["state"], entries["district"])]
print(entries[~entries["ok"]].to_string(index=False))
assert entries["ok"].sum() == 2

The cleaning step removes a duplicate Ghaziabad, joins "uttar pradesh " to "Uttar Pradesh", and trims "Gurugram ". Sorting by state and district makes the blocks contiguous. The simulation shows why that matters: MATCH finds the first Haryana row, COUNTIF says there are two, so the dropdown shows exactly Faridabad and Gurugram. If one Haryana row were left at the bottom of the sheet, the block would include a Uttar Pradesh district and miss a Haryana one, with no error to warn you.

Step 2: lay out the sheets

Put the state dropdown on Entry!A2:A500 with Data > Data Validation > List and the source =Lists!$E$2#. In Lists!E2, enter =UNIQUE(Lists!A2:A1000) once. The # refers to the whole spilled result, so a new state appears in the dropdown without editing the validation. On older Excel without dynamic arrays, type the unique states into column E yourself and use =Lists!$E$2:$E$40.

Step 3: the dependent validation

Select Entry!B2:B500, open Data Validation, choose List, and enter:

=OFFSET(Lists!$B$1, MATCH($A2, Lists!$A:$A, 0) - 1, 0, COUNTIF(Lists!$A:$A, $A2), 1)

The reference $A2 has no dollar sign before the row number, so row 3 reads A3 and so on. The formula starts at B1, moves down to the first row of the chosen state, and returns a range as tall as the number of that state's districts. Excel accepts this inside validation because OFFSET returns a range, not an array.

Two behaviours to expect. When column A is blank, MATCH fails and the district cell shows an error when clicked instead of a list; that is acceptable, since the state should be chosen first. And OFFSET is volatile, so it recalculates on every change. On an entry sheet of a few hundred rows this is not noticeable.

Step 4: catch the entries that validation cannot

Data validation checks a value only at the moment it is entered. Pasted values bypass it, rows typed before you added it keep whatever they had, and changing the state leaves the old district in place. Add a check column on Entry:

C2  =IF(OR(A2="", B2=""), "", IF(COUNTIFS(Lists!$A:$A, A2, Lists!$B:$B, B2) > 0, "ok", "CHECK"))

The audit in the script does the same over five old rows. "Ghaziabad" under Haryana was a state change after the district was picked. "Noida" is a city, not a district; the district is Gautam Buddha Nagar. "Gurgaon" is the former name of Gurugram. Each needs a human decision, and a short alias table (old name, current name) handles the renames on future imports.

Keeping it working

Add new districts at the correct place in the sorted list, or re-sort after adding them at the bottom. Keep Lists protected so a well-meaning colleague cannot sort only column B. Record the date of the LGD download in a cell on the sheet. When a state carves out new districts, existing records under the parent district are not automatically wrong, but they may be out of date, so review them rather than bulk-replacing names.

The same clean-reference-list discipline applies to PIN codes; map customer PIN codes to district and state handles that join. Designing input sheets that prevent errors, instead of cleaning them each month, is a recurring theme in the Excel work in our Data Analyst course.