How to Return a Postcode Region with an Excel Lookup Formula in WPS Office
Question details
The user needs an Excel lookup formula that returns the correct postcode region when postcodes contain one or two letters followed by numbers and the postcode lists are stored under region headers in another worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a lookup worksheet where postcode values are grouped under region headers and wants to search those values from a data worksheet.
- Observed behavior
- Postcode formats vary, so a fixed number of characters cannot reliably identify the matching region.
Before building the formula, confirm that LET, XLOOKUP, and BYCOL are available in your WPS Office version and that the region lookup sheet stores each region header above or beside its postcode list in a consistent layout.
Search normalized postcodes with LET, XLOOKUP, and BYCOL to return the region header
Use this modern formula when your WPS Spreadsheets version supports LET, XLOOKUP, and BYCOL and you want the correct region header returned automatically.
Because postcodes may have one or two leading letters, a fixed-length approach fails. This formula normalizes the lookup postcode and scans each region column with BYCOL, then XLOOKUP returns the header of the first column that contains the normalized postcode.
In WPS Spreadsheets, open the workbook that contains your postcode data and click the cell where you want the postcode region to appear.
Enter a formula such as =LET(p,SUBSTITUTE(A2," ",""),rng,RegionSheet!$B$2:$D$100,hdrs,RegionSheet!$B$1:$D$1,XLOOKUP(TRUE,BYCOL(rng,LAMBDA(c,ISNUMBER(XMATCH(p,SUBSTITUTE(c," ",""))))),hdrs,"Not found")) and adjust A2, rng, and hdrs to your actual layout.
Press Enter, verify that the cell returns the expected region, then use the fill handle to copy the formula to the remaining postcode rows.
Return Postcode Regions in WPS Spreadsheets with LET, XLOOKUP, and BYCOL
WPS Spreadsheets can directly handle this task. Build a single LET, XLOOKUP, and BYCOL formula to normalize postcodes, scan the region columns, and return the correct region header.
- 1. Download and install WPS Office: Go to the official WPS Office download page, download WPS Office, and open your existing workbook in WPS Spreadsheets.
- 2. Open the region lookup workbook: Click Menu > Open, select the Excel workbook that contains your postcode data and region lookup sheet.
- 3. Enter the lookup formula: In the result cell, enter the LET/XLOOKUP/BYCOL formula and adjust the region range and header range to match your sheet layout.
- 4. Verify and save: Press Enter, confirm the returned region, fill down the formula for the remaining rows, and save the file as an .xlsx workbook if you need to share it with Excel users.
Frequently Asked Questions
Why does a fixed character count fail for UK postcodes?
Postcodes can start with one or two letters, such as B1 and AB1. Extracting a fixed number of characters may split or miss the correct prefix, so normalize the postcode and match it against each region list instead.
Can I use this formula if my postcode lists are under region headers in different columns?
Yes. Set the lookup range to the postcode list area below the headers and set the header range to the region header row. BYCOL then scans each column separately and returns the matching header.
What should I do if my WPS Office version does not support LET or XLOOKUP?
Check for updates in WPS Office to get the latest formula support, or adapt the task with a legacy INDEX, MATCH, and SUMPRODUCT array formula for your version.
Does the formula work when postcode values contain spaces or lowercase letters?
The sample formula removes spaces with SUBSTITUTE. If your source data is inconsistent, wrap the lookup postcode and column values in UPPER or TRIM to normalize case and extra spaces.
What does the formula return if no postcode matches any region?
The XLOOKUP formula can include a "Not found" argument, so the result cell explicitly shows "Not found" instead of returning an #N/A error.
