Description: Hello, the "rechercheV" (in french) function imposes a new option unlike previous versions: "Recherche dans une plage triée" (in French) Here is the function "=RECHERCHEV($ E2; $ L $ 2: $ O $ 147677; 2;)" I added one; with no range at the end for it to work. I have a little anxiety if my old formulas no longer work with this new version Regards Steps to Reproduce: 1.=RECHERCHEV($ E2; $ L $ 2: $ O $ 147; 2) 2. 3. Actual Results: does no research and gives the result of the line where the formula is located Expected Results: the search Reproducible: Always User Profile Reset: No Additional Info: Here is the function "=RECHERCHEV($ E2; $ L $ 2: $ O $ 147; 2;)" I added one; with no range at the end for it to work.
It's not a new option or tell me in which LO version were you. Also, it's an optional option. Could you attach a file where VLOOKUP (RechercheV) is wrong?
My old version was 6.1 (I no longer remember minor versions). My function was this RECHERCHEV($b$2;$f$2:$j$11226;2) In version 6.4 it does not work, I took a long time before realizing that it was necessary to put the last option for it to work. Like this :RECHERCHEV($b$2;$f$2:$j$11226;2;) I cannot send you the file because it is very large and in my over files y have make changes
Created attachment 159617 [details] test file On pc Debian x86-64 with LO Debian package 6.4.2.2 and French UI, I don't reproduce this. See attached file.
For the test, could you rename your LO directory profile (see https://wiki.documentfoundation.org/QA/FirstSteps#Corrupted_user_profile) and give a new try?
Created attachment 159619 [details] capture
Created attachment 159620 [details] My file ods
I try in safe mode it is the same thing. I'm on Mac osx 10.11.6
Indeed, I could reproduce this. I can't explain the difference of behaviour between your file and mine.
Created attachment 159621 [details] little diff
A file with a little diff
It seems to be related to the data type because with numbers it works
Taking a look at https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr1.cxx?r=61d8db6b#7204 7204 void ScInterpreter::CalculateLookup(bool bHLookup) 7205 { ... 7210 // Optional 4th argument to declare whether or not the range is sorted. 7211 bool bSorted = true; 7212 if (nParamCount == 4) 7213 bSorted = GetBool(); 7214 ... 7299 if ( bSorted ) 7300 rEntry.eOp = SC_LESS_EQUAL; So when there's no final ";", it's considered as true. so VLOOKUP($A$2;$C$2:$E$5;2) is equivalent to VLOOKUP($A$2;$C$2:$E$5;2;1) VLOOKUP($A$2;$C$2:$E$5;) is equivalent to VLOOKUP($A$2;$C$2:$E$5;2;false) About difference with numbers, I think it's the same behaviour, if you put this in C10:D11 2 test2 1 test1 then =VLOOKUP(1;C10:D11;2) you got "N/A"
I make many tests and this ok only if the search is a number. The search with a text work only when the col of the text is first. See my file TEST 5
Created attachment 159636 [details] My file
N/A is expected in Test 1 and Test 4 fail since the search is done with first column of data, see https://help.libreoffice.org/7.0/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3153152 Same behaviour for Excel (see https://support.office.com/fr-fr/article/recherchev-recherchev-fonction-0bbc8083-26fe-4963-8ab8-93a18ad188a1)
Yes, I had never read the entire doc. Sorry. Two screenshots for information. One in excel (onedrive) and the other in Libre Office. Regards
Created attachment 159649 [details] In excel
Created attachment 159650 [details] In LO
On pc Debian x86-64 with master sources updated today, I can reproduce this but with French UI only, not with English UI. For devs: I tried to change locally translation/source/fr/formula/messages.po index f29fdff7c76..62adb2f8b69 100644 --- a/source/fr/formula/messages.po +++ b/source/fr/formula/messages.po @@ -2432,7 +2432,7 @@ msgstr "#NUM !" #: formula/inc/core_resource.hrc:2687 msgctxt "RID_STRLIST_FUNCTION_NAMES" msgid "#N/A" -msgstr "#N/D" +msgstr "#N/A" I made a make formula.clean && make sc.clean && make formula && make sc && make postprocess Then I removed local profile but I still got #N/D I don't feel like cleaning the whole build just to test this. I'm stuck here.
The "recherchev" does not work. Worse, it returns false results. Very annoying because we do not necessarily notice. See attached files.
Created attachment 159752 [details] File ods
Created attachment 159753 [details] In LO
Created attachment 159754 [details] In XL
Same thing with "rechercheh"? I don't try
It's expected since G2:M39 isn't sorted by G. Either sort G2:M39 on G column or add extra arg "0".
Yes but I insist a little If I sort on G some results are wrong. See attached file. If I add the option "Unsorted" with 0, it's good. Unfortunately it is disturbing because by default (without option 0) the results are false. When you come from Excel it's annoying because you don't put this option. Indeed Excel does not ask for this option and by default considers unsorted data.
Created attachment 159757 [details] Sorted by G
No bug here, it's described in https://help.libreoffice.org/7.0/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3153152 " If the Sorted parameter is omitted or set to TRUE or one, it is assumed that the data is sorted in ascending order. In this case, if the exact SearchCriterion is not found, the last value that is smaller than the criterion will be returned. " So in your case, sort your data on G column (since it's the key search), then put 0 for last argument. For the rest, indeed it's different from Excel since the last argument hasn't the same meaning.
Too bad this prevents migration from Excel to Calc. Users have a lot of formulas in a lot of files and it is illusory to ask them to modify everything and train them again. Regards
Eike: first I wanted to close this bugtracker then I thought you may have some opinion here as Calc expert. For example, should we duplicate VLOOKUP to have VLOOKUP_MS? (Idem for HLOOKUP I suppose)
There is already a big obstacle to wanting to use Free Software, especially within companies that are looking for lasting solutions and that do not want or cannot invest in training. The fear of IT managers among users is the management of errors. It is already very difficult and very time consuming because users often do not import what. They often have general knowledge and limited training and asking them to check for errors due to a change in formula is a job for Titans. The worst part is, as I wrote, having wrong results because most of them will not change or verify anything. Ideally there should be backward compatibility for Libre Office users and a strictly identical correspondence for M Excel users. The fact that there are not all the functions nor absolutely all the functions is not for me blocking. Because, often, the users only apprehend 30 to 40% of the totality of the functionalities. On the other hand, I repeat myself, it is very important that the existing functions are strictly identical. ---------------------------------------- Il y a déja un gros frein a vouloir utiliser des Logiciels libres surtout au sein des entreprises qui cherchent des solutions pérennes et qui ne veulent pas ou ne peuvent pas investir en formations. La peur des gestionnaires informatiques auprès des utilisateurs c'est la gestions des erreurs. C'est déja très difficile et très consommateur de temps car les utilisateurs font souvent n'imorte quoi. Ils ont souvent une connaissance générale et une formation limité et leurs demander de vérifier s'il n'y a pas d'erreurs du à un changement de formule relève d'un travail de Titans. Le pire c'est, comme je l'ai écrit, d'avoir des résultats erronés car la plupart ne vont rien changer ni vérifier. Idéalement il faudrait qu'il y ai une compatibilité ascendante pour les utilisateurs de Libre Office et une correspondance strictement identique pour les utlisateurs de M Excel. Le fait qu'il n'y pas toutes les fonctionnalités ni absolument toutes les fonctions n'est pas pour moi bloquant. Car, souvent, les utilisateurs n'appréhendent que 30 à 40% de la totalité des fonctionalités. Par contre, je me répète, il est très important que les fonctions existantes soient strictement identiques.
(In reply to Phil from comment #31) > There is already a big obstacle to wanting to use Free Software, especially > within companies that are looking for lasting solutions and that do not want > or cannot invest in training. > The fear of IT managers among users is the management of errors. > It is already very difficult and very time consuming because users often do > not import what. They often have general knowledge and limited training and > asking them to check for errors due to a change in formula is a job for > Titans. > The worst part is, as I wrote, having wrong results because most of them > will not change or verify anything. > Ideally there should be backward compatibility for Libre Office users and a > strictly identical correspondence for M Excel users. > The fact that there are not all the functions nor absolutely all the > functions is not for me blocking. Because, often, the users only apprehend > 30 to 40% of the totality of the functionalities. > On the other hand, I repeat myself, it is very important that the existing > functions are strictly identical. > ... <provocative mode>Why Excel wouldn't copycat Calc?</provocative mode> The results are not the same as Excel ones but we can't say they're wrong since they correspond to LO documentation. There's always fear for IT managers see: - migration cost - macro compatibility (there are always power users in a company who did "great" macros that some services use for which IT dpt isn't even informed) - better integration with tools like (Outlook, Sharepoint, ...) - presence of support (which can be more important than the cost) - users who complain - ... Since most of companies already use Windows, they've got already MS support. So if your users don't care about Open Source philosophy, don't expect to migrate towards LO since there'll always be a good reason to stay with MS Office for users or IT managers.
> <provocative mode>Why Excel wouldn't copycat Calc?</provocative mode> > The results are not the same as Excel ones but we can't say they're wrong > since they correspond to LO documentation. > > There's always fear for IT managers see: > - migration cost > - macro compatibility (there are always power users in a company who did > "great" macros that some services use for which IT dpt isn't even informed) > - better integration with tools like (Outlook, Sharepoint, ...) > - presence of support (which can be more important than the cost) > - users who complain > - ... > > Since most of companies already use Windows, they've got already MS support. > So if your users don't care about Open Source philosophy, don't expect to > migrate towards LO since there'll always be a good reason to stay with MS > Office for users or IT managers. Yes there are precedents, David and Goliath for example ;-) Your analysis sticks to reality. But if migration there is as much as it is not in pain. All this is true for large accounts which will never switch to free software, but, for me, it seems to me that this is not the case for SMEs, SMIs which do not have the same budgets. For example, my daughter is independent and works with very large Anglo-Saxon and French clients. She can not afford to roughly and it must have an excellent compatibility, but would be very happy to significantly reduce its software budget.
(In reply to Phil from comment #33) <offtopic> > ... > Yes there are precedents, David and Goliath for example ;-) There's the Bible and there's the reality. > > Your analysis sticks to reality. > > But if migration there is as much as it is not in pain. > All this is true for large accounts which will never switch to free > software, but, for me, it seems to me that this is not the case for SMEs, > SMIs which do not have the same budgets. > > For example, my daughter is independent and works with very large > Anglo-Saxon and French clients. > She can not afford to roughly and it must have an excellent compatibility, > but would be very happy to significantly reduce its software budget. Even if you're independant, you've got suppliers or at least some clients. When there's a compatibility pb (and there are still a lot for example in layout), you can't afford to ask them to convert files in ODF/PDFs. There are also public agencies which sometimes provide/accept only MS format. Most of the time, small companies must adapt to big companies, not the reverse. Moreover, when taking a look at https://products.office.com/fr-fr/compare-all-microsoft-office-products?activetab=tab%3aprimaryr1, "69€ TTC" / per year is not that expensive. Anyway, we're greatly off the main issue now which is VLOOKUP/HLOOKUP functionality. </offtopic> Finally, I suppose we'll have some pb for xlsx which include VLOOKUP since we obviously expect Excel behaviour in xlxs.
(In reply to Julien Nabet from comment #34) > (In reply to Phil from comment #33) > ... > Finally, I suppose we'll have some pb for xlsx which include VLOOKUP since > we obviously expect Excel behaviour in xlxs. Thank you for your work and good luck.
Nothing to fix or implement here. If you tell VLOOKUP() to do a range lookup by not giving the 4th parameter at all then the lookup range MUST be sorted, otherwise the result will be *arbitrary*. This is the same in Calc and Excel though the result may be different arbitrary due to different search algorithms and *by accident* may be what is expected in one or the other. See also https://help.libreoffice.org/6.4/en-GB/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3153152