ASELECT

=ASELECT(a,rm,rx,cm,cx,ec)

a
array
rm
integer, row min or starting row, 0,1 or ignored will take 1 value
rx
integer, row max or ending row, ignored will take ROWS(a) value
cm
integer, column min, or starting column, 0,1 or ignored will take 1 value
cx
integer, column max, or ending column, ignored will take COLUMNS(a) value
ec
constant array integers, appends extra columns, {5,7,10}, ignored will append nothing

array select, selects a region of an array between 2 rows, 2 columns, can append extra columns

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
ASELECT array select, selects a region of an array between 2 rows, 2 columns, can append extra columns
Excel Formula:
=LAMBDA(a,rm,rx,cm,cx,ec,
    LET(r,ROWS(a),c,COLUMNS(a),sr,SEQUENCE(r),sc,SEQUENCE(,c),
       v,IFNA(XMATCH(rm,sr),1),w,IFNA(XMATCH(rx,sr),r),rp,(sr>=MIN(v,w))*(sr<=MAX(v,w)),
       x,IFNA(XMATCH(cm,sc),1),y,IFNA(XMATCH(cx,sc),c),cp,(sc>=MIN(x,y))*(sc<=MAX(x,y)),
       z,ISNUMBER(XMATCH(sc,ec)),
       FILTER(FILTER(a,IF(AND(AND(cp),OR(z)),cp*z,cp+z)),rp)
    )
)
LAMBDA 7.0.xlsx
ABCDEFGHIJKLMNOPQ
1123456789101112131415
2161718192021222324252627282930
3313233343536373839404142434445
4464748495051525354555657585960
5616263646566676869707172737475
6767778798081828384858687888990
7919293949596979899100101102103104105
8106107108109110111112113114115116117118119120
9121122123124125126127128129130131132133134135
10136137138139140141142143144145146147148149150
11
12=ASELECT(A1#,6,9,5,7,{11,12})=ASELECT(A1#,,4,4,7,{15,13})=ASELECT(A1#,,,,,{8,10})
13808182868745671315810
149596971011021920212228302325
151101111121161173435363743453840
161251261271311324950515258605355
17=ASELECT(A1#,7,,13,,{2,4})=ASELECT(A1#,,3,,3,)6870
1892941031041051238385
1910710911811912016171898100
20122124133134135313233113115
21137139148149150128130
22143145
23
ASELECT post
Cell Formulas
RangeFormula
A1:O10A1=SEQUENCE(10,15)
A12,G17,A17,N12,G12A12=FORMULATEXT(A13)
A13:E16A13=ASELECT(A1#,6,9,5,7,{11,12})
G13:L16G13=ASELECT(A1#,,4,4,7,{15,13})
N13:O22N13=ASELECT(A1#,,,,,{8,10})
A18:E21A18=ASELECT(A1#,7,,13,,{2,4})
G18:I20G18=ASELECT(A1#,,3,,3,)
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

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