WPS Spreadsheets: How to Return the Second, Third, and Later Nonblank Cells
Question details
The user needs a formula that skips blank cells and returns the second, third, fourth, and subsequent nonblank values from a horizontal range, such as a form response row.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a horizontal row of form responses that contains scattered blanks and wants to list only the filled responses in order.
- Observed behavior
- The goal is to extract successive nonblank values instead of the full range, so blanks are ignored and values are returned in left-to-right order.
Identify the exact horizontal range that contains the blanks, such as 'Form Responses 1'!$L$2:$AU$2. In WPS Spreadsheets versions without dynamic arrays, be ready to enter the formula with Ctrl+Shift+Enter.
Return the second, third, and later nonblank cells with INDEX, SMALL, and IF
Use this array formula when you need to pull successive nonblank values from a horizontal range and blanks are scattered irregularly.
The formula uses IF to create an array of relative column numbers for cells that are not empty, SMALL to pick the nth smallest matching position, and INDEX to return the value at that position. As you copy the formula right, COLUMN(A$1) increases to 2, 3, 4 and returns the second, third, fourth, and later nonblank values.
Click the cell where you want the first extracted nonblank value, such as B2.
Type =INDEX('Form Responses 1'!$L$2:$AU$2,SMALL(IF('Form Responses 1'!$L$2:$AU$2<>"",COLUMN('Form Responses 1'!$L$2:$AU$2)-11),COLUMN(A$1))). Replace the sheet name and range with your actual data range.
In WPS Spreadsheets or Excel versions before Microsoft 365/Excel 2021, press Ctrl+Shift+Enter instead of Enter so the formula is evaluated as an array formula.
Drag the fill handle to the right to return the second, third, fourth, and subsequent nonblank values in order.
Return second, third, and later nonblank cells with WPS Spreadsheets
Enter the INDEX, SMALL, and IF array formula in WPS Spreadsheets and copy it across to extract successive nonblank values from any horizontal range.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the workbook with the horizontal range, and click the sheet that contains your form responses.
- 2. Select the output cell and enter the formula: Click the first output cell and type the INDEX-SMALL-IF formula, replacing 'Form Responses 1'!$L$2:$AU$2 with your actual range.
- 3. Confirm with Ctrl+Shift+Enter if required: If your WPS Spreadsheets version requires legacy array entry, press Ctrl+Shift+Enter. In newer versions, Enter may be enough.
- 4. Copy the formula across to return later values: Drag the fill handle to the right so COLUMN(A$1) increments from A$1 to B$1, C$1, and so on.
Frequently Asked Questions
Why does the formula subtract 11 from the COLUMN result?
The sample range starts at column L, which is column 12. Subtracting 11 converts absolute column numbers 12, 13, 14, and so on into relative positions 1, 2, 3 for INDEX. If your range starts in a different column, replace 11 with COLUMN(first cell)-1.
Do I need to press Ctrl+Shift+Enter in WPS Spreadsheets?
In WPS Spreadsheets and Excel versions before Microsoft 365 or Excel 2021, press Ctrl+Shift+Enter to enter the formula as an array formula. In newer versions that support dynamic arrays, pressing Enter may be sufficient.
Can I adapt this formula for a vertical range?
Yes. Replace COLUMN with ROW and adjust the subtraction to the starting row. For example, use ROW(range)-start_row+1 instead of COLUMN(range)-11, and copy the formula down instead of across.
What can I do if the formula returns #NUM! after all nonblank values are returned?
Wrap the formula in IFERROR, such as =IFERROR(INDEX(...),""), to return a blank cell when no further matching position exists. Make sure the range reference and column adjustment match your data.
