Bug 108268 - EDITING: Vlookup doesn't work
Summary: EDITING: Vlookup doesn't work
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Function-Vlookup
  Show dependency treegraph
Reported: 2017-05-31 15:29 UTC by Emersson Augusto Suarez Ortiz
Modified: 2017-07-25 03:41 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

The file whit the problem. (29.46 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-05-31 15:29 UTC, Emersson Augusto Suarez Ortiz
the other source of data (49.85 KB, application/wps-office.xlsx)
2017-05-31 15:30 UTC, Emersson Augusto Suarez Ortiz

Note You need to log in before you can comment on or make changes to this bug.
Description Emersson Augusto Suarez Ortiz 2017-05-31 15:29:42 UTC
Created attachment 133748 [details]
The file whit the problem.

Hello, I have a problem whit Vlookup, in the file you will see the sheet3 in the column B and C I have a formula for look the value in Column a. Column B looks in sheet2 in the same file and it doesn't works. Column C looks in a file xlsx and it works, whit errors.

This is a very useful function, and if we can't trust it, then we have a very huge problem.

I send the files, and I can see that is the same problem whit the bug 53277, but pleas do not dismiss it as RESOLVED INVALID
Comment 1 Emersson Augusto Suarez Ortiz 2017-05-31 15:30:19 UTC
Created attachment 133749 [details]
the other source of data
Comment 2 Xisco Faulí 2017-05-31 15:39:01 UTC Comment hidden (obsolete)
Comment 3 Emersson Augusto Suarez Ortiz 2017-05-31 22:49:30 UTC
It is still present in version 5.3.3
Comment 4 Emersson Augusto Suarez Ortiz 2017-05-31 23:53:59 UTC
The worst is to see that when I open the file with WPS, Excel, PlanMaker, it show the perfect data.
Comment 5 Julien Nabet 2017-06-01 19:26:49 UTC
On pc Debian x86-64 with master sources updated today, I could reproduce this.
I noticed 2 things:
1) if I change the 4th parameter to 1 (true) it seems to work
2) if I copy paste a case on a brand new file, it works

Notice, I don't say it'd work for every case, just a case I took.
Indeed I create a brand new file with:
in cell A1 and B1, I put:
Assistance EDD adultes (9h30)

in cell C1, I put:
it works

In brief, I think there's some config on your file or on your cells/sheet, I don't know, which make things fail.
Comment 6 GerardF 2017-06-01 19:44:14 UTC
It is simply the REGEX enabled within the file.
Menu Tools > Option > LO Calc : Enable regular expressions in formula.
'(' is a REGEX.
The special characters are . ^ $ * + ? \ [ ( { |

If you want a (V/H)LOOKUP with one of these characters, disable the regex.