| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| EXACT comparison formula | Excel Worksheet Functions | |||
| Formula to count text and alert me if a text appears more than twi | Excel Discussion (Misc queries) | |||
| Using Vlookup in formula arrays | Excel Worksheet Functions | |||
| Formula Arrays VERY SLOW in Excel 2002 | Excel Worksheet Functions | |||
Hybrid Mode
