Unusual behavior in function "=OR(EXACT(A3,B3:B8))"

[FDAUSER]

I am trying to comapre a cell value to a list using the following function- "=OR(EXACT(A3,B3:B8))"

CellList1022210200FALSE 10222 10225 13524 54236 12265
I have the following info in excel. In C2 I have the following function -
=OR(EXACT(A3,B3:B8))

I am checking to see if the Cell "10222" is contained in the List, which it is. But the function is returning "False"

If I put 10222 into the first cell, it returns "True"

CellList 1022210222TRUE 10222 10225 13524 54236 12265

The interesting thing, when I check the function dialog box for the first example.

CellList1022210200FALSE 10222 10225 13524 54236 12265

The dialog box shows that the function is "True"

Formula Result = True

The result string is actually (False, True, False, False, False, False...) If any one of the conditions is "true" the result should be "true". The formula shows the result is true, but "False" is what is displayed back in the formula cell.

In addition, if I use the "Evaluate Formula" tool in Excel 2007 and walk through the formula. Only the first item in the list is getting compared to the cell, and this tool shows the result is "False"

Is this a bug, or is this happening for a reason?

Thanks for any input,

 

[barry houdini]

That formula is an array formula so to make it work correctly you need to confirm with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.

If your values are numbers then you should be able to use one of these non-array options

=COUNTIF(B3:B8,A3)>0

or

=ISNUMBER(MATCH(A3,B3:B8,0))

PS This question should probably be in "Excel Questions" forum - I'll see if it can be moved......

 

[010199]

I don't know if this relates, but I have struggled a little myself, while learning from John Walkenbach's book: In his example, Value is a value "DOG" and Range1 is an array with DOG as the third term. In taking apart his formula to learn I observe that the array formula =MATCH(TRUE,EXACT(Value,Range1)) returns the correct value 3.
However =EXACT(Value,Range1) entered as an array formula (CTRL-SHIFT-ENTER) returns FALSE. In this case Evaluate formula shows that Range1 is providing only the first value of the array. This is similar to the first value returned in the original question.

under what circumstances can "EXACT" be an array function?

 

[barry houdini]

Hello 010199, welcome to MrExcel

There's no inconsistency

The formula

=EXACT(Value,Range1)

in your examples returns an array the same size as Range1, so in your example that would be {FALSE,FALSE,TRUE}. When you use that in a situation that handles arrays, e.g. as the 2nd argument of MATCH, then you see that TRUE is the third element because MATCH returns 3.......but used on it's own in a single cell all that you see is FALSE....but that's just a display issue

[to see the whole array select the cell with the formula then press F2 key followed by F9]

 

[Aladin Akyurek]

Hello 010199, welcome to MrExcel

There's no inconsistency

The formula

=EXACT(Value,Range1)

in your examples returns an array the same size as Range1, so in your example that would be {FALSE,FALSE,TRUE}. When you use that in a situation that handles arrays, e.g. as the 2nd argument of MATCH, then you see that TRUE is the third element because MATCH returns 3.......but used on it's own in a single cell all that you see is FALSE....but that's just a display issue

[to see the whole array select the cell with the formula then press F2 key followed by F9]

It's also an access issue in that the result array/vector won't be accessible to other formulas from the cell it's pushed into...