[Calc] The LOOKUP() function (Calc vs. Excel)

Carlo_Strata · November 29, 2014, 3:29pm

Hi Everyone,

I recently deal with a supplier catalog/price list spreadsheet xlsx file.

That file use the "LOOKUP()" function (in Italy "RICERCA()") in many places. In one of those occurrences, the first LOOKUP() parameter (Search Criterion) was missed, but on Microsoft Excel this was ok (no errors in the cell), in LibreOffice was ko (Err:511, missing variable/parameter)! I think this difference was yielded by different assumed values of the missing parameter and/or different validation rules.

So compliance depends not only on file format, but also on function behaviour/validation on different program.

This is a Vendor Lockin trouble?
http://en.wikipedia.org/wiki/Vendor_lock-in

What do you think about? Is this a known issue?

The work environment is:
- Microsoft Windows 8.1, 64 bit, Ita gui, daily full updated;
- LibreOffice 4.3.5.1, win32, Ita gui and local help;
- Microsoft Office 2013 Professional Plus trial version (I had installed and then uninstalled in my notebook some weeks ago).

Have All a nice weekend,

Carlo

Luuk · November 29, 2014, 7:07pm

I do not see a difference, besides a language difference (i have Dutch version of Excel, en LO in English)
(in Excel is see '#N/B', when LOOKUP did not found, in LO is see '#N/A')

I uploaded my testfile here:
http://wikisend.com/download/402228/zoeken.xlsx

(LOOKUP, in Dutch "ZOEKEN")

Brian_Barker · November 30, 2014, 10:49am

I don't think the questioner is referring to the situation in your example, where the cell referred to by the first parameter is empty, but that where the LOOKUP() reference itself contains no first parameter - in other words, where its opening parenthesis is followed immediately by the separator character. In that case, the behaviour does appear to be different: LibreOffice treats it as faulty formula, whereas Microsoft Excel evaluates the (slightly deficient) formula and returns #N/A.

Brian Barker

Carlo_Strata · November 30, 2014, 11:16am

Hi Everyone,

Brian understood exactly what I meant. I.e.:
=LOOKUP(;A5)

Have a nice sunday,

Carlo

Luuk · November 30, 2014, 4:49pm

according to Microsoft, this firt value is 'REQUIRED'

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

lookup_value (*required*)

The value you want to lookup. The value you want to look up must be in the first column of the range of cells you specify in table-array .

For example, if table-array spans cells B2:D7, then your lookup_value must be in column B. See the graphic below. Lookup_value can be a value or a reference to a cell.

or, in the Vector form:
LOOKUP(lookup_value, lookup_vector, [result_vector])

The LOOKUP function vector form syntax has the following arguments:

lookup_value *Required*. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.

see: http://goo.gl/SqmmS9

Carlo_Strata · November 30, 2014, 10:37pm

... so this is an Excel problem because it behaves differently with respect to its own help...?!!!

In any case this "Excel bug" yields some documents with some uncompleted/uncorrected formulas that don't generate errors in the corresponding cells... with the concrete risk that this become "a standard" behavior and/or some additional work in migrating to LO, for example.

Isn't it?

Carlo

ing. Carlo Strata

Stefan_Weigel · December 1, 2014, 8:18am

Hi,

... so this is an Excel problem because it behaves differently with
respect to its own help...?!!!

Not really.

Excel states, that you must apply the first parameter and you must
not leave it empty. (It´s required.) If you follow this, everything
is fine. And it´s the same thing in LibreOffice.

There is no specification for the behaviour of the function, in case
it´s not used according to the rules.

You can expect Excel and LibreOffice behave the same, if you use
functions according to their definition and the instructions given
in the documentation. You cannot expect identical behaviour, in case
you violate the instructions.

--> Your Problem is not an Excel problem neither a LibreOffice
problem, it´s a users problem.

Cheers,
Stefan

Stefan_Weigel1 · December 1, 2014, 8:18am

Hi,

... so this is an Excel problem because it behaves differently with
respect to its own help...?!!!

Not really.

Excel states, that you must apply the first parameter and you must
not leave it empty. (It´s required.) If you follow this, everything
is fine. And it´s the same thing in LibreOffice.

There is no specification for the behaviour of the function, in case
it´s not used according to the rules.

You can expect Excel and LibreOffice behave the same, if you use
functions according to their definition and the instructions given
in the documentation. You cannot expect identical behaviour, in case
you violate the instructions.

--> Your Problem is not an Excel problem neither a LibreOffice
problem, it´s a users problem.

Cheers,
Stefan

Carlo_Strata · December 1, 2014, 1:05pm

From what I remember, in Excel the formula does not yield an error or something like that, but a numerical result!!!

The original formula is (I translate the functions from Italian to English named ones):
=IF(ISERROR(LOOKUP("+";E7));D7-D7*E7%;(D7-D7*MID(E7;1;LOOKUP("+";E7)-1)%)-(D7-D7*MID(E7;1;LOOKUP("+";E7)-1)%)*MID(E7;LOOKUP("+";E7)+1;LEN(E7)-LOOKUP(;E7))%)
where:
- D7 contains final user price (full price);
- E7 contains the %discount (i.e. either "24" or "23+5");
- F7 contains the upper big formula;
- the trouble is in the last "LOOKUP(;E7)".

I suppose (!) there is an addition to the two behaviors ("Err:511" and "#N/A", respectively from LO and Excel) described by Luuk: Excel evaluate only the part it calculate and not the entire formula or loose the internal "#N/A" in calculations. I cannot check this because I have not Excel anymore on my notebook...

In other words, if you use a formula like this:
=IF(1=1;3;LOOKUP(;"peace"))

LO check the entire formula and answer "Err:511" and Excel? Someone may check for this?

In any case I agree with Stefan, this is not an Excel problem neither a LibreOffice problem, it´s a users problem.

Thank you very much,

Carlo

p.s. The fixed formula has a plus sign on the first parameter, that is: "LOOKUP("+";E7)", like other ones.

Stefan_Weigel1 · December 1, 2014, 4:00pm

very much depending on locale, of course

Stefan

Carlo_Strata · December 1, 2014, 4:05pm

Thank you very much Brian.

Have a nice evening,

Carlo

ing. Carlo Strata

Brian_Barker · December 1, 2014, 4:44pm

Aaargh! Sorry: I hadn't realised that - though it's obvious when you think about it ...

Thanks for setting me right.

Brian Barker

Luuk · December 1, 2014, 7:14pm

Excel does not need comma separators, this is only true for the English version?

If you have an Italian or Dutch, than a ';' is used as separator.

In fact, the 'List separator' that is defined in the regional setting of Windows is used as separator.

Luuk · December 1, 2014, 7:15pm

Hi,

... so this is an Excel problem because it behaves differently with
respect to its own help...?!!!

Not really.

Excel states, that you must apply the first parameter and you must
not leave it empty. (It´s required.) If you follow this, everything
is fine. And it´s the same thing in LibreOffice.

There is no specification for the behaviour of the function, in case
it´s not used according to the rules.

You can expect Excel and LibreOffice behave the same, if you use
functions according to their definition and the instructions given
in the documentation. You cannot expect identical behaviour, in case
you violate the instructions.

+1

--> Your Problem is not an Excel problem neither a LibreOffice
problem, it´s a users problem.

+1

Cheers,
Stefan

Luuk · December 1, 2014, 7:17pm

I should read the complete thread before posting a reaction...