After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 122085 - vlookup returns 0 instead of #na when referencing empty cell as search criterion
vlookup returns 0 instead of #na when referencing empty cell as search criterion
Status: RESOLVED FIXED
Product: Gnumeric
Classification: Applications
Component: Analytics
git master
Other All
: Normal normal
: ---
Assigned To: Jody Goldberg
Jody Goldberg
Depends on:
Blocks:
 
 
Reported: 2003-09-12 07:07 UTC by mkoschwitz
Modified: 2004-12-22 21:47 UTC
See Also:
GNOME target: ---
GNOME version: ---


Attachments
worksheet that shows the problem (for me) (2.03 KB, application/x-gnumeric)
2003-09-14 07:29 UTC, mkoschwitz
Details

Description mkoschwitz 2003-09-12 07:07:02 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.
Comment 1 Andreas J. Guelzow 2003-09-14 05:19:08 UTC
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?
Comment 2 Andreas J. Guelzow 2003-09-14 05:23:16 UTC
Could you please append a gnumeric worksheet that shows your problem?

Thanks
Comment 3 mkoschwitz 2003-09-14 07:29:22 UTC
Created attachment 19915 [details]
worksheet that shows the problem (for me)
Comment 4 mkoschwitz 2003-09-14 07:33:10 UTC
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
Comment 5 mkoschwitz 2003-09-14 13:46:42 UTC
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...
Comment 6 mkoschwitz 2003-09-19 20:28:28 UTC
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?
Comment 7 Jody Goldberg 2003-09-20 00:18:43 UTC
I can replicate.

The proposed solution is too invasive.  We need something more
localized.  I'll have a look.
Comment 8 Jody Goldberg 2003-10-05 23:42:41 UTC
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