Formula to compare two columns

[Lise]

Hi

I have two columns one has names automated from another worksheet and one
has other names entered manually (the two columns show two attendences)
example
Column A = town Column B = Country
jack black Sue Smith
snow white Jack Black

using A1 as an example I want a formula to check if the name in A1 is in
B1:b2000 and if it is type yes and if not no

Any help as always appreciated

 

[Per Jessen]

Hi Lise

Try this formula in C1 and copy it down as required:

=IF(ISERROR(MATCH(A1,$B$1:$B$1000;0)),"No","Yes")

Regards,
Per

 

[Lise]

Thanks Per unfortunately this brings back yes to all rather than yes or no??
any other thoughts
--
Thanks

Lise

Hi Lise

Try this formula in C1 and copy it down as required:

=IF(ISERROR(MATCH(A1,$B$1:$B$1000;0)),"No","Yes")

Regards,
Per

 

[Dave Peterson]

Make sure that calculation is set to automatic
(Tools|options|calculation tab in xl2003 menus)

If that doesn't help, share the formula you used--or try Per's suggestion one
more time.

(and remember to use either commas or semicolons as your operation
separator--Per had a typo in his.)

Thanks Per unfortunately this brings back yes to all rather than yes or no??
any other thoughts

 

[John]

Hi Dave
I like your formula , taken from your other post
=isnumber(match(A1,B1:B1000,0))
it works well. Any reason not to use it.
Regards
John

 

[Dave Peterson]

Nope.

If you're ok seeing true/false and not Yes/no, then you're fine.

But Per's (corrected) formula should work, too.