How can I use MATCH for multiple rows and get a spill?

[Rnkhch]

Hello,

Suppose I have an array as shown in the attached XL2BB, and I want to spill the positions of the cells that have the value of say 34. MATCH takes only 1D arrays, so I was trying to use MAP, but I couldn't get it to work It seems that MAP only takes functions that take 2D arrays?

Blank power workbook1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2											desired output
3		44	99	34	82	73	77		#N/A		3
4		56	84	64	34	69	38				4
5		57	34	36	53	38	13				2
6		22	30	100	34	61	54				4
7		96	16	25	79	10	34				6
8		37	90	20	34	19	54				4
9		17	12	36	51	15	34				6
10		53	47	13	34	34	43				4
11		34	33	72	79	53	54				1
12
Sheet2

Cell Formulas
Range	Formula
I3	I3	=MATCH(34,B3:G11,0)

Thanks for any input!

 

[Eric W]

I don't have BYROW on my PC, so I can't test this, but you might want to try BYROW:

=BYROW(B3:G11, LAMBDA(array, MATCH(34,array,0)))

 

[hajiali]

Book1
	I
3	3
4	4
5	2
6	4
7	6
8	4
9	6
10	4
11	1
Sheet3

Cell Formulas
Range	Formula
I3:I11	I3	=MATCH(34,B3:G3,0)

 

[Rnkhch]

@Eric W Ah right, I completely forgot about BYROW. It works very well. Thanks!

@hajiali Thanks. I knew about the dragging down, but I was looking for a spill formula.

 

[kvsrinivasamurthy]

Try. In I3

=MMULT(IF(B3:G11=C1,COLUMN(B3:G11)-COLUMN(A1),0),TRANSPOSE(1*(COLUMN(B3:G3)>0)))

If there is no match formula returns 0.

 

[kvsrinivasamurthy]

In the above post C1=34

 

[Rnkhch]

Awesome, thanks! This can be helpful in more complex functions that BYROW may not like. I changed your function like this:

=LET(r,B3:G11,l,34,x,COLUMN(r),MMULT(IF(r=l,x-MIN(x)+1,0),SEQUENCE(COLUMNS(r),,1,0)))

 

[kvsrinivasamurthy]

Ok. I think your problem is solved.

 

[Eric W]

Just a thought - the latest MMULT formulas have a potential issue if there is more than one matching value on a given row. For example, row 10:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1			34
2									kvsrinivasamurthy	Rnkhch	Eric W
3		44	99	34	82	73	77		3	3	3
4		56	84	64	34	69	38		4	4	4
5		57	34	36	53	38	13		2	2	2
6		22	30	100	34	61	54		4	4	4
7		96	16	25	79	10	34		6	6	6
8		37	90	20	34	19	54		4	4	4
9		17	12	36	51	15	34		6	6	6
10		53	47	13	34	34	43		9	9	4
11		34	33	72	79	53	54		1	1	1
Sheet1

Cell Formulas
Range	Formula
I3:I11	I3	=MMULT(IF(B3:G11=C1,COLUMN(B3:G11)-COLUMN(A1),0),TRANSPOSE(1*(COLUMN(B3:G3)>0)))
J3:J11	J3	=LET(r,B3:G11,l,34,x,COLUMN(r),MMULT(IF(r=l,x-MIN(x)+1,0),SEQUENCE(COLUMNS(r),,1,0)))
K3:K11	K3	=LET(rng,B3:G11,v,34,sa,SEQUENCE(ROWS(rng),,0),sb,SEQUENCE(,COLUMNS(rng)),sc,TRANSPOSE(sb),MMULT((rng=v)*(COUNTIF(OFFSET(rng,sa,0,1,sb),v)=1),sc))
Dynamic array formulas.

I found a way to return the value of the first matching column, instead of the sum of the matching columns. It's pretty unwieldy though.

 

[Rnkhch]

Thank you so much Eric for catching that! And great formula!