Forum Discussion
Conditional Formatting Rule - Implicit intersection operator
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|#|"))
UDFs or built in functions like INDIRECT tend to cause Excel problems when compatibility with legacy spreadsheet paradigm is required because it can't tell whether such functions will return an array or a scalar value. Excel tends to sprinkle '@'s like confetti, just to make sure. Personally, I would prefer to move away from constructs like ADDRESS and INDIRECT and use defined names to identify data, e.g.
= ISNUMBER(FIND("\"&XLOOKUP(fName&lName, firstname&lastname, type)&"\", "\a\b\c\d\"))
as an array formula or define 'CFormat?' to generate the single cell relative references that are required to make the antiquated conditional formatting work correctly
= ISNUMBER(FIND("\"&XLOOKUP(@fName&@lName, firstname&lastname, type)&"\", "\a\b\c\d\"))