WPS Spreadsheet: Extract Nonblank Excel Values with Related Cells
Question details
The user needs to return only populated overspend or underspend values to another worksheet while preserving the two cells related to each matching row.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A source list contains more than 100 rows, but only a few randomly located rows contain values. The user wants to extract those values and the two cells immediately to their left into a separate worksheet.
- Observed behavior
- The desired result is a compact list containing only nonblank source values and their corresponding related cells, without empty rows.
Confirm the source sheet name, the column that determines whether a row is populated, and the two related columns immediately to its left. Check that the destination cells are empty so the extracted results can spill down without overwriting existing data.
Extract nonblank rows with INDEX, SMALL, IF, and IFERROR
Use this array formula when matching values are scattered through a long source range and you need to return each matching row in order.
The formula tests the source condition column, uses SMALL to find the next matching row, and INDEX to return the corresponding value. Apply the same pattern to each related column by changing the INDEX range while keeping the condition range unchanged.
For example, if column U contains the overspend or underspend values and column I contains a related value, enter the following formula on the destination sheet. The ROW subtraction matches the source range beginning at row 6.
Open the destination worksheet and select the cell where the first extracted value should appear.
Enter =IFERROR(INDEX(VISITS!$I$6:$I$201,SMALL(IF(LEN(VISITS!$U$6:$U$201)>0,ROW(VISITS!$U$6:$U$201)-5),ROWS($A$1:A1))),"") and replace VISITS, the INDEX range, or the condition range to match your workbook.
In older WPS Spreadsheet versions, press Ctrl+Shift+Enter after entering the formula. In versions that support dynamic arrays, press Enter and let the results fill down.
Drag the fill handle down far enough to cover the possible number of matches. IFERROR returns blank cells after the last populated source row.
Copy the formula to two adjacent destination columns and change only the INDEX range to the two source columns immediately to the left of the condition column, such as columns T and S when the condition is in U.
Use a helper column to simplify row matching
Use this approach when array formulas are difficult to maintain or when you want to inspect the matching row numbers before extracting related values.
Extract nonblank values and matching cells in WPS Spreadsheet
WPS Spreadsheet can identify populated rows with array formulas and return the matching value plus its related cells to another worksheet. Its familiar formula interface supports INDEX, SMALL, IF, IFERROR, and Excel-compatible workbooks for a smooth migration.
- 1. Open the workbook: Launch WPS Office, open the Excel-compatible workbook, and select the source and destination worksheets.
- 2. Set the matching formula: In the destination sheet, enter the INDEX, SMALL, IF, and IFERROR formula using the populated-value column as the condition range.
- 3. Confirm and fill down: Press Ctrl+Shift+Enter for an array formula in older versions, or press Enter in dynamic-array versions, then fill the formula down.
- 4. Add related columns: Copy the formula across and change each INDEX range to the two cells immediately left of the condition column.
Frequently Asked Questions
Which part of the formula determines whether a row is extracted?
The LEN condition, such as LEN(VISITS!$U$6:$U$201)>0, checks whether each cell in column U contains a value. Change this range to the column that identifies an overspend or underspend row.
How do I return the two cells to the left of the matching value?
Keep the condition range fixed and change only the INDEX range. If the populated-value column is U, use the source ranges for T and S in separate destination columns.
Why does the formula need Ctrl+Shift+Enter?
Older spreadsheet engines require Ctrl+Shift+Enter to evaluate the IF array inside SMALL. Newer WPS Spreadsheet versions may support dynamic arrays and accept the formula with Enter.
What does the ROW($A$1:A1) part do?
It creates the sequence 1, 2, 3, and so on as the formula is filled down, allowing SMALL to return the first, second, third, and subsequent matching rows.
How can I prevent zeros or empty results after the last match?
Keep IFERROR around the INDEX and SMALL expression. When there are no more matching rows, IFERROR returns an empty string instead of an error.
