Created attachment 118642 [details] ODS with sample lookup that fails Here is a document created with a version < 4.0 of OpenOffice. Some lookups show the results #N/D when a normal value is expected. I notice this behaviour when data and lookups are in a different tab. I couldn't predict which cells give an incorrect answer, neither find a workaround to make it work fine, but it seems that this is dependant to the looked up value. I noticed this with LibreOffice Calc 4.3.1, 5.0.0.5 and 5.0.1.2. When I rewrite the same file from scratch with Calc 5.0.1.2, I obtain the expected behaviour. This may be related to Bug 74558.
Created attachment 118643 [details] ODS which correct behavior, rewriten from scratch with Calc 5.0.1.2
Hi palmierivinc, thanks for your report. I confirm the behaviour. When I remove row 1 on sheet data in misfunctionnal lookup.ods the VlookUp works fine. Since the function gets the full columns as argument (data.D:D;data.A:A) that may be the cause? Any idea on that? Cheers - Cor
Yes you got it :) If I remove the first row, I still have some "#N/D". But if I remove the two first rows, the lookup works fine. Another way to make it work fine is to fill D1 with "a a" and D2 with "a a" (so that the column D is fully sorted). If I put the argument (data.D$3:D$50;data.A$3:A$50), it works fine too. When I rewrote the file from scratch, I forgot this blank line. Eventually, I obtain the same behaviour with all version of LibreOffice, caused by a lookup in an unsorted column. So, unless I forgot something, this is not a bug. Thank you Cor Nouws !
Thanks for confirming :)