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



## Rnkhch (Dec 17, 2022)

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 workbook1ABCDEFGHIJKL12desired output3449934827377#N/A3456846434693845573436533813262230100346154479616257910346837902034195449171236511534610534713343443411343372795354112Sheet2Cell FormulasRangeFormulaI3I3=MATCH(34,B3:G11,0)

Thanks for any input! 🤗


----------



## Eric W (Dec 17, 2022)

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 (Dec 17, 2022)

Book1I33445264768496104111Sheet3Cell FormulasRangeFormulaI3:I11I3=MATCH(34,B3:G3,0)


----------



## Rnkhch (Dec 17, 2022)

@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 (Dec 17, 2022)

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 (Dec 18, 2022)

In the above post C1=34


----------



## Rnkhch (Dec 18, 2022)

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 (Dec 18, 2022)

Ok. I think your problem is solved.


----------



## Eric W (Dec 18, 2022)

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:

Book1ABCDEFGHIJK1342kvsrinivasamurthyRnkhchEric W344993482737733345684643469384445573436533813222622301003461544447961625791034666837902034195444491712365115346661053471334344399411343372795354111Sheet1Cell FormulasRangeFormulaI3:I11I3=MMULT(IF(B3:G11=C1,COLUMN(B3:G11)-COLUMN(A1),0),TRANSPOSE(1*(COLUMN(B3:G3)>0)))J3:J11J3=LET(r,B3:G11,l,34,x,COLUMN(r),MMULT(IF(r=l,x-MIN(x)+1,0),SEQUENCE(COLUMNS(r),,1,0)))K3:K11K3=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 (Dec 19, 2022)

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


----------

