WPS Spreadsheet: Write Values to Specific Cells from Address Data
Question details
The user needs to place or display values in specific spreadsheet cells based on cell addresses stored in column A.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Cell addresses such as A1 or C5 are listed in column A, and the corresponding values are listed in column B. The user wants each value to appear at its addressed cell without editing every destination manually.
- Observed behavior
- The address-value pairs are available, but the destination cells are not automatically populated.
Confirm that column A contains valid cell addresses in relative format, such as A1 or C5, and that the matching values are in column B. Identify the full destination range where the formula should be entered.
Use an XLOOKUP formula to display values at their addressed cells
Use this method when formulas are acceptable and you want each destination cell to retrieve its matching value automatically.
The formula converts the current cell position into an address, searches for that address in column A, and returns the related value from column B. Cells without a matching address remain blank.
In WPS Spreadsheet, select the full range of cells that may be referenced by the addresses in column A.
Type =XLOOKUP(ADDRESS(ROW(),COLUMN(),4),$A:$A,$B:$B,"") in the formula bar or directly in the selected starting cell.
Press Enter, then use the fill handle or copy and paste the formula across all possible destination cells.
For each destination cell, WPS Spreadsheet compares its address with column A and displays the corresponding value from column B.
Use a VBA macro when destination cells must contain fixed values
Use a macro when the values need to be physically written into the addressed cells instead of being displayed by formulas.
Automate Address-Based Value Placement in WPS Spreadsheet
WPS Spreadsheet can match each cell's address with an address list and return the related value using XLOOKUP, so you can populate a destination range without manually editing every cell.
- 1. Open your spreadsheet: Launch WPS Office and open the workbook in WPS Spreadsheet.
- 2. Prepare the address and value columns: Place destination addresses in column A and their corresponding values in column B.
- 3. Enter the lookup formula: Select the possible destination range and enter =XLOOKUP(ADDRESS(ROW(),COLUMN(),4),$A:$A,$B:$B,"").
- 4. Fill and review the results: Copy the formula across the range and verify that each addressed cell displays the expected value.

Frequently Asked Questions
What does the ADDRESS function do in this formula?
ADDRESS(ROW(),COLUMN(),4) converts the current cell's row and column position into a relative address such as A1 or C5. XLOOKUP then searches for that address in column A.
Where should I enter the XLOOKUP formula?
Enter it in every cell that could be a destination for an address listed in column A. You can select the full range first, then fill or copy the formula across it.
Why are some destination cells blank?
The formula returns a blank when the current cell address is not found in column A or when the matching result in column B is blank. Check the address spelling and formatting for extra spaces or invalid references.
Can this method permanently write values instead of displaying formulas?
No. The XLOOKUP method displays results through formulas. To write fixed values into the addressed cells, use a VBA macro if your WPS Office edition supports VBA, or copy the results and use Paste Special to paste values.
What happens if column A contains the same address more than once?
XLOOKUP returns the first matching value it finds. Remove duplicate addresses or decide which row should contain the value that takes priority.




