MATCH (multiple results)

[Shahin Ghaffari]

Hi

in the attachment, I used MATCH to find the row number of a special cell named "shahin" but there are a lot of "shahin" and MATCH only shows the FIRST row number. how can I show all the cell numbers that contains "shahin"?

 

[bosco_yip]

Try,

In C2, formula copied down :

=IFERROR(AGGREGATE(15,6,ROW(A$1:A$13)/(A$1:A$13="shahin"),ROWS($1:1)),"")

Regards
Bosco

 

[GraH - Guido]

Alternative with a Power Query solution could be

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Column1 = Source[Column1],
  Custom1 = List.Transform(List.PositionOf(Column1, "shahin", Occurrence.All), each _ +1)
in
  Custom1

Requires List.Transform since Power Query is base 0 this starts counting at zero not one.

 

[Peter Bartholomew]

I am really looking forward to being able to deploy the new dynamic arrays!
= FILTER( list, ISTEXT(list) )
returns a filtered version of the list itself showing all text content.

Equivalently,
= FILTER( SEQUENCE(ROWS(list)), list="Shahin" )
will return the record numbers of the matched values.

 

[Shahin Ghaffari]

Try,

In C2, formula copied down :

=IFERROR(AGGREGATE(15,6,ROW(A$1:A$13)/(A$1:A$13="shahin"),ROWS($1:1)),"")

Regards
Bosco

thank you soooooooooooooooooooo much. it works