Here is my 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

Hi!

Ok, I'm not sure what you're trying to do.

Vlookup can only search the leftmost column of a range and return data from
the nth column to the right.

So, Vlookup will not do what I think you want.

Have you tried filtering?

Biff

"YJL" wrote in message
...

Here is my 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

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

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

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

Sorry Biff,Could you tell me how to input the key combo
enter+shift+ctrl?? and where should i put the formula you mentioned?

YJL


--
YJL
------------------------------------------------------------------------
YJL's Profile: http://www.excelforum.com/member.php...o&userid=28693
View this thread: http://www.excelforum.com/showthread...hreadid=484200

Hi!

where should i put the formula you mentioned?

Put it where you wanted to put the Vlookup formula. Since you're returning
possibly more than a single result you have to drag copy down to more cells
in order to extract all the data that meets the criteria.

tell me how to input the key combo enter+shift+ctrl??

Type the formula. Instead of just hitting the ENTER key, hold down the CTRL
key and the SHIFT key then hit ENTER.

Biff

"YJL" wrote in message
...

Sorry Biff,Could you tell me how to input the key combo
enter+shift+ctrl?? and where should i put the formula you mentioned?

YJL


--
YJL
------------------------------------------------------------------------
YJL's Profile:
http://www.excelforum.com/member.php...o&userid=28693
View this thread: http://www.excelforum.com/showthread...hreadid=484200