Weirdness when comparing two strings (possibly encoding?)

[dengar81]

Hi there,

I have a file with two strings that are the same (or are they?) and I wish to vlookup on them. Somehow it doesn’t work.
An example can be found here: LibreOfficeStrangeExample.ods - Google Drive

As you can see in the file, if you compare the cells “=A1= B1” the result is “TRUE”.
But if you do a vlookup the value cannot be found.

Why is that? How can that be? What is the workaround so I can get it to function?

Thanks!

[mariosv]

Hi @dengar81, the issue is that VLOOKUP() use regular expressions and the | is part of them, you can:

• Disable regular expressions in Menu/Tools/Options/LibreOffice Calc/Calculate - Enable regular expressions in formulas.
• Or if you need use them, making a little trick with the value to look for, adding \ before | what forces to search for it as literal value.
  E1: =VLOOKUP(SUBSTITUTE(C2;"|";"\|");A$2:B$16;2;0)

In the help there is a nice explanation on how to use regular expressions

[dengar81]

Thank you very much! I wasn’t thinking about regular expressions, this explains it perfectly!