VLOOHUP ( search criterion; ARRAY; index) index in 1-2-3 column number 0,1,2,etc Libre calc appears to want column number 1,2,3,etc in other words the number returned in Libre Calc is off by one column because it starts counting as 1 while the Louts 1-2-3 file starts at 0.
If you have an example Lotus 1-2-3 file, please remove any proprietary info and attach the file here. That way we can use it for testing.
Created attachment 176181 [details] TaxTest Received attachment via email.
From email: Attached is a Lotus 1-2-3 file that shows the problem. This is not the file that I first found the problem, but a much simpler file that shows the problem. The file that first showed the problem is too large with too much personal info. In the attached file, Input a income amount in cell E4 and cell F4 should show the amount of taxes. When you first import the spreadsheet into Libre calc cell C4 shows 20000 and cell F4 shows 2002 which is correct but when the spreadsheet is recalculated cell F4 shows Err:502. Simply re enter 20000 or any number into cell E4 to recalculate the spreadsheet and see the error. I figured out what is causing the problem, but not a easy fix. The problem is caused by the index number in the VLOOKUP function. VLOOKUP is looking for a number in column A,B or C of the spreadsheet depending on the index number. Lotus 1-2-3 wants the index numbers to start at 0. In this case, Columns A,B or C would be 0,1 or 2. Libre Calc wants the index numbers to start at 1. In this case, Columns A,B or C should be 1,2 or 3. I could change the index numbers after the file is imported, but this requires a lot of work as my spreadsheets are very large. This would work if it was a one time import but I must maintain the file on an older computer because of the Lotus Macro language programing in the file. I wish to import the file to another computer that will not run Lotus 1-2-3 but as you can see from the attached file the VLOOKUP function does not work. Thank you for your assistance Stuart Sabatini
On pc Debian x86-64 with master sources updated today, I could reproduce this.
Dear Stuart Sabatini, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug