URGENT -- search in string for a value in another column, if found, return value from next column

[samkshah]

Hello ALL!

Need an urgent help on this! I am not an excel pro, however can do a
bit of troubleshooting if being helped.

I have 2 different worksheets..# 1 and # 2...I want to search each cell
from column A, worksheet#1 and see if it has any value from column A,
worksheet#2. Each cell in Column A, worksheet#1 has strings and not one
word values. So basically I want to search these strings to see if
those have any word that is also in column A, worksheet # 2. If it
finds one, then returns respective value form column B, worksheet #2 to
respective column B, worksheet # 1..I used this code on B1 cell,
however to make it worked, column A-worksheet#1 has to have single word
values and not strings I believe:

=IF(ISNA(VLOOKUP(A1,'worksheet2'!A:B,2,FALSE)),"Not
Found",VLOOKUP(A1,'worksheet2'!A:B,2,FALSE))

Please correct me if I am going in different path...Again, I am not an
Excel PRO but need to make this worked...Any help is appreciated..

 

[Tom Ogilvy]

=IF(ISNA(VLOOKUP("*"&A1&"*",'worksheet2'!A:B,2,FALSE)),"Not
Found",VLOOKUP("*"&A1&"*",'worksheet2'!A:B,2,FALSE))

seems to work.

 

[samkshah]

Thank you very much Tom for prompt reply. I wonder if I am doing it
wrong or was unable to explain my problem correctly. I changed the code
as you mentioned, however it still doesn't work...Let me explain my
problem again:

worksheet1:

ColumnA

I want to search this string and see if it has any word that belongs to
columnA in worksheet2


Worksheet2:

ColumnA ColumnB

search return
word this
manager value
temp in
template other
other worksheet


As we see, word "Search" in column A, worksheet2 is in the given string
in worksheet1...so I need word "return" from column B of worksheet2 in
column B of worksheet1...if columnA, worksheet2 doesn't have any of the
words from that string, then return "not found"

Please let me know if you need more information.

Again I appreciate your prompt response.

Thanks.

 

[Tom Ogilvy]

I doubt there is a simple formula that will work for that. You would first
need to parse out all the individual words in the string and then search the
second sheet.

 

[samkshah]

Tom:

Many thanks. I will do that way and will get back to you if I have any
other problems.

Regards
----