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!
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!