In LibreOffice Calc, you cannot assign ranges to LET variables if you intend to use them with INDEX or MATCH. You need to replace all range variables with OFFSET directly. VARIABLE,CELL:INDEX(CELL:CELL,CELL) result it #VALUE! VARIABLE, OFFSET(CELL, 0, 0, CELL, 1) result it #REF! SOlution: for LET() to work in libreoffice using dynamic ranges one must use the syntax " VARIABLE, INDEX(INDIRECT(HELPER_CELL)", where the helper cell content must be in text format and may be derived from a CONCAT() function. so please document this to users OR solve this because i lost a great deal of time to do it.
LibreOffice LET() limitation with dynamic ranges LibreOffice Calc does not fully support storing OFFSET arrays inside LET variables LET variables cannot hold raw multi-cell ranges created by OFFSET() or direct references. Using OFFSET inside LET as an array variable in Calc does not work as expected when combined with INDEX/MATCH Do not try to store ranges as text and convert with INDIRECT in LET — Calc won’t allow it SOLUTION To use dynamic ranges in LET, you must wrap the range in an INDEX(INDIRECT(...)). The helper cell must contain a text representation of the range
*** This bug has been marked as a duplicate of bug 127808 ***