Conditional Formatting Rule - Implicit intersection operator

Anglex 

Excel has two calculation methods, implicit intersection and dynamic array. Historically, excel used implicit intersection by default for worksheet formulas, but dynamic array by default for other things like conditional formatting and names. 

More here: https://exceljet.net/glossary/implicit-intersection

With Office 365, MS changed the worksheet formula default calculation method to dynamic array. The implicit intersection operator "@" can be used to force excel to use implicit intersection (more commonly, you probably see it used more in structured table references). 

With your formula, for example, the part where you are concatenating rows 1:1&2:2, this is what implicit intersection returns (screenshot) - it will only return the result corresponding to the same column as the formula (since I entered the formula in column B, it concatentates B1&B2). If I want Excel to return all of the results in versions prior to office 365, I need to force dynamic array by hitting Ctrl+Shift+Enter after keying the formula.

I think you probably want the formula calculated as an array in 365 and prior versions. If you are setting the formula via code, then you may want to further research the Formula, Formula2, and FormulaArray properties of the range object to confirm which one you want (I believe with office 365, you want Formula2 for dynamic array, Formula is implicit intersection - this may be why it's showing up in your worksheet with the "@" symbol). I'm not sure if you would want formula2 or formulaarray for backwards compatibility (if that's an issue, have to research or test it further).

But, if backwards compatibility is an issue, you can't use xlookup. So, you'll probably need to use Iferror(Index(Match())) (which appears to work okay on my machine):

B3=ISNUMBER(FIND("|#|"&IFERROR(INDEX(Sheet1!3:3,MATCH(INDIRECT("'Sheet2'!"&ADDRESS(1,COLUMN()))&INDIRECT("'Sheet2'!"&ADDRESS(2,COLUMN())),Sheet1!1:1&Sheet1!2:2,0)),"Not Found")&"|#|", "|#|a|#|b|#|c|#|d|#|"))