Return the First Nonempty Noncontiguous Cell in Excel with INDEX MATCH
Question details
Return the first nonempty value from selected Excel columns spaced seven columns apart.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A formula checks noncontiguous cells in row 6, starting at column T and continuing at seven-column intervals.
- Observed behavior
- The current formula returns the last nonempty result instead of the first.
Check that T6:NM6 covers the cells you want to search and that the first target is T6. The formula checks T6, AA6, AH6, and subsequent cells seven columns apart.
Use INDEX MATCH with a seven-column MOD pattern
Use this formula to find the first nonempty value among the selected cells, reading from left to right.
COLUMN returns each column number, and MOD identifies columns with the same remainder as column T. MATCH finds the first position where both the column pattern and nonempty-cell test are true.
In Excel, click the cell where you want the first matching value to appear.
Type =INDEX(T6:NM6,MATCH(1,INDEX((T6:NM6<>"")*(MOD(COLUMN(T6:NM6),7)=6),,),0)) in the formula bar, then press Enter.
In the formula bar, change both T6:NM6 references to your search range. If the first target column changes, replace 6 with that column number's remainder when divided by 7.

Find the first matching cell in WPS Spreadsheets
Use the same INDEX MATCH and MOD formula in WPS Spreadsheets to find the first nonempty value at seven-column intervals.
- 1. Open the workbook: Open WPS Spreadsheets and select Open to load your Excel workbook.
- 2. Select a result cell: Click the cell where you want to display the first nonempty value.
- 3. Enter the formula: In the formula bar, enter =INDEX(T6:NM6,MATCH(1,INDEX((T6:NM6<>"")*(MOD(COLUMN(T6:NM6),7)=6),,),0)) and press Enter.
- 4. Adjust and save: Change the range and MOD remainder in the formula bar if your target columns differ, then select Save.

Frequently Asked Questions
Why does the formula use a remainder of 6?
Column T is column 20, and 20 divided by 7 leaves a remainder of 6. Columns seven positions apart have the same remainder.
How do I change the first target column?
Update both range references in the formula bar and replace the MOD remainder with the new starting column's remainder when divided by 7. For example, column U is 21, so its remainder is 0.
What if all the selected cells are empty?
MATCH returns #N/A because it cannot find a qualifying value. To display a blank instead, wrap the formula in IFNA, such as =IFNA(your_formula,"").
Does the formula skip cells containing spaces?
No. A cell containing spaces is not an empty string, so the formula treats it as nonempty. Remove unwanted spaces from the source cells if they should be skipped.




