OR EXACT Formula Appears to not accept Arrays
 

I am trying to compare 2 columns of data to find matches.
I want to take cell A1 and look in range B1 to B1000 to see if the value in
A1 exists any of the cells in that range.
Excel Help says I should use the following formula:
=OR(EXACT(A1,B1:B1000))
This formula returns the value "FALSE" even if I know the value in A1 has a
match in the B range.
When I do not use the range and compare the two matching cells, it returns
"TRUE".
Ex.
=OR(EXACT(A1,B230))
According to Excel Help, this should check each cell and look for a match.
Why does it appear to have a problem with arrays?
Thank you.

OR EXACT Formula Appears to not accept Arrays
 

You might be able to use something like this:

Test if the value in A1 is found in $B$1:$B$1000 (not case sensitive)
=COUNTIF($B$1:$B$1000,A1)0

Does that help?

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

XL2002, WinXP-Pro


"JennyJeneralGraves" wrote:

I am trying to compare 2 columns of data to find matches.
I want to take cell A1 and look in range B1 to B1000 to see if the value in
A1 exists any of the cells in that range.
Excel Help says I should use the following formula:
=OR(EXACT(A1,B1:B1000))
This formula returns the value "FALSE" even if I know the value in A1 has a
match in the B range.
When I do not use the range and compare the two matching cells, it returns
"TRUE".
Ex.
=OR(EXACT(A1,B230))
According to Excel Help, this should check each cell and look for a match.
Why does it appear to have a problem with arrays?
Thank you.

OR EXACT Formula Appears to not accept Arrays
 

Ron, Thanks a Million! I am so appreciative of your quick response. Yes, it
worked, also, I am wondering if I can assign a value if the formula turns out
to be "true" or "false"?

"Ron Coderre" wrote:

You might be able to use something like this:

Test if the value in A1 is found in $B$1:$B$1000 (not case sensitive)
=COUNTIF($B$1:$B$1000,A1)0

Does that help?

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

XL2002, WinXP-Pro


"JennyJeneralGraves" wrote:

I am trying to compare 2 columns of data to find matches.
I want to take cell A1 and look in range B1 to B1000 to see if the value in
A1 exists any of the cells in that range.
Excel Help says I should use the following formula:
=OR(EXACT(A1,B1:B1000))
This formula returns the value "FALSE" even if I know the value in A1 has a
match in the B range.
When I do not use the range and compare the two matching cells, it returns
"TRUE".
Ex.
=OR(EXACT(A1,B230))
According to Excel Help, this should check each cell and look for a match.
Why does it appear to have a problem with arrays?
Thank you.

OR EXACT Formula Appears to not accept Arrays
 

Jenny,
..
Your formula works for me, I entered it with shft-ctrl-enter when complete.
Excel put thebrackets around the array formula for me.
{=OR(EXACT(A1,B1:B1000)) } Tested it and everything.

Beege


"JennyJeneralGraves" wrote in
message ...
I am trying to compare 2 columns of data to find matches.
I want to take cell A1 and look in range B1 to B1000 to see if the value
in
A1 exists any of the cells in that range.
Excel Help says I should use the following formula:
=OR(EXACT(A1,B1:B1000))
This formula returns the value "FALSE" even if I know the value in A1 has
a
match in the B range.
When I do not use the range and compare the two matching cells, it returns
"TRUE".
Ex.
=OR(EXACT(A1,B230))
According to Excel Help, this should check each cell and look for a match.
Why does it appear to have a problem with arrays?
Thank you.

OR EXACT Formula Appears to not accept Arrays
 

Thanks, Beege! I discovered the same thing since I first posted.

"Beege" wrote:

Jenny,
..
Your formula works for me, I entered it with shft-ctrl-enter when complete.
Excel put thebrackets around the array formula for me.
{=OR(EXACT(A1,B1:B1000)) } Tested it and everything.

Beege


"JennyJeneralGraves" wrote in
message ...
I am trying to compare 2 columns of data to find matches.
I want to take cell A1 and look in range B1 to B1000 to see if the value
in
A1 exists any of the cells in that range.
Excel Help says I should use the following formula:
=OR(EXACT(A1,B1:B1000))
This formula returns the value "FALSE" even if I know the value in A1 has
a
match in the B range.
When I do not use the range and compare the two matching cells, it returns
"TRUE".
Ex.
=OR(EXACT(A1,B230))
According to Excel Help, this should check each cell and look for a match.
Why does it appear to have a problem with arrays?
Thank you.

OR EXACT Formula Appears to not accept Arrays
 

Building on the formula I posted, try this:

=IF(COUNTIF($B$1:$B$1000,A1)0,"Match","No Match")

Replace "Match" and "No Match" with whatever you like.

If you want the conditional values to be numeric and not text, omit the
quotation marks:
=IF(COUNTIF($B$1:$B$1000,A1)0,10,45)

or if you just want TRUE=1 and FALSE=0, then:
=--(COUNTIF($B$1:$B$1000,A1)0)

