On Feb 17, 2:47 am, "Max" wrote:
One way ..

Assuming source data within A2:A7
with the text to search entered in B2: car

Then in say, C2, array-entered*:
="A"&MATCH(TRUE,ISNUMBER(SEARCH(B2,A2:A7)),0)+1
returns the 1st instance: A2

And in say, C3, array-entered*:
="A"&MAX((ISNUMBER(SEARCH(B2,A2:A7)))*ROW(A2:A7 ))
returns the last instance: A5

*Press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER

Replace SEARCH with FIND if you need the search to be case sensitive. SEARCH
is not case sensitive.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
wrote in message

ps.com...

I need a formula that returns the cell location of the first instance
of a text string that I specify in a column listing. I also need a
formula that returns the cell location of the last instance of a text
string I specify in a column. For example lets say the text I want to
look for is 'car', The first formula would return A2 and the other
formula would return A5. I hope that makes sense. Can anyone help?

A1
A2 cars and trucks
A3 vans
A4 truck and racecar
A5 red car
A6 bean
A7 rice

Thanks!

Thanks for the responses! Are there similar formulas just to return
the row number instead of the the complete cell address? Preferably
without using arrays.

Thanks again!