Vlookup, Microsoft Excel and LibreOffice Calc

Last updated on December 19, 2012 @ 2 Comments

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.

MS Excel Read Error

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.

Error Vlookup LibreOffice

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.

LibreOffice Working FIne

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).

M Riza

Article by M Riza

Is a blogger since 2007, founder and editor of Oa Ultimate. Working as a network administrator, computer technician, database and web maintainer | Twitter | G+

2 Comments → “Vlookup, Microsoft Excel and LibreOffice Calc”

  1. ac

    use true()

    • M Riza

      Nice, it work like a charm for both excel and calc, thx for sharing.
      Since we’re using FALSE for exact match, so that would be:
      =VLOOKUP(A2;src!A2:B15;2;false()) #for excel
      =VLOOKUP(A2;src.A2:B15;2;false()) #for calc

Leave a Reply


Loading Google+ Comments ...
Loading Facebook Comments ...