XLOOKUP not working in formula #3042

New Issue

shudder · 2023-12-11T21:17:37Z

shudder commented

2023-12-11 21:17:37 +00:00

The XLOOKUP formula keyword is being escaped by Excel with a @.
Is XLOOKUP supported by sheetjs at all?

The XLOOKUP formula keyword is being escaped by Excel with a @. Is XLOOKUP supported by sheetjs at all?

sheetjs commented

2023-12-11 21:45:46 +00:00

XLOOKUP must be prefixed (use _xlfn.XLOOKUP). An old snapshot of the list of prefixed functions is in the docs https://docs.sheetjs.com/docs/csf/features/formulae#prefixed-future-functions

Based on Example 4 from XLOOKUP documentation, here is an example showing both XLOOKUP and the prefixed _xlfn.XLOOKUP: https://jsfiddle.net/Lyxs3bc5/

    /* Starting data */
    var D = [
      [ "Tax Rate", "Max Income" ],
      [ 0.1, 9700 ],
      [ 0.22, 39475],
      [ 0.24, 84200 ],
      [ 0.32, 160726 ],
      [ 0.35, 204100 ],
      [ 0.37, 510300 ],
      [],
      [ 0, 46523, "XLOOKUP" ],
      [ 0, 46523, "_xlfn.XLOOKUP" ]
    ];

    /* Create worksheet */
    var ws = XLSX.utils.aoa_to_sheet(D);

    /* A9 unprefixed formula */
    ws["A9"].f = "XLOOKUP(B9,B2:B7,A2:A7,0,1,1)"
    
    /* A10 prefixed formula */
    ws["A10"].f = "_xlfn.XLOOKUP(B9,B2:B7,A2:A7,0,1,1)"

    /* Export to file (start a download) */
    var wb = XLSX.utils.book_new(ws, "Sheet1");
    XLSX.writeFile(wb, "SheetJSDynamicFormulae.xlsx");

`XLOOKUP` must be prefixed (use `_xlfn.XLOOKUP`). An old snapshot of the list of prefixed functions is in the docs https://docs.sheetjs.com/docs/csf/features/formulae#prefixed-future-functions Based on [Example 4 from `XLOOKUP` documentation](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929), here is an example showing both `XLOOKUP` and the prefixed `_xlfn.XLOOKUP`: https://jsfiddle.net/Lyxs3bc5/ ```js /* Starting data */ var D = [ [ "Tax Rate", "Max Income" ], [ 0.1, 9700 ], [ 0.22, 39475], [ 0.24, 84200 ], [ 0.32, 160726 ], [ 0.35, 204100 ], [ 0.37, 510300 ], [], [ 0, 46523, "XLOOKUP" ], [ 0, 46523, "_xlfn.XLOOKUP" ] ]; /* Create worksheet */ var ws = XLSX.utils.aoa_to_sheet(D); /* A9 unprefixed formula */ ws["A9"].f = "XLOOKUP(B9,B2:B7,A2:A7,0,1,1)" /* A10 prefixed formula */ ws["A10"].f = "_xlfn.XLOOKUP(B9,B2:B7,A2:A7,0,1,1)" /* Export to file (start a download) */ var wb = XLSX.utils.book_new(ws, "Sheet1"); XLSX.writeFile(wb, "SheetJSDynamicFormulae.xlsx"); ```

❤️ 1

sheetjs closed this issue

2023-12-11 21:45:46 +00:00

Sign in to join this conversation.