Dear Gurus . ...

I have a worksheet with 5 columns and 400 rows. All contents are
sentences.
I use Vlookup but it only can show result that matches the input
exactly.
How to ask vlookup to retrieve all the sentences that match my single
input?
And how to ask vlookup shows multiple searching result? (It stops after
getting the first from top)

Please help. Thanks in advance.

YJL


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

Provide some examples. Be very specific!

Biff

"YJL" wrote in message
...

Dear Gurus . ...

I have a worksheet with 5 columns and 400 rows. All contents are
sentences.
I use Vlookup but it only can show result that matches the input
exactly.
How to ask vlookup to retrieve all the sentences that match my single
input?
And how to ask vlookup shows multiple searching result? (It stops after
getting the first from top)

Please help. Thanks in advance.

YJL


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

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