LibreOffice Calc - Anomaly discovered in versions 4.2.8 and 4.4.6 - VLOOKUP function

users
#1

Anomaly discovered in *versions 4.2.8 and 4.4.6* -
VLOOKUP function

Good morning,
.

LibreOfficeCalc use on a package of administration that contains many
features that interact with Pivot Tables

A very used is VLOOKUP.
The package works very well and there are no problems on the LO version
4.0.6
both Windows and Linux (Ubuntu 12.04 LTS, 4.14 32bit or 64bit).
In this version of the function's result it is right.

THE versions 4.2.8 and 4.4.6 the VLOOKUP function from incorrect result.

In particular, in the spreadsheet attached "PROVA SU VERS 446 -
CONTABILMENTE":

- In Sheet "Registro Mastri' function is in cell B4:
= VLOOKUP (+ B6; PianocontiCE.$A$3.$B$1060;2)

the function result is # N / A

correct resultate is: "CONTO TRANSITORIO PAGHE LL.DD."

I checked several times.
The function syntax is correct.
The reference of the search range is correct;
The search key is correct;
The index of the data result is correct.

My interpretation is that in later versions to 4.0.6 LO does not recognize
the data contained in a PivotTable
(Ie the value of Cod. C / R in the example = 3359)
This might be so?

I kindly ask the technicians LO to verify that this bug is really very
important because it undermines the proper functioning of the package
(which is distributed free to download).

I look forward to answers.

Best regards.

Michele Ras

#2

menu:Tools>Options>Calc>Calculation
Regular expression = OFF
Match whole cell = ON

You leave out the 4th argument which means it defaults to 1. You want 0.
VLOOKUP(x;range;2;0)

#3

Thanks, I solved the problem by entering the 4th argument to 0.

The solution, as I look for now is effective.

cordial greetings

Michele Ras

#4

Thanks, I solved the problem by entering the 4th argument to 0.

The solution, as I look for now is effective.

cordial greetings

Michele Ras

You will get wrong matches or wrong #N/A errors if the "regular
expressions" option is ON and your search expressions contain certain
characters. With your own example

CONTO TRANSITORIO PAGHE LL.DD.

will match any characters at the positions where the points are

You may get wrong positive matches when the "match whole cells" option
is OFF.
Search expression CONTO TRANSITORIO PAGHE will match at CONTO
TRANSITORIO PAGHE LL.DD.