AOVERLAP

AOVERLAP(ar,br,[r],[c])
ar
a array, base array
br
b array, array to be overlapped
[r]
row coordinate, r, 0 or omitted, top left corner of base array, r<0, nr. of rows on top of base array, r>0, nr. of rows beneath top left corner of base array
[c]
clm coordinate, c, 0 or omitted, top left corner of base array, c<0, nr. of clms to the left of base array, c>0, nr. of rows to the right of top left corner of base array
Overlapping 2 arrays using a row/column coordinate system
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
AOVERLAP Array Overlap, overlapping 2 arrays using a row/column coordinate system. !! NEW !! MAKEARRAY
Excel Formula: 
=LAMBDA(ar,br,[r],[c],
    LET(a,IF(ar="","",ar),b,IF(br="","",br),x,ROWS(a),y,ROWS(b),z,COLUMNS(a),w,COLUMNS(b),
       s,MAX(({1;1;0;0}=--(r<=0))*({1;0;1;0}=--(c<=0))*SEQUENCE(4)),
       k,MAKEARRAY(IF(s<=2,MAX(y,x-r),MAX(y+r,x)),IF(OR(s=2,s=4),MAX(w+c,z),MAX(z-c,w)),LAMBDA(m,n,SWITCH(s,
         1,IFS(AND(n<=w,m<=y),INDEX(b,m,n),AND(n>-c,m>-r),INDEX(a,m+r,n+c)),
         2,IFS(AND(n>c,n<=(c+w),m<=y),INDEX(b,m,n-c),m>-r,INDEX(a,m+r,n)),
         3,IFS(AND(m>r,m<=(r+y),n<=w),INDEX(b,m-r,n),n>-c,INDEX(a,m,n+c)),
         4,IFS(AND(m>r,m<=(r+y),n>c,n<=(c+w)),INDEX(b,m-r,n-c),TRUE,INDEX(a,m,n))))),
      IFERROR(k,"")
    )
)
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZ
1arr>=0,c>=0
2123456c,3➡c,6➡
3234567r,,c,,➡c,1➡c,2➡123456123456
4345678⬇ABCD56123456123456234567234567
545679EFH67234567r,2234567r,3345678345678
65678910IJKL78r,3345678⬇34ABCD⬇456ABCD45679
7br45679⬇4ABCD945EFH567EFHr,55678910
8ABCD56789105EFH1056IJKLIJKL⬇ABCD
9EFH=AOVERLAP(A2:F6,A8:D10)IJKL=AOVERLAP(A2:F6,A8:D10,2,2)=AOVERLAP(A2:F6,A8:D10,3,3)EFH
10IJKL=AOVERLAP(A2:F6,A8:D10,3,1)IJKL
11=AOVERLAP(A2:F6,A8:D10,5,6)
12r>=0,c<0
13⬅c,-1⬅c,-2⬅c,-4
14r,⬅c,-2⬅c,-1 123456 123456 123456
15⬇ABCD3456r,1 123456r,2234567234567234567
16EFH4567⬇ABCD567⬇ABCD678r,3345678345678
17IJKL5678EFH678EFH79⬇ABCD67945679
1845679IJKL79IJKL8910EFH78910r,55678910
1956789105678910=AOVERLAP(A2:F6,A8:D10,2,-1)IJKL⬇ABCD
20=AOVERLAP(A2:F6,A8:D10,,-2)=AOVERLAP(A2:F6,A8:D10,1,-1)=AOVERLAP(A2:F6,A8:D10,3,-2)EFH
21IJKL
22=AOVERLAP(A2:F6,A8:D10,5,-4)
23r<0,c<=0
24⬅c,-4⬅c,-5
25⬅c,,⬅c,-1ABCDABCD
26⬆ABCD⬆ABCDEFHEFH
27r,-1EFH56r,-1EFH456⬆IJKLIJKL
28IJKL67IJKL567r,-3123456⬆
29345678345678234567r,-4123456
304567945679345678234567
315678910567891045679345678
32=AOVERLAP(A2:F6,A8:D10,-1)=AOVERLAP(A2:F6,A8:D10,-1,-1)567891045679
33=AOVERLAP(A2:F6,A8:D10,-3,-4)5678910
34=AOVERLAP(A2:F6,A8:D10,-4,-5)
35r<=0,c>0
36c,1➡c,2➡c,3➡c,6➡
37⬆c,1➡⬆ ABCD⬆ ABCD ABCD ABCD
38r,,1ABCD6r,-11EFH6r,-112EFH⬆EFHEFH
392EFH72IJKL723IJKLr,-2123IJKL⬆IJKL
403IJKL8345678345678234567r,-3123456
41456794567945679345678234567
4256789105678910567891045679345678
43=AOVERLAP(A2:F6,A8:D10,,1)=AOVERLAP(A2:F6,A8:D10,-1,1)=AOVERLAP(A2:F6,A8:D10,-1,2)567891045679
44=AOVERLAP(A2:F6,A8:D10,-2,3)5678910
45=AOVERLAP(A2:F6,A8:D10,-3,6)
AOVERLAP post 1
Cell Formulas
RangeFormula
AG3:AM8AG3=AOVERLAP(A2:F6,A8:D10,3,3)
AP3:AY10AP3=AOVERLAP(A2:F6,A8:D10,5,6)
I4:N8I4=AOVERLAP(A2:F6,A8:D10)
Q4:V9Q4=AOVERLAP(A2:F6,A8:D10,3,1)
Y4:AD8Y4=AOVERLAP(A2:F6,A8:D10,2,2)
I9,C43,M20,C20,V19,Y9I9=FORMULATEXT(I4)
AG9,S43,K43,K32,C32,AF20,Q10AG9=FORMULATEXT(AG3)
AP11,AJ45,T33,AP22AP11=FORMULATEXT(AP3)
V14:AB18V14=AOVERLAP(A2:F6,A8:D10,2,-1)
AF14:AM19AF14=AOVERLAP(A2:F6,A8:D10,3,-2)
AP14:AY21AP14=AOVERLAP(A2:F6,A8:D10,5,-4)
C15:J19C15=AOVERLAP(A2:F6,A8:D10,,-2)
M15:S19M15=AOVERLAP(A2:F6,A8:D10,1,-1)
T25:AC32T25=AOVERLAP(A2:F6,A8:D10,-3,-4)
AF25:AP33AF25=AOVERLAP(A2:F6,A8:D10,-4,-5)
C26:H31C26=AOVERLAP(A2:F6,A8:D10,-1)
K26:Q31K26=AOVERLAP(A2:F6,A8:D10,-1,-1)
AF34AF34=FORMULATEXT(AF25)
K37:P42K37=AOVERLAP(A2:F6,A8:D10,-1,1)
S37:X42S37=AOVERLAP(A2:F6,A8:D10,-1,2)
AA37:AG43AA37=AOVERLAP(A2:F6,A8:D10,-2,3)
AJ37:AS44AJ37=AOVERLAP(A2:F6,A8:D10,-3,6)
C38:H42C38=AOVERLAP(A2:F6,A8:D10,,1)
AA44AA44=FORMULATEXT(AA37)
Dynamic array formulas.
 
