AINDEXERL

[schardt679]

AINDEXERL returns a stack of all relative row numbers for each column or all relative column numbers for each row in an array. Uses MAKEARRAY!

Thanks to Rico S's LISTCOLUMNS & LISTROWS for the inspiration (from Mr. Excel's UNIQUE of Rectangular Range Using MAKEARRAY in Excel - 2416).
ARRAYSIZE(array) =LAMBDA(array, ROWS(array)*COLUMNS(array)).
LISTCOLUMNS(array) =LAMBDA(array, LET(colCount, COLUMNS(array), modArray, MOD(SEQUENCE(ARRAYSIZE(array)), colCount), columnNumbers, IF(modArray=0, colCount, modArray), columnNumbers) ).
LISTROWS(array) =LAMBDA(array, ROUNDUP(SEQUENCE(ARRAYSIZE(array))/COLUMNS(array), 0)).
Calls CELLCOUNT.
Other functions on minisheet: AFORMULATEXT.

Screen Tip/ Comment: =AINDEXERL(Array☛ range to return row/column numbers, [Column_Numbers]☛ 1=return column numbers; 0/=return row numbers, [By_Col]☛ 1=stack column by column; 0/=row by row) ⁂[]=optional; =default; =omit

=LAMBDA(Array,[Column_Numbers],[By_Col],
      LET(Arr, Array,          nCol?, Column_Numbers,          Col?, By_Col,
             NumScan, AND(nCol?<>{1,0}),          NumMSG, "Column_Numbers=1,0",
             ColScan, AND(Col?<>{1,0}),          ColMSG, "By_Col=1,0",
             ERRORS, IFS(NumScan, NumMSG,  ColScan,  ColMSG,  1, 0),
             RowCt, CELLCOUNT(Arr, 1),          ColCt, CELLCOUNT(Arr, 2),          TotCt, CELLCOUNT(Arr),
             RowSel, MAKEARRAY(TotCt,, LAMBDA(R,C,   IF(Col?, MOD(R-1, RowCt)+1,  R/ColCt))),
             ColSel, MAKEARRAY(TotCt,, LAMBDA(R,C,   IF(Col?, R/RowCt,  MOD(R-1, ColCt)+1))),
             Result, ROUNDUP(IF(nCol?=1, ColSel,  RowSel),),          Return, IF(ERRORS<>0, ERRORS,  Result),
             Return
       )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I
1		AINDEXERL
2
3		Original Data			Column_Numbers:			Result
4		1	#VALUE!		By_Col:			1
5		2						1
6		3	4					2
7								2
8		Row numbers are returned						3
9		Formula in cell H4☛ =AINDEXERL(B4:C6, F3, F4)						3
10
11
12
13		Original Data			Column_Numbers:	0		Result
14		1	#VALUE!		By_Col:	1		1
15		2						2
16		3	4					3
17								1
18		Column numbers are returned						2
19		Formula in cell H14☛ =AINDEXERL(B14:C16, F13, F14)						3
20
21
22
23		Original Data			Column_Numbers:	1		Result
24		1	#VALUE!		By_Col:	0		1
25		2						2
26		3	4					1
27								2
28		Column numbers are returned						1
29		Formula in cell H24☛ =AINDEXERL(B24:C26, F23, F24)						2
30
31
32
33		Original Data			Column_Numbers:	1		Result
34		1	#VALUE!		By_Col:	1		1
35		2						1
36		3	4					1
37								2
38		Column numbers are returned						2
39		Formula in cell H34☛ =AINDEXERL(B34:C36, F33, F34)						2
40
AINDEXERL

Cell Formulas
Range	Formula
H4:H9,H34:H39,H24:H29,H14:H19	H4	=AINDEXERL(B4:C6, F3, F4)
B9,B39,B29,B19	B9	=AFORMULATEXT(H4)
Dynamic array formulas.

 

[jaeiow]

This returns #Valueerror. Did something change?

 

[jaeiow]

I traced it back to the updated CellCount, which calls Avalue, and the formula for AINDEXERL updates to:

=LAMBDA(Array,[Column_Numbers],[By_Col],
      LET(Arr, Array,          nCol?, Column_Numbers,          Col?, By_Col,
             NumScan, AND(nCol?<>{1,0}),          NumMSG, "Column_Numbers=1,0",
             ColScan, AND(Col?<>{1,0}),          ColMSG, "By_Col=1,0",
             ERRORS, IFS(NumScan, NumMSG,  ColScan,  ColMSG,  1, 0),
             RowCt, CELLCOUNT(Arr, 1),          ColCt, CELLCOUNT(Arr, 2),          TotCt, CELLCOUNT(Arr, 0),
             RowSel, MAKEARRAY(TotCt,, LAMBDA(R,C,   IF(Col?, MOD(R-1, RowCt)+1,  R/ColCt))),
             ColSel, MAKEARRAY(TotCt,, LAMBDA(R,C,   IF(Col?, R/RowCt,  MOD(R-1, ColCt)+1))),
             Result, ROUNDUP(IF(nCol?=1, ColSel,  RowSel),),          Return, IF(ERRORS<>0, ERRORS,  Result),
             Return
       )
  )