dynamic array formula needed

[Scott R]

Say that A1:C3 contains 1,2,3;6,5,4;8,7,9 and E1 = A1:C3 making E1 a dynamic cell.

In I1, I've got =SUBTOTAL(5, OFFSET(E1#, SEQUENCE(3,,0),, 1)) to find the minimum value of each row in the 3x3 matrix, or 1;4;7. This is also a dynamic cell.

I'm trying to build a dynamic array formula, using both dynamic cells E1 and I1, to return the column position of the row's minimum value, or in this example 1;3;2
1 is the minimum in row 1 which is in column position 1
4 is the minimum in row 2 which is in column position 3
7 is the minimum in row 3 which is in column position 2

I've tried combinations of INDEX, MATCH, OFFSET, SEQUENCE, etc. but no luck.

Thanks for the help!

 

[Fluff]

Do you really need the helper cells, you could just use

=BYROW(A1:C3,LAMBDA(br,FILTER({1,2,3},br=MIN(br))))

 

[Scott R]

No, helper cells not required.
My Excel 365 doesn't have BYROW or LAMBDA unfortunately. Killing me.

 

[Fluff]

In that case off-hand I can't think of any way to do it so that it spills down.

 

[Scott R]

Thx for looking. I can't seem to find one either.

Guess that's why there's a BYROW function.

 

[Scott R]

Found a solution....

I put column positions in a dynamic array; in K1: =COLUMN(E1#) - COLUMN(E1) + 1

Then in O1: =SUMIF(OFFSET(E1#, SEQUENCE(ROWS(E1#),, 0),, 1), I1#, K1#)

A bit of a journey but it does what I need it to do.

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Scott R]

The SUMIF only works because the values in my dataset (E1#) are never identical by row.

 

[Peter_SSs]

the values in my dataset (E1#) are never identical by row.

Would this work for you then without helpers?

22 10 08.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	1	2	3		1	2	3		1						1
2	6	5	4		6	5	4		4						3
3	8	7	9		8	7	9		7						2
Scott R

Cell Formulas
Range	Formula
E1:G3	E1	=A1:C3
I1:I3	I1	=SUBTOTAL(5, OFFSET(E1#, SEQUENCE(3,,0),,1))
O1:O3	O1	=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,IF(I1#=E1#,COLUMN(E1#)-COLUMN(E1)+1,""))&"</c></p>","//c")
Dynamic array formulas.