1

I have an array of values (TRUE / FALSE seq, screenshot), which is the result of this formula:

=INDEX(I22:N22<>0,)

In this array I want to match the first changing value. It is not necessarily false/true switch as at the screenshot (F2 - G2), it can be numbers from 1 to 4 for example.
I think I would know how to archive this If I know how to refer in formula next value in the range I22:N22. Something like: =INDEX(I22:N22<>I*next value*:N*next value*,)

Improve this question

2 Answers 2

Reset to default
1

How about just offsetting the arrays:

=INDEX(I22:M22<>0,)<>INDEX(J22:N22<>0,)

This finds where a value is <>0 but the one after it =0, or vice versa

<edit: and enter with Ctrl+Shift+Enter />

Improve this answer
3
  • Pretty good idea, but what if I'm working with not a real sheet range but array, sequence of values - the result of INDEX(I22:N22<>0,) formula. And goal to process this result inside greater formula. Then this array exists only in the scope of some greater formula, and doesn't exist on sheet.
    – Yaroslav Zharkov
    Jul 22, 2021 at 12:26
  • @YaroslavZharkov Unforturnately Excel doesn't have more advanced indexing functionality, so the best answer to your current questions is this one. If it doesn't work for you, please post an example for it.
    – Máté Juhász
    Jul 23, 2021 at 8:54
  • If the formula in your question is not the real formula you are using then maybe you need to re-word your question to tell us what you are realy doing so we are not shooting in the dark. SImplifying it to make it easier for us to understand may lead to oversimplifiction and solutions that won't work for you.
    – AdamV
    Jul 23, 2021 at 16:57
0

to find the relative position of the first change something like this should work:

=MATCH(NOT(I22<>0),I22:N22<>0,0)

I22<>0 -> determines what the first value in the array is

NOT() -> converts that to the opposite value

MATCH() -> finds the relative position of the first change in the array

caveats:

You probably need to modify if there is no change in the array (all true or all false),

You probably need to modify as you mentioned that your values actually range from 0 to 4 (although I don't know how that occurs based on I22:N22<>0)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .