Forum Discussion

Krueger22's avatar
Krueger22
Copper Contributor
May 16, 2022

Select next non-blank cell skipping certain letters

I am looking for advice to adjust a formula: =INDEX(Tab!A7:L7,MATCH(FALSE,ISBLANK(Tab!A7:L7),0))

So that it will skip non-blank data from Tab!A7:L7, but also skip a cell that has certain symbols/letters like "A" and pull the next cell with data.

excel
Formulas and Functions

4 Replies

  • flexyourdata's avatar
    flexyourdata
    Iron Contributor
    May 16, 2022
    Hi Krueger22

    What are you trying to return from A7:L10? The first non-blank cell? First non-blank row? Column?

    Your message says "Skip non-blank data from A7:L7" - does this mean you are searching in one row?

    If you upload an example, someone can help more easily.
      • flexyourdata's avatar
        flexyourdata
        Iron Contributor
        May 16, 2022

        Krueger22 

         

         

         

        If you have a variable range of excluded values, and you want to return the first valid value from a row, where first is defined as the first column in which there is a non-blank, non-excluded value, then this should work:

         

         

        =LET(
r,$A$2:$D$2,
exclude,$B$6:$B$8,
valid,SCAN(FALSE,r,LAMBDA(a,b,AND(ISERROR(XMATCH(b,exclude)),NOT(ISBLANK(b))))),
INDEX(FILTER(r,valid),1,1)
)

         

         

        Which is to say:

        1. let r be the row you're searching
        2. let exclude be the list of excluded values (shown in B6:B8 in yellow, as an example)
        3. let "valid" be an array the same size as r which contains FALSE where the cell is blank or the cell contains an excluded value, and TRUE otherwise
        4. Return the first cell from "r" whose corresponding "valid" value is TRUE

         

        If you want to return the cell reference of the first valid cell, then:

         

         

        =LET(
r,$A$2:$D$2,
exclude,$B$6:$B$8,
valid,SCAN(FALSE,r,LAMBDA(a,b,AND(ISERROR(XMATCH(b,exclude)),NOT(ISBLANK(b))))),
ADDRESS(ROW(r),MIN(FILTER(COLUMN(r),valid)))
)

         

         

        The definitions of r, exclude and valid are identical to above. The return value constructs the cell reference using the row of r and the minimum column number of the valid columns.

Resources