excel 2002

[markholt]

I'm trying to use the =OR(Exact) & compare a range of cells. Having
problems. It seems to work when the cells are in the same row, but not when
comparing to cells in different rows in the range.

 

[Sheeloo]

Pl. share the formula you are using...
Exact returns TRUE or FALSE... Why are you wrapping it in OR?

 

[Chip Pearson]

The following array formula will return the number of EXACT matches in
the range A1:A6 compared, row by row, with B1:B6.

=SUM(1*(EXACT(A1:A6,B1:B6)))

The following array formula will return TRUE if all cells in A1:A6 are
exact matches, row by row, with the values in B1:B6. It will return
FALSE if one or more pair of cells are not an exact match.

=SUM(1*EXACT(A1:A6,B1:B6))=ROWS(A1:A6)

EXACT performs a case-sensitive match. E.g., "A" is not equal to "a".

These are both array formulas, so you must press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula and whenever
you edit it later. If you do this properly, Excel will display the
formula enclosed in curly braces { }. See
http://www.cpearson.com/Excel/arrayformulas.aspx for must more
information about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ShaneDevenshire]

Hi,

We need more information - are you trying to compare every cell in one range
with every one in another range and determine if or how many are exact
matches. Note this is different than comparing cell by cel. For example
A1 B1
A2 B2
A3 B3

Do you want to check if A1 is in any of the cells B1:B3 or just compare it
with B1?

If you wanted to check A1 against all the cells in B1:B3 it would be

=OR(EXACT(A1,B1:B3))

entered as an array (Shift+Ctrl+Enter)

If you wanted to compare A1 to B1, A2 to B2 and determine how many were
exact matches the formula would be

=SUMPRODUCT(--EXACT(A1:A3,B1:B3))

If you are trying to find out how many items in column A are also in column
B regardless of position then

=SUMPRODUCT(COUNTIF(A1:A3,B1:B3))

But this is not a case sensitive count.