OR Exact statement not working to compare values in two columns

[lee.kagan]

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

 

[John C]

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

 

[vezerid]

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

 

[Bernard Liengme]

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

 

[ShaneDevenshire]

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.