Slight correction:

=IF(COUNTIF(A$1:A$4,"*abc*")=ROWS(A$1:A1),INDEX(A $1:A$4,SMALL(IF(ISNUMBER(SEARCH("abc",A$1:A$4)),(R OW(A$1:A$4)-ROW(A$1))+1),ROWS(A$1:A1))),"")

Biff

"Biff" wrote in message
...
Hi!

I think you'd be better off using a filter.

That being said, here's a formula that will extract all of the entries
from column A that contain the substring "abc".

Based on your posted sample of data in A1:A4.

Array entered using the key combo of CTRL,SHIFT,ENTER:

=IF(COUNTIF(A$1:A$4,"*abc*")=ROWS(A$1:A1),INDEX(A $1:A$4,SMALL(IF(ISNUMBER(SEARCH("abc",A$1:A$4)),(R OW(A$1:A$4)-1)+ROW(A$1)),ROWS(A$1:A1))),"")

Copy down until you get blanks.

Will return:

abcde
edabc

Biff

"YJL" wrote in message
...

Thanks for your reply.

Vlookup can only search the leftmost column of a range-- In that case,
if i only want to search the leftmost column, can I do this?

Example.

I want to search "abc"

Data

A B C D E
1 abcde fghij klmno pqrst uvwxy

2 fghij klmno pqrst uvwxy abcde

3 klmno pqrst uvwxy pqrst uvwxy

4 edabc fghij klmno pqrst uvwxy