Created attachment 69385 [details] example of vlookup not comparing number values VLOOKUP is not comparing numbers. There is no ' in front of numbers. Example is in attachement.
Reproduced on 3.5.4.2 Ubuntu 12.04 x86 Not all numbers are in DATA spreadsheet A1:B20000 Those that are can be found by VLOOKUP with a manual trick: 1. Double click cell (ex. B311) 2. Select whole text in cell, but not with double click only by move from right to left or left to right 3. Click somewhere else
Reproduced on 3.6.3.2 Ubuntu 12.04 x86
@Marko How did you create the example file? With LibreOffice or with MS Office (which version)?
It is created by ART reporting tool (http://art.sourceforge.net/) version 2.3.1
ART uses Apache POI (http://sourceforge.net/p/art/wiki/Libraries/) for xls and xlsx report output. Could you export also to xlsx and check if bug is there? Is there possibility to export to odf file format?
Produced xls is strange numbers are behaving as text, but they have no ' in front, below are steps that after formatting cells changed all numbers to prefixed with '. 1. select all cells A1:B180 2. format cells as Text (@) 3. format cells as Numbers (Standard) Output: all numbers changed to prefixed with '. ex. '9798 Workaround is to convert numbers "'9798" to real numbers "9798" as below =IF(ISERR(DECIMAL(A1;10));A1;DECIMAL(A1;10)) It is possible that this is "not our bug", because Apache POI produces wrong xls. How MS Office is behaving, or other Offices? If cells contains prefixed numbers, than it is not VLOOKUPs fault but xls Import process.
Postovani, Bicu odsutan u periodu od 04.11.2013 do 11.11.2013. Za sve potrebne informacije mozete se obratiti Dragoslavu Hubacu na e-mail adresu dragoslav.hubac@mercata.rs ili na tel. (011) 3010 930. S postovanjem, Marko Stojanovic ______________________ Marko Stojanovic Asistent za IT podrsku IT Support Assistant Mercata d.o.o. PC USCE, Bulevar Mihajla Pupina 6, 11070 Belgrade, Serbia t +381 11 3010 898 f +381 11 3010 814 marko.stojanovic@mercata.rs www.mercata.rs MOLIMO VAS NE STAMPAJTE OVU PORUKU UKOLIKO NIJE NEOPHODNO Sacuvajmo prirodu! DON'T PRINT THIS E-MAIL UNLESS IT'S NECESSARY Please consider our environment!
Google Sheet and Excel produce the same results as LibreOffice. It's a malformed spreadsheet.