How to use additional range within LAMBDA

Hi, attached a sample of what I am trying to do.

Essentially my starting point is:

Please Login or Register  to view this content.

That is, simply a growing counter when data is avialable in a data range, otherwise leave counter constant.

Then, in cell J4, I was trying to amend the formula to incorporate another condition within the LAMBDA-function based results of another input range called x:

Please Login or Register  to view this content.

While the formula is not working, it is supposed to check each iteration of a also the corresponding value of the range x and then apply the rest of the calculation accordingly.

Happy if someone can explain why this is not working/how to fix it.

Thanks

The first problem is that "a" is initially 0 so the index is returning the entire array. if you change it to a+1 it will work with that data, but if you make D7 blank & put something in E7 it will give the wrong answer as the value of "a" is not sequential & so is looking at the wrong row.

Because "s" is an array the index is returning multiple values, not a single value.

One way to do it is

Formula:

Please Login or Register  to view this content.

Oh thanks, that's quite neat.

Without much success so far, I have currently been looking into this:
https://stackoverflow.com/questions/...e-same-formula
which looks like a somewhat similar problem and tried to adjust the MAP / REDUCE solutions there to my scenario.

Glad to help & thanks for the feedback.

btw - I think I figured that - at least in my example, the range in column E actually seems irrelevant for generating the desired output.
I think also in your formula, Fluff13, appending the range &E4:E8 doesn't really do anything, does it?

So, in the example the following slightly shorter version yields the same result:

Please Login or Register  to view this content.

And this is also equivalent to:

Please Login or Register  to view this content.

So, at least my take-away is that the maniupulation of the range passed to SCAN is the important bit rather than to fiddle around too much within the LAMBDA piece which may mess up the accumulator variable.
And then this made me realise that maybe the concatenation and TEXT-xxx part isn't really needed at all:

Please Login or Register  to view this content.