Introduction

Vietnam is having 63 provinces and associated districts, wards subdivisions. Yet, manually updating this data (example: opening, disabling, adding or renaming locations) is error-prone and time-consuming.

This blog shares a simple, Excel-friendly method to streamline these processes, even for non-technical teams.

Challenge: Why Administrative Updates Are Painful

Vietnam’s three-tier administrative system (province → district → ward) forces teams to juggle three columns of data. Common pain points include:

• Manual Lookups: Non-tech staff waste hours cross-referencing case-by-case (I used to be like that).
• Complex Formulas: Nested VLOOKUPs or INDEX/MATCH to handle three columns.
• Human Error: Typos or mismatched entries lead to incorrect updates.

Solution: Simplify the approach

Initial Step: Create a Unique Key

Instead of treating province, district, and ward as separate columns, combine them into a single unique identifier.

Let’s concatenate the three columns into one string. For example:

• Current Data: = A2 & "," & B2 & "," & C2
• New Data: Same logic for updated datasets.

How It Solves Real-World Scenarios

Opening/Disabling Locations

Problem: Set a specific province-district-ward combo to be active/ inactive based on the given updated list.

Solution:

• Concatenate the target location
• Use VLOOKUP to check this key against your “active” or “inactive” from the given updated list.

Example from given image:

Suppose your original entry has combine_full = "tp. hồ chí minh,bình chánh,ba điểm". VLOOKUP searches for this exact string in the updated list. If found, it returns the corresponding status.

With VLOOKUP, updating administrative statuses becomes a quick, automated process—no manual searching required.

Adding New Locations

Problem: Is this new province-district-ward combo already in our system?

Solution:

• Concatenate the new entry (e.g., tp. hồ chí minh,bình chánh,ba điểm).
• VLOOKUP this key against your existing database:

=IF(ISNA(VLOOKUP(New_Key, Existing_Keys, 1, FALSE)), "Add", "Exists")  

Result: Flag duplicates and avoid redundant entries.

Renaming Locations

Problem: A ward’s name changed. How do we update records efficiently without manual work case-by-case?

Solution:

• Concatenate old entry (with incorrect ward)
• Prepare new data entry, contain both correct & in correct ward.
• Concatenate new entry (with incorrect ward)
• Compare keys to map old → new names.

Formula:

=IFERROR(VLOOKUP([@combine_full], UpdatedListRange, 5, FALSE), [@[correct ward]])  

Example

• Current Entry:
  combine_full = "tp. hồ chí minh,bình chánh,bà điểm"

  • VLOOKUP searches the Updated List for this key.

  • If not found, it keeps the original correct ward ("no update").

• Updated Entry:
  combine_full = "tp. hồ chí minh,bình thạnh,21"

  • VLOOKUP finds a match and updates the correct ward to "19".

Best Practices for Success

• Standardize Formats: For dynamic workflows, remember to conduct data cleanup to standardize text formats before using VLOOKUP.
• Maintain a Master List: Keep a centralized database of all valid province-district-ward keys.

Conclusion

By treating province-district-ward as a single unique key, teams can cut almost manual work and eliminate errors. Whether you’re opening stores, updating customer records or complying with regulatory changes, this method ensures accuracy and saves time.

Ready to simplify? Share this approach with your team and build a template today!