I have two long columns of numbers, one colum much longer than the other
obviously with numbers that don't exist in the other shorter column. I would
like to do a formula in a column beside the shorter one, which will lookup
the numbers in the shorter column to see if they exist in the longer column
and then put YES or NO accordingly beside each number in the shorter column.
Hope that is clear. How do I do this? Connie

Assume short column is B and long column is A. In C1:
=if(countif($A:$A,$B1),"yes","no")

then drag fill down to the bottom of the short column.

--
Regards,
Tom Ogilvy


"Connie Martin" wrote:

I have two long columns of numbers, one colum much longer than the other
obviously with numbers that don't exist in the other shorter column. I would
like to do a formula in a column beside the shorter one, which will lookup
the numbers in the shorter column to see if they exist in the longer column
and then put YES or NO accordingly beside each number in the shorter column.
Hope that is clear. How do I do this? Connie

With
The short list in Col_A, beginning in A1
Only the long list (sorted or unsorted) in Col_C ( no non-list items in
Col_C)

This formula flags Col_A items matched in Col_C:
B1: =IF(COUNT(MATCH(A1,C:C,0)),"YES","NO")
Copy that formula down as far as you need

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Connie Martin" wrote in message
...
I have two long columns of numbers, one colum much longer than the other
obviously with numbers that don't exist in the other shorter column. I
would
like to do a formula in a column beside the shorter one, which will lookup
the numbers in the shorter column to see if they exist in the longer
column
and then put YES or NO accordingly beside each number in the shorter
column.
Hope that is clear. How do I do this? Connie

Thank you very much. That works. Another one for my file! Connie

"Tom Ogilvy" wrote:

Assume short column is B and long column is A. In C1:
=if(countif($A:$A,$B1),"yes","no")

then drag fill down to the bottom of the short column.

--
Regards,
Tom Ogilvy


"Connie Martin" wrote:

I have two long columns of numbers, one colum much longer than the other
obviously with numbers that don't exist in the other shorter column. I would
like to do a formula in a column beside the shorter one, which will lookup
the numbers in the shorter column to see if they exist in the longer column
and then put YES or NO accordingly beside each number in the shorter column.
Hope that is clear. How do I do this? Connie

Thank you, Ron. Both yours and Tom's give the same answer although different
formulas. Thanks so much. I love this newsgroup. There's so much help here!

Connie

"Ron Coderre" wrote:

With
The short list in Col_A, beginning in A1
Only the long list (sorted or unsorted) in Col_C ( no non-list items in
Col_C)

This formula flags Col_A items matched in Col_C:
B1: =IF(COUNT(MATCH(A1,C:C,0)),"YES","NO")
Copy that formula down as far as you need

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Connie Martin" wrote in message
...
I have two long columns of numbers, one colum much longer than the other
obviously with numbers that don't exist in the other shorter column. I
would
like to do a formula in a column beside the shorter one, which will lookup
the numbers in the shorter column to see if they exist in the longer
column
and then put YES or NO accordingly beside each number in the shorter
column.
Hope that is clear. How do I do this? Connie

Thanks for the feedback, Connie

(You might notice a performance hit using the COUNTIF approach because it
will search the entire column to count instances. Whereas, the MATCH
approach stops at the first hit.)

***********
Regards,
Ron

XL2003, WinXP


"Connie Martin" wrote:

Thank you, Ron. Both yours and Tom's give the same answer although different
formulas. Thanks so much. I love this newsgroup. There's so much help here!

Connie

"Ron Coderre" wrote:

With
The short list in Col_A, beginning in A1
Only the long list (sorted or unsorted) in Col_C ( no non-list items in
Col_C)

This formula flags Col_A items matched in Col_C:
B1: =IF(COUNT(MATCH(A1,C:C,0)),"YES","NO")
Copy that formula down as far as you need

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Connie Martin" wrote in message
...
I have two long columns of numbers, one colum much longer than the other
obviously with numbers that don't exist in the other shorter column. I
would
like to do a formula in a column beside the shorter one, which will lookup
the numbers in the shorter column to see if they exist in the longer
column
and then put YES or NO accordingly beside each number in the shorter
column.
Hope that is clear. How do I do this? Connie