OR EXACT Formula Appears to not accept Arrays

  • Thread starter JennyJeneralGraves
  • Start date
J

JennyJeneralGraves

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.
 
R

Ron Coderre

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
 
J

JennyJeneralGraves

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"?
 
B

Beege

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
 
R

Ron Coderre

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
 
J

JennyJeneralGraves

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top