Hi,

I am trying to compare two columns of numbers to see if values in one
column appear in the other.

Given something like the two columns on the left below, I have a thrid
column called "match?" that uses the statement: =OR(EXACT(b2, $a$2:$a
$16)) to compare values in columns.

value 1 value 2 match?
1 5 FALSE
2 3 FALSE
3 1 FALSE
4 2 FALSE
5 4 FALSE

However, the formula is returning FALSE for all comparisons.

Any help on how I can quickly or easily compare values in two columns?
Have a couple of hundred to do, above is just simple illustration.

Thanks in advance,

Lee

On your OR statement, did you enter it as an **array** formula? It looks as
if it should be. Type the formula, then, instead of JUST pressing the ENTER
key, press and hold CTRL+SHIFT+ENTER
--
** John C **

" wrote:

Hi,

I am trying to compare two columns of numbers to see if values in one
column appear in the other.

Given something like the two columns on the left below, I have a thrid
column called "match?" that uses the statement: =OR(EXACT(b2, $a$2:$a
$16)) to compare values in columns.

value 1 value 2 match?
1 5 FALSE
2 3 FALSE
3 1 FALSE
4 2 FALSE
5 4 FALSE

However, the formula is returning FALSE for all comparisons.

Any help on how I can quickly or easily compare values in two columns?
Have a couple of hundred to do, above is just simple illustration.

Thanks in advance,

Lee

It seems you have to array-enter the formula. Double-click the cell
and commit with Shift+Ctrl+Enter.

You can avoid array-entering with the scalar formula

=ISNUMBER(MATCH(b2, $a$2:$a$16, 0))

HTH
Kostis Vezerides

On Oct 30, 6:23*pm, wrote:
Hi,

I am trying to compare two columns of numbers to see if values in one
column appear in the other.

Given something like the two columns on the left below, I have a thrid
column called "match?" that uses the statement: =OR(EXACT(b2, $a$2:$a
$16)) to compare values in columns.

value 1 value 2 match?
1 * * * 5 * * * FALSE
2 * * * 3 * * * FALSE
3 * * * 1 * * * FALSE
4 * * * 2 * * * FALSE
5 * * * 4 * * * FALSE

However, the formula is returning FALSE for all *comparisons.

Any help on how I can quickly or easily compare values in two columns?
Have a couple of hundred to do, above is just simple illustration.

Thanks in advance,

Lee

Let 1,2,3,4 ... be in column A; and 5,6,7... be in B
Let there be 500 entries
In C1 use =IF(COUNTIF($A$1:$A$500,B1),"Repeated","")

You never need EXACT to compare numbers (only text)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

wrote in message
...
Hi,

I am trying to compare two columns of numbers to see if values in one
column appear in the other.

Given something like the two columns on the left below, I have a thrid
column called "match?" that uses the statement: =OR(EXACT(b2, $a$2:$a
$16)) to compare values in columns.

value 1 value 2 match?
1 5 FALSE
2 3 FALSE
3 1 FALSE
4 2 FALSE
5 4 FALSE

However, the formula is returning FALSE for all comparisons.

Any help on how I can quickly or easily compare values in two columns?
Have a couple of hundred to do, above is just simple illustration.

Thanks in advance,

Lee

Hi,

Or just the =COUNTIF(A$1:A$500,B1)

this returns 0 if there are no matches, and a number greater than 0 if there
are matches.


--
Thanks,
Shane Devenshire


" wrote:

Hi,

I am trying to compare two columns of numbers to see if values in one
column appear in the other.

Given something like the two columns on the left below, I have a thrid
column called "match?" that uses the statement: =OR(EXACT(b2, $a$2:$a
$16)) to compare values in columns.

value 1 value 2 match?
1 5 FALSE
2 3 FALSE
3 1 FALSE
4 2 FALSE
5 4 FALSE

However, the formula is returning FALSE for all comparisons.

Any help on how I can quickly or easily compare values in two columns?
Have a couple of hundred to do, above is just simple illustration.

Thanks in advance,

Lee