I was recently helping someone to develop a formula-based Conditional Formatting rule, a stipulation for which (at least in 2010) is that the explicit use of array constants is forbidden.

Of course, we can get around this by first storing these array constants as Defined Names and then referencing those in the formula instead, which is perfectly legitimate.

On that particular day, however, I was in a rather creative mood (no doubt to the OP’s detriment!), and so went about the (somewhat convoluted) task of creating a version of my formula in which any array constants were replaced with suitable functional constructions, only to find that this process was not always quite as straightforward as I had first presumed…