Formula Errors

Return the First Nonempty Noncontiguous Cell in Excel with INDEX MATCH

Khadija KhanKhadija Khan Nov 18, 2026 869 views

Question details

Return the first nonempty value from selected Excel columns spaced seven columns apart.

Return the First Nonempty Noncontiguous Cell in Excel with INDEX MATCH
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

In Excel, click the cell where you want the first matching value to appear.

2
Enter the formula

Type =INDEX(T6:NM6,MATCH(1,INDEX((T6:NM6<>"")*(MOD(COLUMN(T6:NM6),7)=6),,),0)) in the formula bar, then press Enter.

3
Adjust the range if needed

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.

Use INDEX MATCH with a seven-column MOD pattern
If no value is found: The formula returns #N/A when none of the selected cells contains a value. Cells containing formulas that return an empty string are treated as empty.
Try it in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and select Open to load your Excel workbook.
  2. 2. Select a result cell: Click the cell where you want to display the first nonempty value.
  3. 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. 4. Adjust and save: Change the range and MOD remainder in the formula bar if your target columns differ, then select Save.
Find the first nonempty value in regularly spaced columns.Open and edit Microsoft Excel XLSX files.Work with familiar spreadsheet formulas and a familiar interface.
microsoft office alternative - wps office

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.