comparing two columns for partial values
 

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

comparing two columns for partial values
 

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" wrote in message
...
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

comparing two columns for partial values
 

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" wrote:

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" wrote in message
...
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

comparing two columns for partial values
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

comparing two columns for partial values
 

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

comparing two columns for partial values
 

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" wrote in message
...
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

comparing two columns for partial values
 

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

comparing two columns for partial values
 

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.

"Trumpet" wrote in message
...
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