Index small if row (match if begins with....

[vasim]

In the formula

=Index(range1,small(if(range2=cell,row(range2)),row())

when dragged gives me the first cell match, second cell match & so on.... is it possible to get match that begins with or contains...something like

=Index(range1,small(if(range2=cell&"*",row(range2)),row())
=Index(range1,small(if(range2="*"&cell&"*",row(range2)),row())

Both the above doesnt work...any help

 

[Faseeh]

Hi Vasim,

Please try this file:

 

[vasim]

Thanks Faseeh....From the two questions, for the second this is exactly what I was looking for...
This can be used for "contains".....any idea how can we incorporate for "begins with" .....

THANKS AGAIN...

 

[Faseeh]

Hi vasim,

This worksheet also lists entries that starts with AA. Plz check that.

 

[vasim]

Thanks Faseeh, the list entries present 1)AA-BBC & 2)BBC-AA for the lookup value as AA, however I was looking to get only AA-BBC & not BBC-AA as AA-BBC starts with AA (then the second match that starts with AA), whereas BBC-AA starts with BB and not the lookup value (AA).....

(EDIT Added - to entries to avoid confusion...)

 

[Somendra Misra]

Hi Vasim...

See if this formula solve your purpose...

=IFERROR(INDEX($A$1:$A$6,AGGREGATE(15,6,IF(SEARCH($C$1&"*",$A$1:$A$6)=1,ROW($A$1:$A$6)),ROWS($A$1:A1))),"")

Enter the Ctrl+Shift+Enter.

I had attached the file also. It will give all the data starting with the criteria set in Blue Cell.

Somendra.

 

[Somendra Misra]

Sorry.Forgot to upload the file.
Somendra.

 

[vasim]

Awesome........only one word.....PERFECT......thanks