Bug 85553 - Vlookup returns #N/A if save in .xls
Summary: Vlookup returns #N/A if save in .xls
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.6.3 release
Hardware: All All
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:5.3.0 target:5.2.3 target:5.2....
Keywords: bibisected, bisected, regression
Depends on:
Blocks:
 
Reported: 2014-10-28 12:57 UTC by Konstantin
Modified: 2022-04-04 18:20 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample file (9.50 KB, application/excel)
2014-10-28 12:57 UTC, Konstantin
Details
sample file 2 (9.50 KB, application/vnd.ms-excel)
2014-10-29 12:27 UTC, Konstantin
Details
Sample file 1 (7.00 KB, application/vnd.ms-excel)
2014-10-29 12:28 UTC, Konstantin
Details
Sample file in .ods (36.62 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-29 12:30 UTC, Konstantin
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Konstantin 2014-10-28 12:57:02 UTC
Created attachment 108570 [details]
Sample file

When using vlookup function in Calc and saving document in .xls it always return #N/A. If you'll save it in .ods - function will became working. The problem does not occur with 4.1.6.2 and earlier versions and occur in 4.2.6 and 4.3.2 on both Linux and Windows versions.
Earlier problem was described and fixed in in https://www.libreoffice.org/bugzilla/show_bug.cgi?id=75642 and https://www.libreoffice.org/bugzilla/show_bug.cgi?id=74451 but only for .ods.
P.S. Sorry for my poor English and thanks in advance :)
Comment 1 raal 2014-10-29 08:40:49 UTC
Hello,
vlookup function in your file find data in external file
=VLOOKUP(A3;'file:///C:/temp/home/yar4e/Рабочий стол/home/finman/Рабочий стол/Файл 1.xls'#$Лист1.A3:B23;2;0)

Please attach .ods file and external file to reproduce the bug. Thank you
Comment 2 Konstantin 2014-10-29 11:50:56 UTC
(In reply to raal from comment #1)
> Hello,
> vlookup function in your file find data in external file
> =VLOOKUP(A3;'file:///C:/temp/home/yar4e/Рабочий стол/home/finman/Рабочий
> стол/Файл 1.xls'#$Лист1.A3:B23;2;0)
> 
> Please attach .ods file and external file to reproduce the bug. Thank you

Hello! Yes it linked with external file but you don't need external file to reproduce the bug. All you need to do is open sample file with LO 4.1.x and LO 4.2-4.3 and compare the results. In LO 4.1.x (and MSO 2010) sample file opens with correct values of vlookup and in LO 4.2+ it opens with #N/A values. If you'll save sample file as .ods and will open it in any version of LO you'll see correct values of vlookup function. I attached all files you requested to make sure you'll get all needed data for reproduce the bug.
Comment 3 Konstantin 2014-10-29 12:27:45 UTC
Created attachment 108621 [details]
sample file 2

This file illustrates the problem
Comment 4 Konstantin 2014-10-29 12:28:57 UTC
Created attachment 108622 [details]
Sample file 1
Comment 5 Konstantin 2014-10-29 12:30:25 UTC
Created attachment 108623 [details]
Sample file in .ods
Comment 6 raal 2014-10-29 19:50:33 UTC
I can confirm with Version: 4.4.0.0.alpha1+
Build ID: 04ea7b24ec1b5a027efa0b850f2bc3ac7116c52e
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2014-10-25_08:38:01

With LO 3.5 I can see cached results in xls file, regression.
Comment 7 raal 2014-11-08 21:43:28 UTC
git bisect log
# bad: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e
# good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932
git bisect start 'latest' 'oldest'
# good: [e02439a3d6297a1f5334fa558ddec5ef4212c574] source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb
git bisect good e02439a3d6297a1f5334fa558ddec5ef4212c574
# bad: [4850941efe43ae800be5c76e1102ab80ac2c085d] source-hash-980a6e552502f02f12c15bfb1c9f8e6269499f4b
git bisect bad 4850941efe43ae800be5c76e1102ab80ac2c085d
# skip: [a043626b542eb8314218d7439534dce2fc325304] source-hash-9379a922c07df3cdb7d567cc88dfaaa39ead3681
git bisect skip a043626b542eb8314218d7439534dce2fc325304
# skip: [aba65c3e4c0df07e4909aeefb758cdb688242bf6] source-hash-827524abfb4b577d08276fde40929a9adfb7ff1a
git bisect skip aba65c3e4c0df07e4909aeefb758cdb688242bf6
# skip: [aba65c3e4c0df07e4909aeefb758cdb688242bf6] source-hash-827524abfb4b577d08276fde40929a9adfb7ff1a
git bisect skip aba65c3e4c0df07e4909aeefb758cdb688242bf6
# bad: [c81a8a0dcfc1ed095a80e4485c89dd0fcaf73f31] source-hash-c69ed33628ec0b7abf6296539cf280d6c4265930
git bisect bad c81a8a0dcfc1ed095a80e4485c89dd0fcaf73f31
# bad: [c81a8a0dcfc1ed095a80e4485c89dd0fcaf73f31] source-hash-c69ed33628ec0b7abf6296539cf280d6c4265930
git bisect bad c81a8a0dcfc1ed095a80e4485c89dd0fcaf73f31
# good: [1d4980621741d3050a5fe61b247c157d769988f2] source-hash-89d01a7d8028ddb765e02c116d202a2435894217
git bisect good 1d4980621741d3050a5fe61b247c157d769988f2
# skip: [89110ca258fa7a15dfc546acfb39e76fc3eb2a44] source-hash-e450a2c506ac7cd4433b0f93fc750a89919bc03c
git bisect skip 89110ca258fa7a15dfc546acfb39e76fc3eb2a44
# good: [1cca92a409385d9288c28a54d5e3008e56728bc0] source-hash-7be7824bbbdeee6fa998b950e6046ab37fe690cb
git bisect good 1cca92a409385d9288c28a54d5e3008e56728bc0
# skip: [5fa28ce2931a35ae64ae08d3904cfb76d24459d8] source-hash-2304beaca33c63b94df99cb827716f00ce259f9a
git bisect skip 5fa28ce2931a35ae64ae08d3904cfb76d24459d8
# bad: [2a9ff869c5638dc5c3aa387d0fe55c3291c86288] source-hash-01b7e04172889cbc9e4ac404b105e18ddc062d6f
git bisect bad 2a9ff869c5638dc5c3aa387d0fe55c3291c86288
# good: [9771d0c212cfa71b07742ff3dc5c05df22d600eb] source-hash-a9a0933ec67eab0ec31c8fadb60fb8e8e3e90485
git bisect good 9771d0c212cfa71b07742ff3dc5c05df22d600eb
# skip: [edd0c98bc8eba396299d252906dea0898436e42a] source-hash-6489f97fc428446c53c17f885963776cdcc21490
git bisect skip edd0c98bc8eba396299d252906dea0898436e42a
# skip: [9fc19519e5bc25b68aff579d6f6dd57553e21cb4] source-hash-d63a69a087c9c7641e28e2002d7ad56076d08ca1
git bisect skip 9fc19519e5bc25b68aff579d6f6dd57553e21cb4
# skip: [8cb23b3181bfae3988e27d5621476e8fc74ab823] source-hash-132731f0704b5a9e996cb0d413c8e369efe8875f
git bisect skip 8cb23b3181bfae3988e27d5621476e8fc74ab823
# skip: [79ff8e93eb0ddcebca4f814fc6883da8e1485c9f] source-hash-0bca15197461f9e0c6f28ce301c2fed2ec4b38cb
git bisect skip 79ff8e93eb0ddcebca4f814fc6883da8e1485c9f
# skip: [6b6b50cddd82bdfd45433bb7f8b31663211e2e13] source-hash-c3ef735fd5d8866c8ce28b89744150e733087427
git bisect skip 6b6b50cddd82bdfd45433bb7f8b31663211e2e13
# good: [91460ba3ce3a0efa5693f098c1072b058903a31c] source-hash-69f61526cd4268a330be1e835e9e52f9b1dbde50
git bisect good 91460ba3ce3a0efa5693f098c1072b058903a31c
# good: [8988ebdd001d592339b7f8a64f1dcb4191c2396e] source-hash-beb43ed0942dcd5f14d3d49b407878866e3d248d
git bisect good 8988ebdd001d592339b7f8a64f1dcb4191c2396e
# skip: [9c14d560e000ed66786dfab9f9faa60f158e207b] source-hash-51c647cf744480db7ce1c450f6668727cc75d74b
git bisect skip 9c14d560e000ed66786dfab9f9faa60f158e207b
# good: [36f3dc5427eb2e1af3ddd629e0e7e199b7782fae] source-hash-21055f014c2d98f9f57ee81da9c54a4218d45e0d
git bisect good 36f3dc5427eb2e1af3ddd629e0e7e199b7782fae
# bad: [560f398ecbc305b65a32a4ba0b9e4444761db889] source-hash-dace560b350346b9f9a102ee602bb129a008bcfe
git bisect bad 560f398ecbc305b65a32a4ba0b9e4444761db889
# first bad commit: [560f398ecbc305b65a32a4ba0b9e4444761db889] source-hash-dace560b350346b9f9a102ee602bb129a008bcfe

http://cgit.freedesktop.org/libreoffice/core/log/?qt=range&q=21055f014c2d98f9f57ee81da9c54a4218d45e0d..dace560b350346b9f9a102ee602bb129a008bcfe

Maybe this commit "Fix VLOOKUP calculation when the matching value is literal string".	Kohei Yoshida, commit b3e35aa551fa8abc0215a0ef416fc9acbbc3c0d9
Comment 8 Matthew Francis 2014-12-25 15:19:00 UTC
It actually seems to be at this commit that the cached values stopped being shown:

commit 7333881bb7b04f7e4e2a28638024ae82a9c14e81
Author: Kohei Yoshida <kohei.yoshida@collabora.com>
Date:   Thu Oct 10 20:24:21 2013 -0400

    Formula tokens, formula cells and formula interpreters to use shared strings.
    
    Change-Id: I5da99869fc7f61ce698180fa5daa9be9db9ac132
Comment 9 Alexander 2015-08-17 13:40:36 UTC
It is still relevant to
Calc: 5.0.0.5
ID : 1b1a90865e348b492231e1c451437d7a15bb262b
Win 7 pro VL 32bit

when opening a file in xls, saved in excel, having the function VLOOKUP to external sources, in case of failure update, issues # N / A
Comment 10 Robinson Tryon (qubit) 2015-12-13 11:10:52 UTC Comment hidden (obsolete)
Comment 11 Xisco Faulí 2016-10-03 09:23:38 UTC
Adding Cc: to Kohei Yoshida
Comment 12 Eike Rathke 2016-10-19 22:53:30 UTC
Taking.
Comment 13 Commit Notification 2016-10-19 23:00:54 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0d2797fdb06f504e7213b3859a53c363f4f56bc9

Resolves: tdf#85553 intern SharedString of cached external references result

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 14 Eike Rathke 2016-10-19 23:13:15 UTC
Pending review https://gerrit.libreoffice.org/30068 for 5-2
Comment 15 Commit Notification 2016-10-25 11:13:02 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=15054d82cc94a392ff708d499e395ab184818c24&h=libreoffice-5-2-3

Resolves: tdf#85553 intern SharedString of cached external references result

It will be available in 5.2.3.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 16 Commit Notification 2016-10-26 22:53:51 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e2dc12fa1803d5e3d6a06b2533d04c1bc54c8113&h=libreoffice-5-2

Resolves: tdf#85553 intern SharedString of cached external references result

It will be available in 5.2.4.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 17 Commit Notification 2022-04-04 18:20:33 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/0225f1dd04e49191bbf1fa787bb15d1617ab996c

tdf#85553: subsequent_export_test2: Add unittest

It will be available in 7.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.