Spilled array formula to match column for minimum of each row in a 2D table

[jrhouse5]

I have a 2D table of timeseries data. I am able to use the following equation in column B: "=BYROW(Value_Array,LAMBDA(x,MIN(x)))" to get the minimum value for each row that results in an array output. In column C the following does not work "=BYROW(Value_Array,LAMBDA(x,MATCH(B6#,x,0)))" to find the column (time period) where the minimum value happened and results in a spilled array.

I very much like the array results because the data table size is always changing, and it is easier to let excel automatically adjust to the correct size as needed. I am sharing with others of varying abilities, so VBA is a no-no.

Is there an excel formula that can match column with the minimum value for each row of the table and results in a spilled range output?

I tried using B6# (spilled array) in the match function in column C per the following: =BYROW(Value_Array,LAMBDA(x,MATCH(B6#,x,0))) and expecting a spilled array but get a #CALC! error. If I replace B6# with just B6, the results are a spilled array with only the first row resulting in the correct value and remaining rows with #N/A errors.

 

[Anonymous1378]

Perhaps try

=BYROW(IF(Value_Array=B6#,B6#,""),LAMBDA(x,XLOOKUP(TRUE,x<>"",x)))

Your formula is asking excel to check each row for the B6#, which causes the BYROW() result for each row to output an array. Since BYROW() cannot output an array for each row it is checking, you get a CALC# error.

 

[Rick Rothstein]

This formula in cell C6 seems to work...

=BYROW(HSTACK(B6#,Value_Array),LAMBDA(r,XLOOKUP(TAKE(r,,1),TAKE(r,,-20),E3:X3)))

 

[Anonymous1378]

Oh, if you wanted a match output, replace the B6# in the last formula with either E3:X3 or SEQUENCE(1,COLUMNS(Value_Array)) instead, like:

=BYROW(IF(Value_Array=B6#,E3:X3,""),LAMBDA(x,XLOOKUP(TRUE,x<>"",x)))

 

[jrhouse5]

Thank you Anonymous1378, the =BYROW(IF(Value_Array=B6#,Time_Array,""),LAMBDA(x,XLOOKUP(TRUE,x<>"",x))) works! Now, I need to try to understand the logic of this equation.

 

[jrhouse5]

working example. Thank you again Anonymous1378.

 

[duggie33]

You all had this figured out and, moved on. I lost track of the post and kept working on it when I had time. I had to learn how to use BYROW better...

Here is my solution...

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1				Time Array
2				1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
3	Min Value	Time Array @ Min		Value Array
4	2.88	6		9.92	10.96	15.87	15.09	19.38	2.88	8.75	12.06	4.65	5.2	9.94	21.06	25.35	38.56	33.53	5.9	32.18	6.99	23.42	24.05
5	19.78	13		24.64	39.49	24.15	38.6	37.93	25.51	25.77	39.79	77.97	38.87	53.55	23.9	19.78	191	161.63	128.82	144.31	114.61	123.24	306
6	11.53	5		49.75	29.45	26.08	53.92	11.53	28.49	41.89	41.7	65.85	55.29	24.12	20.25	112.15	101.71	122.06	53.9	58.22	39.54	123.85	44.9
7	0.15	20		14.49	36.57	25.99	18.17	48.39	1.24	62.07	71.6	19.49	50.37	53.14	53.39	39.91	43.24	62.41	79.81	2.19	73.78	13.59	0.15
8	4.20	4		48.71	19.69	10.58	4.2	19.55	88.02	75.15	77.99	121.52	34.91	30.04	114.9	330.86	362.95	177.24	11.01	82.31	509.51	246.58	205.13
9	9.80	2		20.97	9.8	17.19	79.94	126.99	77.15	67.06	42.09	276.35	308.33	342.02	54.74	188.44	212.71	199.17	92.34	873.95	238.86	61.46	1,238.98
10	1.25	6		39.87	7.68	14.11	29.52	41.34	1.25	23.71	91.18	126.84	115.43	140.91	109.64	101.84	87.29	396.91	348.64	16.69	418.69	339.92	525.05
11	1.18	4		11.99	10.09	8.33	1.18	2.36	14.55	22.7	23.62	16.19	28.15	2.85	10	49.2	107.19	82.65	7.18	25.16	18.89	150.48	195.75
12	0.08	4		8.16	2.97	4.19	0.08	10.58	11.3	8.54	5.4	20.57	61.43	47.94	2.26	6.51	34.1	82.8	104.4	149.77	126.65	99.16	236.13
Sheet5

Cell Formulas
Range	Formula
A4:A12	A4	=BYROW(value_array,LAMBDA(x,MIN(x)))
B4:B12	B4	=BYROW(value_array,LAMBDA(x,MATCH(MIN(x),x,0)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
value_array	=Sheet5!$D$4:$W$12	A4:B4

For what it's worth,

Doug