Created attachment 190389 [details] example with data When I paste Tab separated columns with some order information I use text import dialog and tell it to treat column with order numbers as text (since some companies use preceding zeros, and normal paste drops them). However, this causes the VLookup to fail to find data. It is caused by apostrophe (or single quote mark, char 39) inserted in front of order number, but this apostrophe is invisible in formula bar under cell formatting set by text import dialog. Only after cloning formatting from cell unaffected by text import dialog can I see this apostrophe. This makes determining why VLookup fails quite frustrating. Now I don't know if any of this should be considered a wrong behavior, but I think that VLookup should be enhanced to handle data regardless of that preceding apostrophe, since it is there only for formatting purposes.
Created attachment 190390 [details] Text file with tab separated values that served as basis for spreadsheet
Not a bug. VLOOKUP() is defined to lookup _matching_ values. A numeric value does not match a text value. Also note that a leading ' apostrophe you might see in the Input Line or while editing a cell is not part of the cell content and does not influence a lookup. It indicates that the input could be interpreted as a number if it was entered without the apostrophe in a cell formatted as numeric, and with apostrophe will be entered as text content. Editing such content means it was already entered as text. See also https://help.libreoffice.org/7.6/en-GB/text/scalc/guide/text_numbers.html?&DbPAR=CALC and https://help.libreoffice.org/7.6/en-GB/text/scalc/guide/integer_leading_zero.html?&DbPAR=CALC Your sample document does not contain such scenario though, because the cell range A1:A8 is formatted as Text, not numeric.
I set it as an enhancement, not a bug, so I would like to have a discussion about how it is a problem for a user and if it can be enhanced. As a user I have little ability to tell, why my vlookup formula does not work with data that looks like numbers (you can right adjust text cells). Text cells don't really differ visually from numeric cells if the content is adjusted (and a user have to be conscious about difference between text cells and numeric cells). Formula output also tells me nothing about why it fails. It is even hard to quickly find the reason with the help of google.