GNOME Bugzilla – Bug 122085
vlookup returns 0 instead of #na when referencing empty cell as search criterion
Last modified: 2004-12-22 21:47:04 UTC
vlookup (at least in version 1.1.20/1.1.90) does not return #NV if an empty cell is referenced as search criterion. For example A1="vlookup(a2;b1:c2;2)" and a2=empty, then it will return 0, while other spreadsheets (Excel, OpenOffice) return #NA or similar. This also breaks the usage of iferror to detect, if a search was successful.
Interesting, in current cvs I seem to get for A1: =vlookup(A2,B1:C2,2) the result of #N/A as requested. Trying in german locale yields #NV. How come that I don't get your problem. Did somebody already fix this?
Could you please append a gnumeric worksheet that shows your problem? Thanks
Created attachment 19915 [details] worksheet that shows the problem (for me)
I am sorry that my first report was not complete/slightly wrong. I forgot to say that I am searching unsorted tables of variable sizes, so you need to specify a larger array and give 0 as the last optional parameter. I hope I have it right this time... Thanks for your patience
The function also returns "0" when the search value is "0" and this problem should also occur for other lookup functions like hlookup etc. I did a (very) short debugging session and it seems as if empty cells are reported as VALUE_INTEGER, so find_type_valid returns TRUE for these cells. I am not familiar with your code - but this seems to be the problem. There is a match for every empty cell in the range (because empty cell=integer cell with value "0") I would trace the problem down further, but I don't have enough time...
I had another look at the problem (gnumeric 1.2.0). There is the function handle_empty in src/expr.c which calls "value_new_int(0)" to return empty cell values if NULL is not permitted. If I change both lines to "value_new_empty()" my problem is solved (apart from still receiving "0" as result if I search for a non-existent "0") But these changes might be entirely wrong, because - as I said - I don't know anything about the gnumeric code and I didn't test my change very well for other side effects. BTW: Can you confirm this problem, by now?
I can replicate. The proposed solution is too invasive. We need something more localized. I'll have a look.
Solution is to add another arg type 'B' that enforces scalar like the previous 'S' but also allows empties. Patched match/lookup/vlookup/hlookup for 1.2.1