How to spill multiple results with MATCH?

[Rnkhch]

Hello,

I was just playing with MATCH, and I noticed it only returns the position of the first match it finds. I was trying to make it spill out all matching positions, but didn't get anywhere I thought it would be pretty simple, but I might need more coffee

Blank power workbook1.xlsx
	A	B	C	D	E	F	G	H	I	J
1	1	2	3	4	1		actual result:	1
2							intended result:	1	5
3
4	a	b	c	d	a		actual result:	1
5							intended result:	1	5
6
Sheet7

Cell Formulas
Range	Formula
H1	H1	=MATCH(1,A1:E1,0)
H4	H4	=MATCH("a",A4:E4,0)

Thanks for any input!

 

[Fluff]

The Match function will only ever find the 1st match, that is what it's designed to do. You would need to use the Filter function to get all the matches.

 

[ISY]

Hi

=XLOOKUP(1,A1:E1,COLUMN(A1:E1))
=XLOOKUP(1,A1:E1,COLUMN(A1:E1),,,-1)

 

[Rnkhch]

Thanks all!

@Fluff How about this:

Blank power workbook1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4	1	1		result:	1	5	6
2
3	a	b	a	d	e	a		result:	1	3	6
4
Sheet7

Cell Formulas
Range	Formula
I1:K1	I1	=LET(a,A1:F1,f,IF(a=1,1,0)*SEQUENCE(,COUNT(IFERROR((a)/1,1)),1,1),FILTER(f,f>0))
I3:K3	I3	=LET(a,A3:F3,f,IF(a="a",1,0)*SEQUENCE(,COUNT(IFERROR((a)/1,1)),1,1),FILTER(f,f>0))
Dynamic array formulas.

 

[Fluff]

I would use

=FILTER(COLUMN(A1:F1),A1:F1=1)

 

[Rnkhch]

That seems to work only if the cells start at column A, so I adjusted your formula like this:

Blank power workbook1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			1	2	3	4	1	1		result:	1	5	6
2											3	7	8
3											1	5	6
4
5			a	b	a	d	e	a		result:	1	3	6
6											3	5	8
7											1	3	6
8
9		1
10		3
11		8
12		1
13		2
14		1
15		5
16
17		result:	1	1
18			4	4
19			6	6
20
21
Sheet7

Cell Formulas
Range	Formula
K1:M1	K1	=LET(a,C1:H1,f,IF(a=1,1,0)*SEQUENCE(,COUNT(IFERROR((a)/1,1)),1,1),FILTER(f,f>0))
K2:M2	K2	=FILTER(COLUMN(C1:H1),C1:H1=1)
K3:M3	K3	=LET(r,C1:H1,cr,COLUMN(r),rr,ROW(r),s,IF(COUNT(cr)>COUNT(rr),COLUMN(r),ROW(r)),FILTER(s-MIN(s)+1,r=1))
K5:M5	K5	=LET(a,C5:H5,f,IF(a="a",1,0)*SEQUENCE(,COUNT(IFERROR((a)/1,1)),1,1),FILTER(f,f>0))
K6:M6	K6	=FILTER(COLUMN(C5:H5),C5:H5="a")
K7:M7	K7	=LET(r,C5:H5,cr,COLUMN(r),rr,ROW(r),s,IF(COUNT(cr)>COUNT(rr),COLUMN(r),ROW(r)),FILTER(s-MIN(s)+1,r="a"))
C17:C19	C17	=FILTER(ROW(B9:B15)-MIN(ROW(B9:B15)-1),B9:B15=1)
D17:D19	D17	=LET(r,B9:B15,cr,COLUMN(r),rr,ROW(r),s,IF(COUNT(cr)>COUNT(rr),COLUMN(r),ROW(r)),FILTER(s-MIN(s)+1,r=1))
Dynamic array formulas.

Now it can recognize if the range is horizontal or vertical

 

[Fluff]

In that case I would use

=LET(a,C1:H1,FILTER(SEQUENCE(,COLUMNS(a)),a=1))

 

[Rnkhch]

I see. With your latest formula, I was trying to make it automatically recognize whether the range is horizontal or vertical. Is this the best way, or there is a better way:

Blank power workbook1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			1	2	3	4	1	1		result:	1	5	6
2
3			a	b	a	d	e	a		result:	1	3	6
4
5		a
6		3
7		8
8		1
9		a
10		1
11		5
12
13		result:	4	1
14			6	5
15
16
17
Sheet8

Cell Formulas
Range	Formula
K1:M1	K1	=LET(a,C1:H1,IFERROR(FILTER(SEQUENCE(,COLUMNS(a)),a=1),FILTER(SEQUENCE(ROWS(a),),a=1)))
K3:M3	K3	=LET(a,C3:H3,IFERROR(FILTER(SEQUENCE(,COLUMNS(a)),a="a"),FILTER(SEQUENCE(ROWS(a),),a="a")))
C13:C14	C13	=LET(a,B5:B11,IFERROR(FILTER(SEQUENCE(,COLUMNS(a)),a=1),FILTER(SEQUENCE(ROWS(a),),a=1)))
D13:D14	D13	=LET(a,B5:B11,IFERROR(FILTER(SEQUENCE(,COLUMNS(a)),a="a"),FILTER(SEQUENCE(ROWS(a),),a="a")))
Dynamic array formulas.

 

[Fluff]

Personally, I would just use two different formulae, rather than making things far more complex than they need to be.

 

[Rnkhch]

I wanted to make a convenient LAMBDA that would automatically work whether the selected range is horizontal or vertical

I turned your formula into this, which works very well:

=LET(a,C1:H1,hv,IF(COLUMNS(a)>ROWS(a),"h","v"),FILTER(SWITCH(hv,"h",SEQUENCE(,COLUMNS(a)),SEQUENCE(ROWSS(a),)),a=1))

Thank you!