How to Assign a Day from an Address Using an Excel Lookup Formula in WPS Office
Question details
The user needs to assign a weekday to each address based on the street name using an Excel lookup formula, and the result must update when an address changes.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user maintains an address list and wants each row to show the scheduled weekday according to the street name.
- Observed behavior
- A formula placed in the weekday cell overwrites any manually entered value, so manually typed weekdays cannot remain in the same cell while also using a lookup formula.
Before entering formulas, create a separate street-to-day mapping table with one street per row. Also check whether the address cells contain only the street name or a full address, because full addresses require a wildcard lookup formula.
Create a Street-to-Day Mapping Table and Use an Exact Lookup Formula
Use this method when the address cell contains only the street name or always matches the mapping table exactly.
A separate mapping table keeps the street-day relationship easy to update and prevents formula errors when a new street is added. In WPS Spreadsheets, you can use XLOOKUP or VLOOKUP for exact matching.
Open the workbook in WPS Spreadsheets. In an empty area such as D1:E10, enter the header "Street" in D1 and "Day" in E1, then list each street and its assigned weekday below.
Click the first cell where the weekday should appear, for example B2, so you can enter the lookup formula for row 2.
Type =IF(A2="","",XLOOKUP(A2,$D$2:$D$10,$E$2:$E$10,"")) and press Enter. This returns a weekday only when column A is not empty and leaves the cell blank when there is no match.
Drag the fill handle from B2 down through the remaining address rows to apply the lookup to the entire list.
Use Wildcard Lookup When the Address Contains Extra Text
Use this method if the address cell contains additional details such as house number, apartment, or city in addition to the street name.
Preserve Manually Entered Weekdays in a Separate Column or with VBA
Use this approach when some rows already contain a manually typed weekday that should not be overwritten.
Assign Weekdays from Addresses with Lookup Formulas in WPS Spreadsheets
WPS Spreadsheets supports the same XLOOKUP and VLOOKUP workbook formulas as Excel, so you can build a street-to-day mapping table and return the correct weekday dynamically whenever an address is updated.
- 1. Open the address workbook in WPS Spreadsheets: Launch WPS Office, open your .xlsx address list, and click the worksheet where you want to display the weekday.
- 2. Create the street-to-day mapping table: In two empty columns, enter "Street" and "Day" headers, then list every street and its assigned weekday.
- 3. Insert the lookup formula: Click the first weekday result cell and enter =IF(A2="","",XLOOKUP(A2,$D$2:$D$10,$E$2:$E$10,"")) for exact matches, or use the wildcard version for full addresses.
- 4. Fill the formula down the column: Drag the fill handle down through the address rows so each row returns the weekday based on its street.
- 5. Keep manual overrides separate: Put any manually entered days in a separate column or use a VBA macro that fills only blank cells, because a formula cannot share a cell with a typed value.
Frequently Asked Questions
Can a formula and a manually typed weekday exist in the same cell?
No. A WPS Spreadsheets cell can contain either a formula or a static value. Keep manual overrides in a separate column, or use a VBA procedure that writes formulas only into blank cells.
Which formula should I use when the address includes the house number and street?
Use a wildcard lookup such as =IF(A2="","",XLOOKUP("*"&$D$2:$D$10&"*",A2,$E$2:$E$10,"",2)) to match the street anywhere within the address text.
Does WPS Spreadsheets support XLOOKUP?
Yes. Recent versions of WPS Spreadsheets support XLOOKUP. If your version does not, use VLOOKUP with an exact match, for example =IF(A2="","",VLOOKUP(A2,$D$2:$E$10,2,FALSE)).
How do I prevent the lookup formula from overwriting existing manual days?
Store existing manual days in a separate column and use the formula only in the calculated column, or run a VBA macro that fills only empty cells and skips cells that already contain a value.
Why does my exact lookup return a blank or #N/A?
The formula returns blank if the street is not found in the mapping table or if the address cell is empty. #N/A can appear with VLOOKUP when there is no exact match. Check that the street spelling matches the mapping table and that the lookup range covers all mapping rows.
