GNOME Bugzilla – Bug 593238
VLOOKUP/HLLOKUP/MATCH sometimes return wrong results
Last modified: 2009-08-27 15:45:06 UTC
Created attachment 141803 [details] sample file I will be attaching a file from https://bugs.launchpad.net/ubuntu/+source/gnumeric/+bug/418366 That bug report is slightly confused, but the main problem i see is that in the file the calculation of F6:F8 on the "Buggy lookup" sheet appears incorrect.
Unrelatedly, the code or this help text is wrong. The default is TRUE. { GNM_FUNC_HELP_ARG, F_("approximate:if false, an exact match of @{value} " "must be found; defaults to FALSE")},
I see the problem: the caches for hlookup and vlookup need to be separate.
Regarding comment 1 bor both Excel and ODF, the default is TRUE, so the documentation is incorrect.
Fixed. C7:D8 still have errors, but I think that is because the formulas are wrong. (It looks like they should use the as-index mode of h/vlookup.)
Note, that the values calculated (before the fix) depended on evaluation order. Lots of strangeness can be explained that way.
re comment4: Excel doesn't have the as-index mode it seems, so since he uses xls file format he may depend on compatibility with XL.
re comment 5: my point was that the formulas aren't right and that C7:D8 are in error because of that. I believe Gnumeric does the right thing. To get the index, MATCH can probably be used.