Find, return string address in a range of cells with numbers and text

Please I need a formula to look for a string in a range of cells that can have text, numbers or both, and return the cell address. In the example below I need in B1 to have a formula that will look after this string "Sub-Total by Work Function:" in the range A1:A22. I tried =LEFT and SEARCH functions but did not work. I can't use as search string the whole phrase (Sub-Total by Work Function: Production, Sub-Total by Work Function: Studio) etc because I have a few hundreds Work functions. That's why I need to find the cells that have only the beginning of the string.

A1 Accnt Serv
A2 2.00
A3 3.55
A4 Editorial
A5 Sub-Total by employee
A6 Sub-Total by Work Function: Production
....

A10 Sub-Total by Work Function: Studio

A22 Sub-Total by Work Function: Managers

Thank you so much

Vera

I thought that this a tough one only for me but it seems that is a really difficult problem.

Thank you

I will try to upload a screen shot of the spreadsheet. I made a few changes hoping that will make more sense.

Thank you.

=ADDRESS((SMALL(IF(LEFT(A1:A616,27)="sub-Total by Work Function:",ROW(A1:A616),FALSE), 1 )),COLUMN(A1))

as an array or if the textstring is in e22
=ADDRESS((SMALL(IF(LEFT(A1:A616,LEN(E22))=E22,ROW(A1:A616),FALSE), 1 )),COLUMN(A1))

Regards

Dav

Thank you a million times Dav, it's working perfectly.

But ADDRESS doesn't give the values you want does it? Are you adding INDIRECT to get the value?

Personally I dislike that approach, ADDRESS is almost always unnecessary in these situations, you could try this formula in G2 copied down as far as necessary....

=IF(COUNTIF(A$1:A$616,"sub-total*")>=ROWS(G$2:G2),INDEX(C$1:C$616,SMALL(IF( LEFT(A$1:A$616,9)="sub-total",ROW(A$1:A$616)-ROW(A$1)+1),ROWS(G$2:G2))),"")

confirmed with CTRL+SHIFT+ENTER