Ask Your Question

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? [closed]

asked 2014-03-06 19:57:59 +0200

mreky2 gravatar image

updated 2015-08-31 08:30:03 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-23 21:50:35.816280

3 Answers

Sort by » oldest newest most voted

answered 2014-05-29 13:12:41 +0200

hhhello gravatar image

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 ;-)

edit flag offensive delete link more

answered 2014-03-07 12:47:43 +0200

Pedro gravatar image

updated 2014-03-07 17:55:03 +0200

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...

edit flag offensive delete link more


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.

mreky2 gravatar imagemreky2 ( 2014-03-07 17:10:06 +0200 )edit

@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: 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).

Pedro gravatar imagePedro ( 2014-03-07 17:26:44 +0200 )edit

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: Build ID: d7dbbd7842e6a58b0f521599204e827654e1fb8b (just updated)

mreky2 gravatar imagemreky2 ( 2014-03-07 20:33:26 +0200 )edit

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?

mreky2 gravatar imagemreky2 ( 2014-03-15 19:41:01 +0200 )edit

@mreky2, you can also try (aka RC1) which is already available from some of the official mirrors (e.g. ) 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.

Pedro gravatar imagePedro ( 2014-03-18 12:45:10 +0200 )edit

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?

Gdcondor gravatar imageGdcondor ( 2014-04-28 20:46:24 +0200 )edit

answered 2014-03-07 03:29:06 +0200

mreky2 gravatar image

updated 2014-03-07 03:33:21 +0200

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.

edit flag offensive delete link more


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.

mreky2 gravatar imagemreky2 ( 2014-03-07 17:14:15 +0200 )edit

Question Tools



Asked: 2014-03-06 19:57:59 +0200

Seen: 1,316 times

Last updated: May 29 '14