comparing two columns for partial values

[Trumpet]

Hello every, I have a question and would really appreciate if someone could
reply me ASAP.
I have two columns in excel
Coulmn A contains 500 cells for example
Jane Doe, SOme Last name, Hellp there,
Coulmn B contain 70 cells only with example
Doe J, Lastname S, There H
I only have 70 matches, but i don't know how to match those 70 cells to the
500 cells. I am looking for a partial match to check in cell A2 (for exmple)
if there is DOE in cell B2 and return the value Yes in cell C2

May you please help

 

[Alan]

Try the FIND or SEARCH functions, FIND is case sensitive, SEARCH is not. eg
=IF(ISERROR(FIND("Doe",A1)),"No","Yes") will return 'Yes' for 'Doe', but not
'doe'
=IF(ISERROR(SEARCH("Doe",A1)),"No","Yes") will return 'Yes' for 'Doe' or
'doe'
Both will return 'No' if there is no match.
Regards,
Alan.

 

[Trumpet]

I don't want to type DOE in every cell, otherwise I would just search for
everyone and highlight them manually. I though there should be a search
function which automatically search all the terms in one cell and compare it
to the array in another coulmn.

 

[Alan]

I'm not that sure what you mean. You used 'Doe' as an example so that's what
I used. The formulas will return 'Yes' if any cell contains 'Doe'. Change
'Doe' to whatever you want to search for, change the cell references to suit
your lists. What do you mean by 'automatically search all the terms in one
cell'? Please be more specific.
Why you think you need to type 'Doe' into every cell I really don't know.
Regards,
Alan.

 

[Trumpet]

What I want is that I create one formula and paste it in all the 500 cells.
the formula will search in every cell of column A to find a partial match in
column B

 

[Alan]

The formulas I sent you will do that. Put one into the appropriate cell,
change the criteria to what you want to search for, adjust the cell
references to suit and drag the formula down to the end of your list.

 

[Trumpet]

no, I have to put in every cell what ever name is there that I want to search
for. May be I can not express well my request, but your answer is definitely
not what I am looking for. Thanks anyway

 

[Fred Smith]

Life just isn't fair, is it Trumpet. To get quick, accurate solutions, all
you have to do is ask a clear question.

Alan answered the question you asked. If it doesn't do what you want, you
need to say why, and what you are looking for instead.

Regards,
Fred.