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

At 18:46 06/10/2017 -0400, Gary Dale wrote:
> I have two spreadsheets. I created the first back in June. It uses 
> two sheets, ...
> 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))
>
> 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. [...]
> 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.
>
> Does anyone have any ideas on what could be going wrong?

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


--
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