OR Exact statement not working to compare values in two columns
 

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

OR Exact statement not working to compare values in two columns
 

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

OR Exact statement not working to compare values in two columns
 

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

OR Exact statement not working to compare values in two columns
 

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

OR Exact statement not working to compare values in two columns
 

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