Finding column number of "x" using a SPILL formula for each row

[rizzo93]

I want to find the column number of "x".

	A​	B​	C​	D​	E​	F​	H​
1	Plan​	Department​	1​	2​	3​	4​	6​
2	Alpha	Red	x​			​
3	Beta	Orange		​	x​
4	Gamma	Blue			x​	​
5	Omega	Green					x​
6	Theta	Yellow	x​
7	Pi	Pink	x​

Column B is a spill formula. To the right of column H, I want another spill formula to find which column the "x" is in for each row. I can do that in a non-spill way, but I want the formula to be dynamic so that it knows how many rows are involved based on what column B produces.

This is the formula I'm using:

=LET(r,ROWS(B2#),s,SEQUENCE(r),IF(s<=r,MATCH("x",C2:H2,0)))

While the results do spill, they only come up as "1" as a result, not the actual column number.

What am I missing please?

 

[Eric W]

Your problem is that the formula only looks at C2:H2, it does not adapt for subsequent rows. Something like this:

Book3
	A	B	C	D	E	F	G	H	I
1	Plan	Department	1	2	3	4	5	6	Column
2	Alpha	Red	x						1
3	Beta	Orange			x				3
4	Gamma	Blue			x				3
5	Omega	Green						x	6
6	Theta	Yellow	x						1
7	Pi	Pink	x						1
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=A14:A19
I2:I7	I2	=IFERROR(INDEX(C1:H1,1/(1/MMULT(--(C2:INDEX(H:H,ROWS(B2#)+1)="x"),SEQUENCE(ROWS(B2#))))),"No x")
Dynamic array formulas.

This assumes at most 1 x per row. If you have the BYROW function, there may be better options.

 

[rizzo93]

Thanks for the assist, Eric W.