VLookup for find Text in Column

[NKRA]

I searched the website and could not find solution, I used Vlookup and List function but could not get the result.

I am trying to populate in Sheet 1 - Column B the values found in Column A of Sheet 2 with comma.

Sheet 1 - Column A
Names
AMY Goldy ANNA
George Leena
Geena Amy Shawn
Andrew Anna
Deborah
Victor
Carlos
Nancy Goldy
juilo

Sheet2 - Column B
Names
Amy
Anna
Goldy
Victor

Whats best way to do ?

Thnx
NKRA

[martindwilson]

?????what result do you excpect

[NKRA]

I want pull info attached in attached

NKRA

[NKRA]

Any suggestion or guidance to help on this?

[NKRA]

I tried with this formula but i cannot get the result i have in excel work book
=INDEX(Sheet2!$A$2:$A$5,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(Sheet2!$A$2:$A$5,A9)),0),0))

[NBVC]

You can do this with a free addin downloaded from here: Morefunc.

Then applying formula:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and not just enter and copied down

or you can use a UDF like (ALT+F11, then Insert|Module and Paste the below code):

Please Login or Register  to view this content.

and applying formula:

Please Login or Register  to view this content.

and confirmed with CTRL+SHIFT+ENTER and not just ENTER and copied down

NOTE: If you change anything in the formula, like references and ranges, then you must reconfirm with CSE key combination before copying down.

[NKRA]

It doesnot give result like the one I am looking in attached

[NBVC]

Actually, it does.

See attached.

[NKRA]

Yes it worked, Thnx for update. There is no way we could without addin?

[NBVC]

I did not use the addin... I used a user defined VBA function.

[NKRA]

Further to this solution, I used this formula to pull certain words out of text which it did perfectly but at some places it pulled word text though it was not a direct match. Any resolution to that.

Attached example.

[NBVC]

Try this revised formula:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER

[NKRA]

I tried looking into forum could not find solution in 2007 excel shift+control+enter does not work it results in #Name?..

folks any suggestion?

NKRA

[NBVC]

It is the MCONCAT function probably giving you that error.. you need to download the addin from the link in post #6 or if you use the aconcat() udf, you need to add the macro to your VB editor in XL2007, if you started fresh.