Formula: Cannot use INDEX in formula because excel adds the implicit intersection operators "@". Cannot use FILTER either...

[a-gold]

Describe the bug

I've got to generate a dynamic excel file containing a block with 'raw data' which I want to present at the top conditionally, based on 2 dropdown fields. (Basically it's a year and a keyword selection.)

Although I know two formula solutions neither of them seem to work with the ws.cell(x, y).formula() function.
The first version uses the INDEX method as well as some data-ranges. If I enter this manually to my Excel file it works like a charm.

(Another helper formula exists, which writes the current row number to column A if the two conditions are true. If A85 to A155 contain a number, the data of these lines is referenced at the top of my worksheet.)
worksheet.cell.(1,2).formula('=IFERROR(INDEX($D$85:$T$155,SMALL(IF($A$85:$A$155<>"",ROW($85:$155)-84),ROW(A1)), COLUMN(A1)),"")');

Unfortunately excel includes "@" chars within this formula before INDEX and inside the if-statement.
(The formula does not work anymore with the @ chars, unless you remove them manually...)

=IFERROR(@INDEX($D$85:$T$155,SMALL(IF(@$A$85:$A$155<>"",@ROW($85:$155)-84),ROW(A1)), COLUMN(A1)),"")

I found this article which explains why excel inserts the implicit intersection operator "@". It's seems to be related to the dynamic array stuff.
https://superuser.com/questions/1525270/why-have-at-symbols-suddenly-appeared-in-my-excel-formulae

So I tried a second approach using the FILTER function, my new formula looks like this:

=FILTER(D85:T155, (B85:B155=D4)*(C85:C15=D6), "")
(D4 and D6 contain the current dropdown value. This version is way more reasonable and compact.)

Again this works fine if I enter it manually. But this time (generating the file with excel4node) it's getting deleted by the error-modal when opening the generated file with excel.

Update: Inbetween I know I CAN set the filter formula as follows:
=_xlfn.FILTER(D85:T155, (B85:B155=D4)*(C85:C15=D6), "")

Unfortunately this also results in a formula which includes the implicit intersection operator:
=@FILTER(D85:T155, (B85:B155=D4)*(C85:C15=D6), "")

To Reproduce
Just use an INDEX or FILTER function inside worksheet.cell(x, y).formula(); And open the file with a modern excel version that supports dynamic arrays.

Expected behavior
A working formula in my excel file without the implicit intersection operator "@".

Environment (please complete the following information):

• OS: MacOS 14.4.1
• Node Version: 22.1.0
• excel4node Version: 1.8.2
• Application: Microsoft Excel for Mac (Microsoft 365)
• Application Version: Version 16.85 (24051214)

Additional context
If you know another approach how I can solve this riddle I'd also be fine with that. :-)

Update: I found this article. Another way to set the formula seems to be necessary.
Is there something like the formula2 function in excel4node?

https://stackoverflow.com/questions/76743917/why-does-excel-add-an-in-the-middle-of-my-formula

Thank you!

[a-gold]

Okay, I think now I have a "quick & dirty" solution...

I extended the Cell constructor with:

this.d = false; // 'd' appends dynamic array attribute for formula child element

and updated addToXMLele() in cell.js

    formula2(formula) {
        this.t = null;
        this.v = null;
        this.f = formula;
        this.d = true;
    }

    addToXMLele(ele) {
        if (this.v === null && this.is === null) {
            return;
        }

        let cEle = ele.ele('c').att('r', this.r).att('s', this.s);
        if (this.t !== null) {
            cEle.att('t', this.t);
        }
        if (this.f !== null) {
            if (this.d) {
                cEle.ele('f').att('t', 'array').att('ref', this.r).txt(this.f).up();
            } else {
                cEle.ele('f').txt(this.f).up();
            }
        }
        if (this.v !== null) {
            cEle.ele('v').txt(this.v).up();
        }
        cEle.up();
    }

Additionally I added the formula2Setter() method within the index.js of the cell directory:

function formula2Setter(val) {
    if (typeof (val) !== 'string') {
        throw new TypeError(util.format('Value sent to Formula function of cells %s was not a string, it has type of %s', JSON.stringify(this.excelRefs), typeof (val)));
    }
    if (this.merged !== true) {
        this.cells.forEach((c, i) => {
            c.formula2(val);
        });
    } else {
        var c = this.cells[0];
        c.formula2(val);
    }
    return this;
}

cellBlock.prototype.formula2 = formula2Setter;

Although that's enough for my personal use case I think it's not a clean or complete solution yet.
(Especially regarding the cell-reference-logic. Probably this won't work correctly for a ref-range.)