calc: vlookup not behaving in one spreadsheet

users
#1

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?

#2

Hi,
I think your issue comes from the changes introduced in Version
5.3. The V5.3 release notes (https://wiki.documentfoundation.org/Releas
eNotes/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
(Eike Rathke (Red Hat, Inc.))

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.See also ReleaseNotes 5.2 for wildcards.
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   1b   2c   3d a 4e   5f   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.

#3

You are relying on VLOOKUP() supporting regular expressions. For this to be so, you must have a tick at Tools | Options... | LibreOffice Calc | Calculate | Enable regular expressions in formulae. It seems that you have this ticked in your June spreadsheet but not in your newer one. Note that, although this appears to be a setting in the application, it is actually saved in each document. With the tick in place, ".*" means zero or more of any character (which is what you mean), whereas with no tick it means just the two characters dot-asterisk.

I trust this helps.

Brian Barker

#4

Thanks. That explains everything.