Simple - does Col B exist in Col A?

P

Phillip Vong

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!
 
K

Ken Puls

=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
 
T

Trevor Shuttleworth

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
 
I

ilia

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.
 
I

ilia

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.
 
Top