Is there an Excel formula that will return the first value in a set of non-sequential cells?
I am using this formula and it works for sequential cells, but not for non-sequential cells.
=INDEX(range,MATCH(FALSE,ISBLANK(range),0))
For example, if Row 2 in my spreadsheet was like this and I used the formula above with a range of B2:D2, the formula would return a value of 6.
What I want to do is find the first value of B2, D2, and F2 (or any non-sequential range), which would be 8. I updated the formula to what is below but that returned #N/A.
=INDEX((B2,D2,F2),MATCH(FALSE,ISBLANK((B2,D2,F2)),0))
| Row 1 | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| Row 2 | 5 | 6 | 8 | 11 | 13 |
CodePudding user response：
Not sure if I interpreted your question correctly, but see if you can use:
=@FILTER(B1:F1,ISEVEN(COLUMN(B1:F1))*(B1:F1<>""),"")
CodePudding user response：
Yes, you can just hstack them into a single array and use index/match. @Jos Woolley's solution works for me as well so I have taken the liberty of including it in my screenshot. My formula:
=LET(stack,HSTACK(range),INDEX(stack,MATCH(FALSE,stack="",0)))
Where range is defined as
Suppose they aren't single cells any more
@Jos's formula is only designed to work with single elements so will get out of sync in this case, but hstack is still OK.
However the question only asked for single elements so in my view both answers are correct.
