Re: [libreoffice-users] calc: vlookup not behaving in one spreadsheet

Thanks. That explains everything.

On 06/10/17 08:23 PM, Remy Gauthier wrote:
> Hi,
>
> I think your issue comes from the changes introduced in Version 
> 5.3. The V5.3 release notes 
> (https://wiki.documentfoundation.org/ReleaseNotes/5.3#Calc) say the 
> following:
>
>
>       Option settings
>
>   * In new installations the default setting for new documents is now
>     *Enable wildcards in formulas* instead of regular expressions.
>     tdf#88581
>     <https://bugs.documentfoundation.org/show_bug.cgi?id=88581> (Eike
>     Rathke (Red Hat, Inc.))
>       o This is for better interoperability with other spreadsheet
>         applications and a better experience for users who are not
>         familiar with regular expressions. Wildcards are more
>         widespread and the large set of regular expression
>         metacharacters often makes queries too complicated for casual
>         users.
>       o See also ReleaseNotes 5.2 for wildcards
>         
> <https://wiki.documentfoundation.org/ReleaseNotes/5.2#Support_wildcards_to_be_compatible_with_XLS.2FXLSX_and_with_ODF_1.2>.
>
>
> If you go to Tools -> Options -> Calc -> Calculate and enable "Enable 
> regular expressions in formulas", it should work (at least, it does 
> for me). You can also change your ".*" to just "*" to make it work 
> (but that will prevent the other spreadsheet from perhaps working 
> correctly).
>
> If you want to be more robust, you could use a dedicated worksheet 
> that has a small array of lets say 1 columns and 6 rows. In column 1 
> (starting at A1), you place a, b, c, d a, e, f and in column 2 
> (starting at B1), you place 1, 2, 3, 4, 5, 6, like this:
>
> a 1
> b 2
> c 3
> d a 4
> e 5
> f 6
>
> Create a named cell (for instance "Wildcard" without the quotes) that 
> contains this formula:
>
> =IF(ISERROR(VLOOKUP("d"&"*",A1:B6,2,0)),".*","*")
>
> You can then use something like this as your search element the lookup 
> function: UPPER(TRIM(C2)&" "&TRIM(E2)&Wildcard
>
> I tried to use an inline matrix instead of A1:B6 but I did not get any 
> good results. If anyone has an idea, I would be interested in knowing 
> how to do it (it would require only one cell instead of 7 (6 for the 
> array, 1 for the result)).
>
> I hope this helps.
>
> Rémy Gauthier.
>
> Le vendredi 06 octobre 2017 à 18:46 -0400, Gary Dale a écrit :
> > I have two spreadsheets. I created the first back in June. It uses two
> > sheets, one for registration information (Entrants) which I download
> > from a site that takes registrations for races, and the other (Results)
> > I download from a site that the timer posts results to. The second sheet
> > has the standing information while the first has all the entrant
> > information. I can relate the two by the entrant's name, which is 3
> > columns in the first sheet and 1 (uppercase) column in the second using
> > the formula:
> >
> > =N(VLOOKUP(UPPER(TRIM(C2)&" "&TRIM(E2)&".*"),Standings.C$2:J$37,8,0))
> >
> > where C & E are the first and last name columns on Entrants, Standings.C
> > contains the combines first and last names and column J is the number I
> > want (standing within a division by age and gender). A failed lookup
> > would mean the person registered but didn't complete the event.
> >
> > This works in the spreadsheet I created in June but doesn't work in the
> > spreadsheet I just created. I traced this down to the &".*" part of the
> > search term. This was intended to handle trailing spaces or other
> > extraneous characters after the person's name. The spreadsheet I created
> > in June didn't have any but the one I just created did. However removing
> > the trailing spaces from the Standings names didn't help. The formula
> > simply didn't work.
> >
> > Replacing the &".*" with a simple &" " to assume that the names always
> > have a trailing space did work, as did removing the trailing spaces and
> > leaving out the &" ". However the original formula works on the June
> > spreadsheet with or without trailing spaces in the names.
> >
> > I'm working with both spreadsheets on the same computer using the same
> > copy of LibreOffice Calc (5.4.1.2.0+ on a Debian Buster AMD64 system). I
> > can have them open side by side to demonstrate the problem (it's also
> > how I copied and pasted the formula between the two - multiple times -
> > to ensure that it wasn't a typing error).
> >
> > Does anyone have any ideas on what could be going wrong?


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted