Created attachment 193730 [details] File for testing Open attached document. It shows the used formula and right from it as text how the formula looks in Excel and how the result looks in Excel. Notice, that XLOOKUP as parameter in CELL does not work at all. Correct the formula of the CELL function rows to the value shown as text. Still there is no result. Save the file into ods format and open the ods file. Notice that now none of the formulas has a result. I have tested it with Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: d76f1877f699c91f5e4a020641c6d65064bfb997 CPU threads: 32; OS: Windows 10.0 Build 22631; UI render: default; VCL: win Locale: de-DE (de_DE); UI: en-US Calc: threaded
Balázs, could you please have a look?
(In reply to Regina Henschel from comment #1) > Balázs, could you please have a look? Thanks a lot for the test, Regina. :) Will take look. My first guess would be its wrong because of the Lookup_value is an empty cell: =XKERES(???;A2:A10;A2:A10) In that case hopefully will be easy to fix.
In XLOOKUP(), the argument named "Lookup" (aka "lookup_value" in Excel) is required, but if it is omitted then XLOOKUP() should return blank cells it finds in the "Array" area (aka "lookup_array" in Excel). Currently in Calc 24.8 alpha, this seems to be not true, so the search fails to find a match and thus the #N/A result is given. For the sample in this report, the first blank cell would return "itself", and Excel's XLOOKUP() returns 0 (zero) for blank (empty) results. In Excel, this is not the same as not finding a match. IIRC, also Google Sheets fails to comply with this condition, but I could be misremembering. @Regina, BTW, IINM, that build you posted in comment 0 lacks the latest modifications to XLOOKUP (adding regex support). It should not alter your result regarding XLOOKUP() for this case anyway. Please also keep in mind that other functions such as CELL() have their own bugs. I hope we are focusing on XLOOKUP() in this report.
(In reply to Regina Henschel from comment #0) > Save the file into ods format and open the ods file. Notice that now none of > the formulas has a result. Also this ods save/reload issue is not related to the ods filter (import/export). If you open the original bugdoc and just click on the hard recalculation all the function result will be #N/A instead of the expected result. Thanks Ady for the detailed feedback about the empty cells search. I will focus on that one to fix it in this bugreport.
JIC I was not clear enough (for whoever reads this ticket): * The problem in Calc 24.8 alpha ATM (vs. Excel) seems to be triggered when the "Search Criterion" argument is really _omitted_. * In Excel's XLOOKUP(), omitting the first argument in XLOOKUP() is _not_ the same as explicitly writing "" (aka empty string) as argument in the formula in Calc. * Omitting the "Search Criterion" argument is _not_ the same as using a reference to a cell that is blank empty. * In Calc 24.8 alpha ATM, omitting the "Search Criterion" argument seems to always result in (#N/A) error. * In Excel's XLOOKUP(), when omitting the "Search Criterion" argument, it performs a "blank empty cell" search in the "Search Array" area, providing the relevant result according to the whole set of arguments. Additionally: * Excel returns 0 (zero) when the _content_ of the _resulting_ cell/range ("Search Array" argument) is empty. This is _not_ related to the "Search Criterion" argument (except when the "Search Criterion" argument is omitted, as described above). This is _not_ the problem in Calc 24.8 alpha ATM.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/8df17c340193c89549d8c563b04d015156afa3fb tdf#160711 - sc fix XLOOKUP function search for empty cell It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I see it fixed in my build from today.