Vlookup worked in LO 4.1/earlier but mostly fails with N/A return in 4.2. What changed and how do I fix it?

English
, , ,
#1

Large spreadsheet with multiple pages. Databases are on back pages in the same file. VLOOKUP to those pages worked in Excel, Quattro Pro/Windows, and all SCALC (OO/LO) through LO 4.1. Now it mostly (but not completely) fails returning ‘N/A’. What changed and do I have to completely rebuild the spreadsheet (big job!) to fix it (or downgrade back to 4.1)? Release notes mention something about VLOOKUP changes (shared strings? with what?) for faster searching but no details. Sample VLOOKUP syntax (which works in some cells but not in others, with no changes other than the cell reference for lookup):

=IF(E20<100," ",VLOOKUP(E20,$Accounts.$A$1:$B$139,2))

Note: all cells being used as references are numeric, not text. All cells in the search column in the databases are numeric. And the database is sorted on that column. The result should be the text which is in the second column of the database; works about 1/2 of the time.

#2

AHA! Found it. The syntax of VLOOKUP changed in 4.2 (or else it’s now being interpreted differently/more strictly). There’s a “sort order” parameter now, which apparently is mandatory. Took some seriouis spelunking in the help files. So if I change the formula to:

=IF(E20<100," ",VLOOKUP(E20,$Accounts.$A$1:$B$139,2**,0**))

all the lookups work again with that change, which is handled easily with a copy/paste (unfortunately in many files - will need to leave a note someplace so others that run into it on older files will know what to fix). Note that “,0” at the end of the parameter list. Grump - might have been nice to flag that in the release notes. But we’re working again.

#3

Not completely. The spreadsheets I’m using have been working since Quattro Pro/Win and StarOffice 4.2. As I noted in the comment above, the formula returned the nearest value as expected. What seems to have changed in LO 4.2 is that the default lookup is no longer reliable, and I have to add the parameter. Might be a side effect of something else, but the symptom is real.

#4

There was no change in syntax and omitting the last parameter is still the same as making it 1.

When the last parameter is equal to 1 (and this is the same as it has always worked since Excel 5.0, back in 1993) means that the nearest value will be returned. This is not good when you want an exact match.

This means that your formula is not returning what you expect. In your position I would carefully revise all my other spreadsheets…

#5

Thanks, but … something did change because the lookups worked in all past versions (back into StarOffice even). Yes, without that last parameter, if the match wasn’t perfect it returned the nearest value - which was acceptable in this application. THAT doesn’t work any more since 4.2, at least not most of the time. If it failed all of the time, I would have found the issue sooner.

#6

@mreky2, can you provide a sample file with errors? If this is really a regression it needs to be fixed. Also, you need to mention which version exactly you are using e.g. Version: 4.2.2.1 Build ID: 3be8cda0bddd8e430d8cda1ebfd581265cca5a0f (just copy and paste from the About dialog) and which OS and version (and in the case of Linux, also the distro).

#7

OK - can sanitize one of the old files and send it in, but I don’t have a Skydrive or the like account. Will see if I can reactivate something else formerly used for big emails.

EDIT: saw in the faqs how to attach a file - maybe. Link would still be better.

Oh yes: Windows 7 SP1 64-bit fully patched. Has occurred in both stable releases of 4.2 I’ve installed - currently Version: 4.2.1.11 Build ID: d7dbbd7842e6a58b0f521599204e827654e1fb8b (just updated)

#8

Another problem has popped up that, combined with this one, is forcing me to downgrade to 4.1. Too many things going on with 4.2. While this one is fixed by adding the explicit sort order parameter, the other one (I may add an ‘ask’ item about it) simply fails. So the “answer” for this question, really, is to drop 4.2 for a general user like me.

I was unable to get a dropbox-type thing working to post a copy of a sanitized file, in any case. Can I post one to a bug if I open one?

#9

@mreky2, you can also try 4.2.3.1 (aka RC1) which is already available from some of the official mirrors (e.g. Index of /pub/tdf/libreoffice/testing/)
And yes, you can attach files to bug reports in Bugzilla. The earlier you report it, the sooner it has the chance of being fixed.

#10

I have exactly the same issue here! I just upgrade from Ubuntu 13.10 to 14.04 so LO was updated from 4.1 .x to 4.2.x (don’t know exactly) and all my previous documents which were working with no problem have exactly the same issue (#N/A) almost half of the time.
Sometimes I just need to delete the cells VLOOKUP is searching then undo and it is working again (!)
It is a major issue for me as almost all the documents I am working with for years have this issue now…
Any idea?

#11

Same problem here. Although the “sort order” was not ommitted and specified as “0”, exact match, all vlookups gave #NA. After sorting the lookup range the vlookup results returned miraciously :wink:

#12

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.