Excel checking numbers against a large list of numbers

[Guest]

Hi. In Excel, I need to check 450 serial numbers against a list of 4000
numbers. Using the Find function will take too long as I have to type each
number into the Find field. Is there an other way i.e using a formula.

I have tried using a formula, but this does not work. I have used a small
list to test i.e. =IF(B1=$A$1:$A$5,1,0) when B1 column contains numbers to
find (i.e 450 serial nos.) and column A1 contains the large list of numbers
to check against (i.e 4000 serial numbers). A sum of 1's is done against
any matching numbers and this indicates how many time it's matched. I find
the formula does not check the entire range A1 to A5, on cell A1, why would
this be?

 

[Niek Otten]

=IF(ISNA(VLOOKUP(B1,$A$1:$A$6,1,FALSE)),0,1)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Frank Kabel]

Hi
try
=--(COUNTIF($A$a:$A$400,B1)>0)