WPS Spreadsheets: Use XLOOKUP with Values in Merged Cells
Question details
The user needs to prepare location numbers stored in merged cells so XLOOKUP can match every related row.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains more than 10,000 rows where one location number is displayed across a merged area, and the user wants to use XLOOKUP or FILTER with those rows.
- Observed behavior
- Only the first cell of each merged area contains the location number; the other cells are blank after unmerging, so lookups cannot match every row.
Save a copy of the workbook before changing the merged ranges. Identify the lookup column and the first and last data rows so the cleaned helper range matches the source data.
Unmerge the cells and fill location values down before using XLOOKUP
Use this reliable approach when each row within a merged area should have the same location number.
Merged cells store the value only in the upper-left cell. XLOOKUP cannot treat the visually merged area as repeated values, so the lookup column must be normalized first.
In WPS Spreadsheets, select the column or range containing the merged location numbers, including all rows that will be searched.
Open the Home tab, select Merge and Center, and choose Unmerge Cells. The original value remains in the top cell of each former merged area.
For each group, copy the location number from the first cell and fill it through the blank rows below. For a large uniform range, use a helper column with the formula =SCAN("",A2:A10001,LAMBDA(a,v,IF(v<>"",v,a))) and replace A2:A10001 with the location-number range.
Enter a formula such as =XLOOKUP(E2,$C$2:$C$10001,$D$2:$D$10001,"Not found") and adjust E2, the lookup column, and the return column to match the worksheet.
Use a VBA cleanup procedure for repeated merged-cell imports
Use an adapted macro when the same merged-cell cleanup must be repeated across large workbooks.
Clean merged-cell data and run XLOOKUP in WPS Spreadsheets
WPS Spreadsheets lets you unmerge lookup data, fill location values into every applicable row, and use XLOOKUP or FILTER on a normalized range. Its familiar spreadsheet interface also makes it easy to review the results before saving.
- 1. Open the workbook: Launch WPS Office, open the spreadsheet, and save a working copy before editing merged cells.
- 2. Unmerge the lookup range: Select the location-number range, then choose Home > Merge and Center > Unmerge Cells.
- 3. Fill values down: Use a helper formula such as =SCAN("",A2:A10001,LAMBDA(a,v,IF(v<>"",v,a))) or fill each former merged group with its top value.
- 4. Apply XLOOKUP: Use XLOOKUP with the completed helper range, for example =XLOOKUP(E2,$C$2:$C$10001,$D$2:$D$10001,"Not found").
Frequently Asked Questions
Why does XLOOKUP fail with values displayed in merged cells?
A merged area usually stores its value only in the upper-left cell. After unmerging, the remaining cells are blank, so XLOOKUP cannot match them until the value is filled into each applicable row.
How do I unmerge cells in WPS Spreadsheets?
Select the merged range, open the Home tab, choose Merge and Center, and click Unmerge Cells. The value remains in the upper-left cell of each former merged area.
What formula can fill merged-cell values down automatically?
In a helper column, use =SCAN("",A2:A10001,LAMBDA(a,v,IF(v<>"",v,a))). Replace the range with the location-number column, then use the resulting values with XLOOKUP or FILTER if your WPS version supports these functions.
Can I use XLOOKUP directly on merged cells without unmerging them?
Generally, no. The lookup range should contain a value in every row that must be matched. Unmerge the cells and populate the blank rows, or create a helper range that carries each value downward.
Is a macro suitable for cleaning more than 10,000 merged-cell rows?
Yes, a VBA procedure can unmerge a range and fill blank values through an array, but it should be adapted for blank rows, subtotals, and multiple sections. Always test it on a duplicate workbook first.
