May 16 2022 07:40 AM - edited May 16 2022 07:55 AM
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.
May 16 2022 07:47 AM
May 16 2022 08:01 AM - edited May 16 2022 08:02 AM
@flexyourdata apologise I misstyped the formula, I made a quick example were I am looking for "C2"
May 16 2022 08:56 AM - edited May 16 2022 08:58 AM
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:
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.
May 16 2022 10:28 AM