Simple - does Col B exist in Col A?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Phillip Vong - 26 Oct 2006 19:55 GMT

Simple question.

I have a whole bunch of Names in Col A, like 500.
I have 10 names in Col B.
I want Col C to look at each entry in Col B and see if it exist anywhere in
Col A.

Can someone help me with this?

Thanks!

Reply to this Message

Ken Puls - 26 Oct 2006 20:05 GMT

=IF(ISNA(VLOOKUP(B2,$A$2:$A$500,1,FALSE)),"Does not exist","Exists")

This assumes your data in in A2:A500. Place the formula in C2 and copy
down. If the range is smaller, then you'll need to adjust it.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

> Simple question.
>
[quoted text clipped - 6 lines]
>
> Thanks!

Reply to this Message

Patrick - 26 Oct 2006 20:10 GMT

try the function vlookup()

> Simple question.
>
[quoted text clipped - 6 lines]
>
> Thanks!

Reply to this Message

Trevor Shuttleworth - 26 Oct 2006 21:09 GMT

And another option:

=IF(COUNTIF(A:A,B1)>0,"found","not found")

And, for fun:

=IF(ISNA(MATCH(B1,A:A,0)),"not found", "found")

Regards

Trevor

> Simple question.
>
[quoted text clipped - 6 lines]
>
> Thanks!

Reply to this Message

ilia - 27 Oct 2006 15:12 GMT

You might consider using a dynamic named range in case you list
shinks/grows. The formula for named range would be
=OFFSET($A$2,0,0,COUNTA($A:$A),1). You can come up with different or
mix-match criteria to determine exactly where your name data ends.

> And another option:
>
[quoted text clipped - 18 lines]
> >
> > Thanks!

Reply to this Message

ilia - 27 Oct 2006 15:14 GMT

You might consider using a dynamic named range in case you list
shinks/grows. The formula for named range would be
=OFFSET($A$2,0,0,COUNTA($A:$A),1) assuming your column header is in
$A$1. You can come up with different or mix-match criteria to
determine exactly where your name data ends.

Or, you can use the ISNA(VLOOKUP()) combination for conditional
formatting, for example to highlight non-existent names in red, for
instance.

> And another option:
>
[quoted text clipped - 18 lines]
> >
> > Thanks!

Reply to this Message