Does that help?

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

XL2002, WinXP-Pro


"JennyJeneralGraves" wrote:

Ron, Thanks a Million! I am so appreciative of your quick response. Yes, it
worked, also, I am wondering if I can assign a value if the formula turns out
to be "true" or "false"?

"Ron Coderre" wrote:

You might be able to use something like this:

Test if the value in A1 is found in $B$1:$B$1000 (not case sensitive)
=COUNTIF($B$1:$B$1000,A1)0

Does that help?

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

XL2002, WinXP-Pro


"JennyJeneralGraves" wrote:

I am trying to compare 2 columns of data to find matches.
I want to take cell A1 and look in range B1 to B1000 to see if the value in
A1 exists any of the cells in that range.
Excel Help says I should use the following formula:
=OR(EXACT(A1,B1:B1000))
This formula returns the value "FALSE" even if I know the value in A1 has a
match in the B range.
When I do not use the range and compare the two matching cells, it returns
"TRUE".
Ex.
=OR(EXACT(A1,B230))
According to Excel Help, this should check each cell and look for a match.
Why does it appear to have a problem with arrays?
Thank you.

OR EXACT Formula Appears to not accept Arrays
 

This works beautifully!
It would be impossible to express how grateful I am for your help on this.
My bosses now believe I am a genius. Many thanks, Ron.
Jen

"Ron Coderre" wrote:

Building on the formula I posted, try this:

=IF(COUNTIF($B$1:$B$1000,A1)0,"Match","No Match")

Replace "Match" and "No Match" with whatever you like.

If you want the conditional values to be numeric and not text, omit the
quotation marks:
=IF(COUNTIF($B$1:$B$1000,A1)0,10,45)

or if you just want TRUE=1 and FALSE=0, then:
=--(COUNTIF($B$1:$B$1000,A1)0)

Does that help?

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

XL2002, WinXP-Pro


"JennyJeneralGraves" wrote:

Ron, Thanks a Million! I am so appreciative of your quick response. Yes, it
worked, also, I am wondering if I can assign a value if the formula turns out
to be "true" or "false"?

"Ron Coderre" wrote:

You might be able to use something like this:

Test if the value in A1 is found in $B$1:$B$1000 (not case sensitive)
=COUNTIF($B$1:$B$1000,A1)0

Does that help?

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

XL2002, WinXP-Pro


"JennyJeneralGraves" wrote:

I am trying to compare 2 columns of data to find matches.
I want to take cell A1 and look in range B1 to B1000 to see if the value in
A1 exists any of the cells in that range.
Excel Help says I should use the following formula:
=OR(EXACT(A1,B1:B1000))
This formula returns the value "FALSE" even if I know the value in A1 has a
match in the B range.
When I do not use the range and compare the two matching cells, it returns
"TRUE".
Ex.
=OR(EXACT(A1,B230))
According to Excel Help, this should check each cell and look for a match.
Why does it appear to have a problem with arrays?
Thank you.

OR EXACT Formula Appears to not accept Arrays
 

You're very welcome, Jen.
***********
Regards,
Ron

XL2002, WinXP-Pro


"JennyJeneralGraves" wrote:

This works beautifully!
It would be impossible to express how grateful I am for your help on this.
My bosses now believe I am a genius. Many thanks, Ron.
Jen

"Ron Coderre" wrote:

Building on the formula I posted, try this:

=IF(COUNTIF($B$1:$B$1000,A1)0,"Match","No Match")

Replace "Match" and "No Match" with whatever you like.

If you want the conditional values to be numeric and not text, omit the
quotation marks:
=IF(COUNTIF($B$1:$B$1000,A1)0,10,45)

or if you just want TRUE=1 and FALSE=0, then:
=--(COUNTIF($B$1:$B$1000,A1)0)

Does that help?

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

XL2002, WinXP-Pro


"JennyJeneralGraves" wrote:

Ron, Thanks a Million! I am so appreciative of your quick response. Yes, it
worked, also, I am wondering if I can assign a value if the formula turns out
to be "true" or "false"?

"Ron Coderre" wrote:

You might be able to use something like this:

Test if the value in A1 is found in $B$1:$B$1000 (not case sensitive)
=COUNTIF($B$1:$B$1000,A1)0

Does that help?

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

XL2002, WinXP-Pro


"JennyJeneralGraves" wrote:

I am trying to compare 2 columns of data to find matches.
I want to take cell A1 and look in range B1 to B1000 to see if the value in
A1 exists any of the cells in that range.
Excel Help says I should use the following formula:
=OR(EXACT(A1,B1:B1000))
This formula returns the value "FALSE" even if I know the value in A1 has a
match in the B range.
When I do not use the range and compare the two matching cells, it returns
"TRUE".
Ex.
=OR(EXACT(A1,B230))
According to Excel Help, this should check each cell and look for a match.
Why does it appear to have a problem with arrays?
Thank you.