AINDEXER

[schardt679]

AINDEXER returns 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 ARRAYCOLUMNS & ARRAYROWS for the inspiration (from Mr. Excel's UNIQUE of Rectangular Range Using MAKEARRAY in Excel - 2416).
ARRAYCOLUMNS(Array) =LAMBDA(array,SEQUENCE(,COLUMNS(array))*SEQUENCE(ROWS(array),1,1,0)).
ARRAYROWS(array) =LAMBDA(array,SEQUENCE(ROWS(array))*SEQUENCE(,COLUMNS(array),1,0)).
Calls CELLCOUNT.
Other functions on minisheet: AFORMULATEXT.

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

=LAMBDA(Array,[Column_Numbers],
      LET(Arr, Array,          Col?, Column_Numbers,
             ColScan, AND(Col?<>{1,0}),          ColMSG, "Column_Numbers=1,0",
             RowCt, CELLCOUNT(Arr, 1),          ColCt, CELLCOUNT(Arr, 2),
             Result, MAKEARRAY(RowCt, ColCt, LAMBDA(R,C, IF(Col?, C,  R))),
             Return, IF(ColScan, ColMSG,  Result),
             Return
     )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I
1		AINDEXER
2
3		Original Data			Column_Numbers		Result
4		1	#VALUE!		0		1	1
5		2					2	2
6		3	8				3	3
7		4	9				4	4
8		5	10				5	5
9
10		Row numbers are returned
11		Formula in cell G4☛ =AINDEXER(B4:C8, E4)
12
13
14
15		AINDEXER
16
17		Original Data			Column_Numbers		Result
18		1	#VALUE!		1		1	2
19		2					1	2
20		3	8				1	2
21		4	9				1	2
22		5	10				1	2
23
24		Column numbers are returned
25		Formula in cell G18☛ =AINDEXER(B18:C22, E18)
26
AINDEXER

Cell Formulas
Range	Formula
G4:H8,G18:H22	G4	=AINDEXER(B4:C8, E4)
B11,B25	B11	=AFORMULATEXT(G4)
Dynamic array formulas.