Description: using the formula =--(B1=(B1:B2)) If this is in A1, returns TRUE If this is in A3, returns #VALUE! More generally, if the cell the formula is in is in a row outside the rows in the formula, it returns #value! The cell the formula is in does not take part in the evaluated value. This occured in 5.x. Downloaded latest 20230927 version 7.x. Steps to Reproduce: using the formula =--(B1=(B1:B2)) If this is in A1, returns TRUE If this is in A3, returns #VALUE! Actual Results: using the formula =--(B1=(B1:B2)) If this is in A1, returns TRUE If this is in A3, returns #VALUE! Expected Results: For the example, always TRUE. For a more indepth example, the results should be TRUE or FALSE depending on the data in the cells. --(RANGE=CELL) * is a more capable Vlookup method used in intermediary skill spreadsheets. Reproducible: Always User Profile Reset: No Additional Info: As per expected results.
You are using an array B1:B2 without an array formula. So when the formula is in A1 it intersects with B1, but when it is in A3 there is not B3 to intersect with. Please put your question in https://ask.libreoffice.org/c/english/5, explain what you want to achieve, attaching a sample file, and pasting the information in Menu/Help/About LibreOffice, there is an icon to copy.
(In reply to Fergus from comment #0) > --(RANGE=CELL) * is a more capable Vlookup method used in intermediary > skill spreadsheets. It could be helpful to add (in your next comment) some link to some web page with an example or explanation of how this formula/syntax is expected to work (in whichever site/forum). As per the specific example presented in comment 0, does that work in MS Excel or in any other spreadsheet tool or in some older version of Calc?
(In reply to ady from comment #2) > (In reply to Fergus from comment #0) > > > --(RANGE=CELL) * is a more capable Vlookup method used in intermediary > > skill spreadsheets. > > It could be helpful to add (in your next comment) some link to some web page > with an example or explanation of how this formula/syntax is expected to > work (in whichever site/forum). > > As per the specific example presented in comment 0, does that work in MS > Excel or in any other spreadsheet tool or in some older version of Calc? Just to be clear, I agree with comment 1, unless proven differently, and that's the reason for my questions. FWIW, if the syntax is used as in the SUMPRODUCT() function, please be aware that SUMPRODUCT() acts as an array function without requiring its introduction as an array formula with Ctrl+Shift+Enter. Having said that, I would be happy to get a different usage/example, or a clearer explanation of what should be the formulas in cells A2 and A3 and their expected results.