Conditional Formatting via Lambda

[boukasa]

I have a Lambda that produces an array of true/false values. I want to conditionally format a data range where true values produced by the Lambda are in bold. Like this:

Data Range:

  A B C

  D E F

  G H I

Lambda result:

  T F F

  F T F

  T F F

Desired outcome:

  A, E, and G are bold

If I materialize the results of the Lambda in a range, I can use the materialized range as the condition, using the upper left corner of the materialized condition range as a relative reference as applied to the data range.

But if I use the Lambda itself in the condition (=GetFormatArray()) the conditional formatting does not work - each data range cell is evaluated against the top left result of the formula, so they all get the same condition result and are formatted the same way as dictated by the upper left cell of the array produced by the lambda.

How can I spread the lambda result across the data range as conditional formatting? Any technique to do this other than hidden sheets that materialize the lambda?

(I wish Excel would remove/hide the distinction between a range and an array so they were seamless and interchangeable! You almost don't need the sheet anymore at that point, and you could output the language result any way you want including via API. That would be amazing!)

[#2]

@boukasa 

This has nothing to do with LAMBDAs vs non-LAMBDAs; rather, arrays vs ranges. Conditional Formatting requires that a given rule be defined relative to the range reference(s) of the cell(s) to which it is applied. Since arrays do not by definition meet this criterion, they cannot be used as the basis for Conditional Formatting.

The only way to circumvent this would be to INDEX the array, though this would necessitate an explicit reference to the range to which the Conditional Formatting is being applied. For example, with MyArray defined as:

={1,0,0;0,1,0;0,0,1}

you could then apply the following Conditional Formatting rule to the range D1:F3:

=INDEX(
    MyArray,
    ROW() - ROW(INDEX($D$1:$F$3, 1, 1)) + 1,
    COLUMN() - COLUMN(INDEX($D$1:$F$3, 1, 1)) + 1
)

I understand, however, that this need to include an explicit cell reference is not what you were hoping for.

Regards

[#3]

@JosWoolleyThank you for your thoughts, your answer held the key. I don't need to realize the array into a range on a sheet. I can do something like this:

Fmt = LAMBDA({"blue"; "bold|blue"; "bold"});
CondFmt = LAMBDA(corner,row,col,fmttest,
  LET(
      rrow,row-ROW(corner)+1,
      rcol,col-COLUMN(corner)+1,
      formats,INDEX(Fmt(),rrow,rcol),
      formatarr,TEXTSPLIT(formats,"|"),
      r,NOT(ISNA(MATCH(fmttest,formatarr,0))),
      r
  )
);

My conditional formulas are like this:

=CondFmt($B$29,ROW(B29),COLUMN(B29),"blue") => [make blue]

=CondFmt($B$29,ROW(B29),COLUMN(B29),"blue") => make bold

I can now create a report and control its formatting with lambdas only. The only missing piece is the ability to scale the conditional formatting to the extent of the report spill. Maybe one day!