https://bugs.documentfoundation.org/show_bug.cgi?id=170654
Regina Henschel <[email protected]> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |[email protected] --- Comment #2 from Regina Henschel <[email protected]> --- In regard to "#N/A" ------------------- Your spreadsheet has several problems: (A) You use the legacy xls-format. The native format in LibreOffice is the ods-format. All other formats might have converting problems. (B) The LOOKUP function requires that SearchVector (the range where you lookup the value) is sorted. That is not the case for the range $'Accounts List'.$B$10:$B$93. In a range with mixed number and text, the text has to be after the numbers. See help https://help.libreoffice.org/26.8/en-US/text/scalc/01/func_lookup.html (C) You search with a reference to an empty cell. The standardization of LOOKUP function has no rule for this special case. You should avoid such situations. The behavior in LibreOffice and OpenOffice has always been to produce a #N/A error, Excel renders it blank. The LOOKUP function is not explicitly mentioned in https://help.libreoffice.org/26.8/en-US/text/scalc/05/empty_cells.html. The examples there could be extended. For that a new bug report with Component 'Documentation' is needed. You can suppress the error message, when you wrap the LOOKUP function with a IF(ISBLANK(E3);"";LOOKUP(E3;...)). (D) You use the legacy LOOKUP function. You should use the XLOOKUP function instead, that is available in xlsx-format and in current LibreOffice. It allows to specify the result for the case of "Not Found" and it has a mode that does not require that SearchVector is sorted. in regard to cell background color: ---------------------------------- I see pink cells in Excel as well. Which cell is different between Excel and LibreOffice? BTW: (1) Such large document is very unhandy to examine. Provide a document that is a small as possible. (2) A document attached to a bug report should not be protected. Only in case the issue is about the protection and in that case the password has to be told. -- You are receiving this mail because: You are the assignee for the bug.
