Search for specific text and give multiple values

[Thomas Kuriakose]

Respected Sirs,

We have a list of values in the data tab and this needs to be summarized in the Search tab as follows -

1. Search Value is the specific ID
2. Return value is multiple values adjacent to the ID for the specific ID

Kindly find attached the workbook where I have used the following formula -

{=INDEX(Data!$B$2:$B$238,SMALL(IF(ISNUMBER(SEARCH($B$1,Data!$A$2:$A$238)),MATCH(ROW(Data!$A$2:$A$238),ROW(Data!A2:A238))),ROW(Data!A1)))}

This is not giving the correct values when the text string is matching in other rows. Please let me know how to get the correct value in this case.

For example it is taking any text which has M10, where as we need result only for M10_CALL.

Thank you very much,

with regards,
thomas

 

[Chihiro]

=INDEX(Data!$B$2:$B$238,SMALL(IF(Data!$A$2:$A$238=Search!$B$1,ROW($A$2:$A$238)-1),ROW(A1)))
Confirmed as array (CSE).

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the correct formula provided.

Much appreciated.

with regards,
thomas

 

[bosco_yip]

Another option in non-array formula include error trap.

In C3, formula copy down :

=INDEX(Data!B$1:B$15000,SMALL(MMULT((Data!A$2:A$500<>$B$1)/1%%+ROW($2:$500),1),ROWS($1:1)))&""

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this option provided.

Thanks once again,

with regards,
thomas

 

[bines53]

Another option,

=INDEX(Data!$B$2:$B$238,AGGREGATE(15,6,ROW(Data!$B$2:$B$238)-1/(Data!$A$2:$A$238=$B$1),ROW(A1)),0)

David

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this option.

Much appreciated.

with regards,
thomas