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:
- let r be the row you're searching
- let exclude be the list of excluded values (shown in B6:B8 in yellow, as an example)
- 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
- 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.