Upvote 0
Overlapping alignment. Other functions equivalents on minisheet APP2H, APP2V
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARAS
1arInner alignment (no equivalent functions)
2123456
3234567=AOVERLAP(ar,br)=AOVERLAP(ar,br,ROWS(ar)-ROWS(br))=AOVERLAP(ar,br,ROWS(ar)-ROWS(br),COLUMNS(ar)-COLUMNS(br))
4345678ABCD5612345612ABCD123456
545679EFH6723456723EFH234567
65678910IJKL78ABCD7834IJKL34ABCD
7br45679EFH94567945EFH
8ABCD5678910IJKL910567891056IJKLother functions
9EFH=AOVERLAP(ar,br,,COLUMNS(ar)-COLUMNS(br))APP2H
10IJKLAPP2V
11
12Horizontal alignment
13=AOVERLAP(ar,br,,-COLUMNS(br))=AOVERLAP(ar,br,ROWS(ar)-ROWS(br),-COLUMNS(br))=AOVERLAP(ar,br,,COLUMNS(ar))=AOVERLAP(ar,br,ROWS(ar)-ROWS(br),COLUMNS(ar))
14ABCD123456 123456123456ABCD123456
15EFH234567234567234567EFH234567
16IJKL345678ABCD345678345678IJKL345678ABCD
1745679EFH456794567945679EFH
185678910IJKL567891056789105678910IJKL
19
20Horizontal alignment equivalent functions
21=APP2H(br,ar)=APP2H(br,ar,,ROWS(br)-ROWS(ar))=APP2H(ar,br)=APP2H(ar,br,,ROWS(ar)-ROWS(br))
22ABCD123456 123456123456ABCD123456
23EFH234567234567234567EFH234567
24IJKL345678ABCD345678345678IJKL345678ABCD
2545679EFH456794567945679EFH
265678910IJKL567891056789105678910IJKL
27
28Vertical alignment
29=AOVERLAP(ar,br,-ROWS(br))=AOVERLAP(ar,br,-ROWS(br),COLUMNS(ar)-COLUMNS(br))=AOVERLAP(ar,br,ROWS(ar))=AOVERLAP(ar,br,ROWS(ar),COLUMNS(ar)-COLUMNS(br))
30ABCD ABCD123456123456
31EFHEFH234567234567
32IJKLIJKL345678345678
331234561234564567945679
3423456723456756789105678910
35345678345678ABCDABCD
364567945679EFHEFH
3756789105678910IJKLIJKL
38
39Vertical alignment equivalent functions
40=APP2V(br,ar)=APP2V(br,ar,,COLUMNS(br)-COLUMNS(ar))=APP2V(ar,br)=APP2V(ar,br,,COLUMNS(ar)-COLUMNS(br))
41ABCD ABCD123456123456
42EFHEFH234567234567
43IJKLIJKL345678345678
441234561234564567945679
4523456723456756789105678910
46345678345678ABCDABCD
474567945679EFHEFH
4856789105678910IJKLIJKL
49
AOVERLAP post 2
Cell Formulas
RangeFormula
H3,AF40,W40,L40,B40,W29,B29,AI13,X13,B13H3=FORMULATEXT(H4)
O3,AE29,L13O3=FORMULATEXT(P4)
AF3,J29AF3=FORMULATEXT(AH4)
H4:M8H4=AOVERLAP(ar,br)
P4:U8P4=AOVERLAP(ar,br,ROWS(ar)-ROWS(br))
Y4:AD8Y4=AOVERLAP(ar,br,,COLUMNS(ar)-COLUMNS(br))
AH4:AM8AH4=AOVERLAP(ar,br,ROWS(ar)-ROWS(br),COLUMNS(ar)-COLUMNS(br))
X9X9=FORMULATEXT(Y4)
B14:K18B14=AOVERLAP(ar,br,,-COLUMNS(br))
M14:V18M14=AOVERLAP(ar,br,ROWS(ar)-ROWS(br),-COLUMNS(br))
X14:AG18X14=AOVERLAP(ar,br,,COLUMNS(ar))
AI14:AR18AI14=AOVERLAP(ar,br,ROWS(ar)-ROWS(br),COLUMNS(ar))
B21,AI21,X21,M21B21=FORMULATEXT((B22))
B22:K26B22=APP2H(br,ar)
M22:V26M22=APP2H(br,ar,,ROWS(br)-ROWS(ar))
X22:AG26X22=APP2H(ar,br)
AI22:AR26AI22=APP2H(ar,br,,ROWS(ar)-ROWS(br))
B30:G37B30=AOVERLAP(ar,br,-ROWS(br))
L30:Q37L30=AOVERLAP(ar,br,-ROWS(br),COLUMNS(ar)-COLUMNS(br))
W30:AB37W30=AOVERLAP(ar,br,ROWS(ar))
AF30:AK37AF30=AOVERLAP(ar,br,ROWS(ar),COLUMNS(ar)-COLUMNS(br))
B41:G48B41=APP2V(br,ar)
L41:Q48L41=APP2V(br,ar,,COLUMNS(br)-COLUMNS(ar))
W41:AB48W41=APP2V(ar,br)
AF41:AK48AF41=APP2V(ar,br,,COLUMNS(ar)-COLUMNS(br))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
ar='AOVERLAP post 2'!$A$2:$F$6AI22, AI14, AH4, AF41, AF30, Y4, X22, X14, W41, W30, P4, M22, M14, L41, L30, H4, B41, B30, B22, B14
br='AOVERLAP post 2'!$A$8:$D$10AI22, AI14, AH4, AF41, AF30, Y4, X22, X14, W41, W30, P4, M22, M14, L41, L30, H4, B41, B30, B22, B14
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,505
Messages
6,131,026
Members
449,616
Latest member
PsychoCube

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top