I was working with more than 10k rows of data with Microsoft Excel 2007 before with lot of Vlookup, Countif and Sumproduct functions placed on different column in one file. Recently I’ve got dozens important data generated by an application which can not be read by MS Excel, even though it was 97-2003 excel format (xls), it’s always said that “Excel found unreadable content”.
Lucky LibreOffice Calc somehow can read the file without any problem.
The file contain only static data, which is no function used on it. Now i have to fill some columns on this file referenced by external MS Excel files using Vlookup function. One more problem raised, every-time i tried to use vlookup on it (with LibreOffice calc), it always give me error results.
Tried several time and the result always the same, until i re-read their official help web about vlookup here.
SortOrder (…) Enter the Boolean value FALSE or zero if the first column is not sorted in ascending order.
Somehow LibreOffice Clac can not use FALSE as boolean value, the function only work with 0 (zero) or 1 (one) as the boolean value.
On conclusion regarding Vlookup, Microsoft Office Excel read TRUE/FALSE as boolean value while LibreOffice Calc use ONE (1)/ZERO (0) as their boolean value.
MS Office Excel Example:
LibreOffice Calc Example:
Now there are one more question, do i have to manually change all vlookup function on dozens unreadable excel files by Microsoft Office Excel 2007? or i just convert them manually to 2007-2010 excel format using LibreOffice Calc ? (Last one look better solution, but still i have to convert manually one at a time).