Standard Excel formula behaves differently depending on which version of Excel I use

Question

I have what I think is a standard formula, which is behaving differently depending on which Excel version I use, Excel 365 or Excel 2019

=IF(F5=$M$1;IFERROR(IF(AND(IFERROR(FIND("CONV";B5;1);"NY");F5=$M$1);"KONV");IF(F5=$M$1;"NY";"ORG"));"ORG")

In the two screenshots below, Excel version 2019 is on top, and Excel version 365 is beneath it. In the first screenshot everything is the same, but in the 2nd picture, "NY" becomes an error in Excel 2019 (which is correct and what I want), but in Excel 365 I get a wrong TRUE.

My issue isn't to fix the formula so it works. Rather, I want to know why the difference between Excel 365 and Excel 2019 is there in the first place.

Answer 1

Interesting post. With the problem at hand being illogical, the explaination (or rather my hypothesis due to the lack of documentation) is actually the opposite. In short: Dynamic arrays are the culprit to the difference between ms365's and previous versions of handling each condition in the AND() function.

First, even though the ms-documentation tells us that all conditions need to evaluate to TRUE, the reality of it is that there are two other important rules for us to keep in mind:

• Text values or empty cells supplied as arguments are ignored.
• The AND function will return #VALUE if no logical values are found or created during evaluation.

---

Let's assume that with the below examples, the value NY is written in cell A1. In Excel 2019 and earlier versions, you should recieve the following results:

Formula	Outcome
=AND(TRUE)	TRUE
=AND(TRUE,A1)	TRUE
=AND(TRUE,"NY")	#VALUE
=AND(TRUE,{"NY"})	TRUE
=AND({"NY"})	#VALUE

---

Whether it's intentional or not, in versions prior to ms365 any text value will result in an error unless it's written in an array format or pulled in through a cell-reference.

I can only expect the difference with ms365 to be explained that the latter will automatically evaluate the single text-value as an array due to the mechanics of dynamic array functionality.