Function Problems

WPS Spreadsheet: Write Values to Specific Cells from Address Data

Algirdas JasaitisAlgirdas Jasaitis Nov 12, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination range

In WPS Spreadsheet, select the full range of cells that may be referenced by the addresses in column A.

2
Enter the lookup formula

Type =XLOOKUP(ADDRESS(ROW(),COLUMN(),4),$A:$A,$B:$B,"") in the formula bar or directly in the selected starting cell.

3
Fill the formula across the range

Press Enter, then use the fill handle or copy and paste the formula across all possible destination cells.

4
Check the displayed values

For each destination cell, WPS Spreadsheet compares its address with column A and displays the corresponding value from column B.

Formula result: This method displays values through formulas. It does not permanently replace the destination cells with fixed values.

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. 1. Open your spreadsheet: Launch WPS Office and open the workbook in WPS Spreadsheet.
  2. 2. Prepare the address and value columns: Place destination addresses in column A and their corresponding values in column B.
  3. 3. Enter the lookup formula: Select the possible destination range and enter =XLOOKUP(ADDRESS(ROW(),COLUMN(),4),$A:$A,$B:$B,"").
  4. 4. Fill and review the results: Copy the formula across the range and verify that each addressed cell displays the expected value.
Automatically matches destination cells with addresses listed in column ADisplays corresponding values from column B across a selected rangeProvides a familiar spreadsheet workflow with formula-based automationSupports Microsoft Excel XLSX and XLS formats for easier file sharing and migration
microsoft office alternative - wps office

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.