WPS Spreadsheets Formula to Assign Zones from Postal Code Prefixes
Question details
The user needs a formula that assigns one of five zones from the first two digits of a five-digit postal code.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A postal code prefix must be matched to a zone using listed prefix ranges or a lookup table.
- Observed behavior
- The zone definitions contain overlapping ranges and duplicate prefixes, so some postal codes currently produce more than one possible zone.
Review the zone definitions and decide which zone owns every two-digit prefix, especially duplicated values such as 49 and 75 and ranges that overlap. Store postal codes as five-digit text or format the input cells as 00000 so prefixes beginning with zero are preserved.
Resolve the prefixes and use an XLOOKUP zone table
Use this recommended approach when the zone rules can be clarified and you want a maintainable formula that returns one zone for each prefix.
Create one row for every valid two-digit prefix rather than matching directly against overlapping ranges. The lookup table must contain only one assigned zone per prefix; a formula cannot determine the correct result while the source definitions remain contradictory.
In WPS Spreadsheets, enter each valid two-digit prefix in E2:E101 and its approved zone in F2:F101. For example, enter 49 once with its final zone assignment. Add headers such as Prefix and Zone.
Place the five-digit postal code in A2. If it may contain leading zeros, select the input cells, open Home > Number Format, and apply a custom format of 00000.
Use TEXT and LEFT to create a two-digit lookup key. The expression VALUE(LEFT(TEXT(A2,"00000"),2)) converts the prefix to a number while preserving leading zeros during extraction.
In the result cell, enter =IFERROR(XLOOKUP(VALUE(LEFT(TEXT(A2,"00000"),2)),$E$2:$E$101,$F$2:$F$101,"Not found"),"Not found") and press Enter.
Drag the fill handle down the result column or copy and paste the formula for the remaining postal codes. Check any Not found result against the prefix map.
Use INDEX and MATCH in older WPS Spreadsheets versions
Use this compatible alternative when XLOOKUP is unavailable or when the workbook must use traditional lookup functions.
Create a reliable postal-prefix zone lookup in WPS Spreadsheets
WPS Spreadsheets can build the prefix map, preserve five-digit postal codes, and use XLOOKUP or INDEX and MATCH to return a single zone. Resolve overlapping assignments first, then copy the formula through the dataset.
- 1. Open a spreadsheet: Launch WPS Office, choose Spreadsheet, and open an existing workbook or select Blank to create a new one.
- 2. Build the prefix map: Enter the approved two-digit prefixes and their zones in two columns, ensuring each prefix appears only once.
- 3. Enter the lookup formula: Select the output cell and use XLOOKUP with LEFT(TEXT(postal_code,"00000"),2), or use INDEX and MATCH for older versions.
- 4. Verify and fill results: Check Not found values and copy the completed formula down the postal-code list.
Frequently Asked Questions
Why can’t one formula choose the correct zone from overlapping ranges?
A formula can return only one result based on its matching logic, but overlapping ranges make multiple zones valid for the same prefix. Resolve the business rule first and record one final zone for each two-digit prefix.
How do I preserve a postal code that starts with zero?
Select the postal-code cells, open Home > Number Format, and apply the custom format 00000. The formula TEXT(A2,"00000") also ensures that LEFT extracts two digits, including a leading zero.
What should the lookup table contain?
Use one row for every valid two-digit prefix and one corresponding zone column. Do not include duplicate prefixes unless another field, such as region or city, is included to make the match unique.
What formula works if XLOOKUP is not available?
Use =IFERROR(INDEX($F$2:$F$101,MATCH(VALUE(LEFT(TEXT(A2,"00000"),2)),$E$2:$E$101,0)),"Not found"). Adjust the ranges to match your prefix map.
How can I identify missing or ambiguous prefixes?
Compare the complete list of possible prefixes from 00 through 99 with the approved map. Review prefixes that are missing, duplicated, or assigned to conflicting ranges before relying on the zone results.
