Q [ Why cant vlookup handle certain characters ]

[KHIKENT]

Hi
Not been using libre office for very long Version 5, windows 10

I use vlookup to return certain values or text based on a Lookup List.
Example: ’ BH30 ’ Returns the Text ‘300 Base Unit 1 Door, 1 Shelf’.
The code value ’ BH30/2+1ID ’ returns #NA, remove the ‘+’ char and it returns a value this works in Excel 2003 to 2013

the formula is constructed using Named Ranges
=IF(ISERROR(TRIM(VLOOKUP($A2;STOCKTEXT;2;0))),"",(TRIM(VLOOKUP($A2;STOCKTEXT;2;0))))
this formula works very well on an unsorted list and to be honest i am not inclined to change either as i have 20 or so works sheets that would require changing and 11 other spreadsheet files that would require the same treatment

PS i am moving from excel 2013 to Libre Office 5 but if cant solved this problem then i will have to rethink my options

Thanks in advance for your help

[PYS]

Hi

You just have to uncheck Tools▸Options▸Calc▸Calculate▸Enable regular expressions in formulas

Explanation: + is one of the regular expressions. If the option is checked… it is used as such.

Note: you can simplify your formula using:

=IFERROR(TRIM(VLOOKUP($A2;STOCKTEXT;2;0));"")

Regards

[KHIKENT]

Hi
Thank you very much that worked a treat .

I use Ubuntu 15.10 at home and tried your answer, the formula worked without unchecking the 'Enable regular expressions in formulae, but when i unchecked the box, for the formula to work i just changed the sort order from false(0) to True(1) just a point of interest

Regards