I have this what I think is a standard formula, which I don't understand why 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")

The top of the picture is from Excel version 2019, and the below of the picture is Excel version 365. In the first picture 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, but my lack of understanding why the difference is there in the first place.

Thank you in advance.

CodePudding user response：

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:

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.