Excel array formula using if() with two rows from spilled array source

Question

I have set of data that I am pulling from another sheet with filter() function, and then I'd like to use array formula if possible (with # operator) to have formula spill dynamically for as much rows as I receive from filter().

I can do it with VBA easily, but then other users might have issues, and I would really like to get to a solution that would just allow me and other users of this template to just paste exported data into initial sheet, and that magic happens without any additional clicks (macro, pivot refresh, code on sheet change that autorefreshes, etc). Therefore, formula that works with # would be really amazing.

Formula that works (but with dragging down) is quite simple:

=IF((D2="IN")*(D1="OUT")*(B2=B1),C2-C1,0)

Simplified excel file

For some reason I can't reply to the answer that provided the solution, but I have marked it as solution. Thanks a lot @JB-007 :) — ch1zra
– ch1zra, Commented Nov 25 at 5:36

chris neilsen · Accepted Answer · 2025-11-25 05:41:58Z

3

=LET(x_,COUNTA(A:A)-1,
b1_,OFFSET(B1,,,x_),
b2_,OFFSET(b1_,1,0),
c1_,OFFSET(b1_,,1),
c2_,OFFSET(c1_,1,0),
d1_,OFFSET(b1_,,2),
d2_,OFFSET(d1_,1,0),
y_,IFERROR((d2_="IN")*(d1_="OUT")*(b2_=b1_)*(c2_-c1_),),
y_)

sample file here

edited Nov 25 at 5:41

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

answered Nov 25 at 0:21

JB-007

2,6441 gold badge9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JB-007 Nov 25 at 0:23

if col A known to have 'gaps' in data, then use something like max(filter(sequence(rows(A:A)),--(A:A<>"")))-1 instead of counta(a:a)-1... may need some tweaking RE: typographical / syntax errors, and depends on placement of first row header... etc.

JB-007 · Accepted Answer · 2025-11-25 00:31:25Z

0

=LET(x0_,DROP(A1#,-1),x1_,DROP(A1#,1),IFERROR( (INDEX(x0_,,2)=INDEX(x1_,,2))*(DROP(x1_,,3)="IN")*(DROP(x0_,,3)="OUT")*(INDEX(x1_,,3)-INDEX(x0_,,3)),))

answered Nov 25 at 0:31

JB-007

2,6441 gold badge9 silver badges23 bronze badges

1 Comment

JB-007 Nov 25 at 0:32

achieves 'true' wrap as required in OP Q. (but alternative can run off source data sheet db directly without any interim helper...)

VBasic2008 · Accepted Answer · 2025-11-25 06:58:11Z

Differences with Conditions

Replace the range reference as required, e.g., A2#, B2#, Drop(A2#,,1),...
Adjust the rest of the inputs in the first two rows as required, e.g., if you're going to loose the helper columns, then the IN-OUT column (i_col) might be 2 in the 2nd formula.

Date Helper Columns

=LET(data,B2:D10,d_col,1,i_col,3,t_col,2,
        start_val,"OUT",end_val,"IN",not_val,0,
    a,DROP(data,1),
    b,DROP(data,-1),
    d,INDEX(a,,d_col)=INDEX(b,,d_col),
    i,(INDEX(a,,i_col)=end_val)*(INDEX(b,,i_col)=start_val),
    t,INDEX(a,,t_col)-INDEX(b,,t_col),
    VSTACK(not_val,IF(d*i,t,not_val)))

No Helper Columns

Assumes text in column A.

=LET(data,A2:D10,dt_col,1,i_col,4,dt_delim," ",
        start_val,"OUT",end_val,"IN",not_val,0,
    a,DROP(data,1),
    b,DROP(data,-1),
    dta,INDEX(a,,dt_col),
    dtb,INDEX(b,,dt_col),
    d,TEXTBEFORE(dta,dt_delim)=TEXTBEFORE(dtb,dt_delim),
    t,TEXTAFTER(dta,dt_delim)-TEXTAFTER(dtb,dt_delim),
    i,(INDEX(a,,i_col)=end_val)*(INDEX(b,,i_col)=start_val),
    VSTACK(not_val,IF(d*i,t,not_val)))

Collectives™ on Stack Overflow

Excel array formula using if() with two rows from spilled array source

3 Answers 3

1 Comment

1 Comment

Differences with Conditions

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Differences with Conditions

Comments

Related