2

When I put a set of formulas in excel 2013 like this:

A1 = abc
A2 = ac
A3 = bc

B1 = OR(TRUE) = TRUE
B2 = OR(FALSE) = FALSE

B3 = ISNUMBER(SEARCH({"a", "b"}, A1)) = TRUE
B4 = ISNUMBER(SEARCH({"a", "b"}, A2)) = TRUE
B5 = ISNUMBER(SEARCH({"a", "b"}, A3)) = FALSE

Yet if I add an OR function in the formulas :

C1 = OR(ISNUMBER(SEARCH({"a", "b"}, A1))) = TRUE
C2 = OR(ISNUMBER(SEARCH({"a", "b"}, A2))) = TRUE
C3 = OR(ISNUMBER(SEARCH({"a", "b"}, A3))) = TRUE

Logically the input reveived by the OR function in cell C3 is supposed to be FALSE (see B5 and compare it with B2) yet it resulted in TRUE when it supposed to result in FALSE. I want to know why is this happening? Is there an explanation for this? And does this happen in all version of excel after 2007?

Another interesting fact is that if I take the result in B5 as the direct input of an OR function like so:

C4 = OR(B5) = FALSE

It resulted in FALSE yet it is basically the same formula as C3. Why does this inconsistency happens? Is it a bug?

Updated question

According to the answers below, the OR function is an "array-friendly" function. What does that exactly mean? And how would a beginner like me know which function in excel are "array friendly" and which are not? Is there a list as such?

Also does this mean that the SEARCH and ISNUMBER function aren't "array friendly"? Yet it accepts "array" as an argument. I'm confused.

Tomsofty33
  • 121
  • 2
  • sorry I did not write it as obvious as it sounds in my mind, but if `ISNUMBER(SEARCH())` returns an array, then both functions are array-friendly... otherwise they couldn't return an array – Aprillion Aug 03 '19 at 12:53
  • "if ISNUMBER(SEARCH()) returns an array" let me clarify, u mean bcoz the Intermediate result of this (I mean cell C3 but before the OR operation = { FALSE, TRUE}) is an array & it is directly put into the OR function in its full form (as an array) & also bcoz OR accept arrays, then that's why it was able to take into account all the elements in the array & able to produce that result (TRUE). As contrast to, in this case, cell C4 which takes a non array (B5 = FALSE i.e not an array bcoz the results was first put as a single cell output) as the input value which causes the result to be FALSE. – Tomsofty33 Aug 03 '19 at 13:29
  • Am I... mistaken? – Tomsofty33 Aug 03 '19 at 13:39
  • not mistaken... – Aprillion Aug 03 '19 at 14:50
  • I feel like some of my explanation is kinda....disconnected or perhaps it is not capturing the full picture I would say. There are some left unexplained. I'm just curious, what is your opinion about this quote : "Like other array-style formulas, the part that delivers the array has to be enclosed in some sort of aggregate function to make it scan through the array - otherwise it only looks at the first element of the array. So anything like COUNT, SUM, SUMPRODUCT will do the trick." Taken from https://stackoverflow.com/questions/51472960/excel-2016-searching-for-multiple-terms-in-a-cell – Tomsofty33 Aug 03 '19 at 15:03
  • Which is the part that "delivers the array" and which one is the "aggregate function" in C3? Since I am not yet eligible to comment over there. – Tomsofty33 Aug 03 '19 at 15:07
  • `OR` in C3 would be the "aggregate function" in this sense – Aprillion Aug 03 '19 at 15:37
  • And the part that delivers the array, in this sense, would be? – Tomsofty33 Aug 03 '19 at 15:48

1 Answers1

1

OR can take multiple arguments, or evaluate all items from an array...

ISNUMBER(SEARCH({"a","b"}, A3)) returns a single value in context of displaying formula result in a single cell, but it returns an array in array-friendly context such as inside OR (I don't know the exact name of this feature though.)

OR({FALSE, TRUE}) is TRUE, similar to OR(ISNUMBER(SEARCH("a", A3)), ISNUMBER(SEARCH("b", A3))).

Update after changing the question:

All of the intermediate functions must be array-friendly for this to work (I believe most functions are, but I am not aware of any list - you have to do some research yourself, this is not paid support).

The single cell output is NOT array friendly. When a result is an array, e.g. {TRUE, FALSE} and it's displayed in a single cell, only the first value will be visible in the cell.

More on array formulas where 1 formula can be inserted into multiple cells: https://support.office.com/en-us/article/create-an-array-formula-e43e12e0-afc6-4a12-bc7f-48361075954d

Aprillion
  • 21,510
  • 5
  • 55
  • 89
  • How did you know the OR function is labeled as array-friendly, Is there a list of which functions are array-friendly and which are not? – Tomsofty33 Aug 03 '19 at 12:33
  • I clicked on the ***fₓ*** icon left from formula bar. It displayed the result of the `ISNUMBER` as an array when inside OR. It should happen in all versions of Excel (but I tried only in 2010, can't guarantee it wasn't changed in previous/future versions) – Aprillion Aug 03 '19 at 12:42
  • "All of the intermediate functions must be array-friendly for this to work" does the Intermediate functions you are referring in this context includes ISNUMBER and SEARCH? – Tomsofty33 Aug 03 '19 at 12:56
  • 1
    "The single cell output is NOT array friendly." Just to confirm, is this one addressing the part of why the C4 returns FALSE ? – Tomsofty33 Aug 03 '19 at 13:08