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

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