[Solved] VLOOKUP problem working with cell formatting

[brubble51a]

Hello,

I cannot find any reason for the vLookup function to work on cells formatted as Text but with characters such as - or 1/2 etc.
Could use some advice please.

spreadsheet attached.

Thanks

[MrProgrammer]

Hi, and welcome to the forum. Thank you for the attachment.

cells formatted as Text

Cell formatting and cell values are independent in Calc. Cells formatted as numbers can contain text values. Cells formatted as text can contain numeric values. Your cells are not formatted as text. Format → Cells shows that the category is General. Some of the cells have numeric values, like B2 on the first sheet. Some contain text like B3, the cell below it. Use View → Value Highlighting to see the difference.
1. Types of data in Ten concepts that every Calc user should know

I cannot find any reason for the vLookup function to …

I presume you are asking why VLOOKUP returns #N/A. This is because the lookup value is not found in your range $Sheet2.$A$1:$C$1686. Let's check one: =EXACT('Price List'.B3;Sheet2.A2). Oops, that's FALSE though they look identical (01610-1/2 and 01610-1/2).
[Tutorial] VLOOKUP questions and answers, especially Q15, Q16. Q17

Check the lengths: =LEN('Price List'.B3)=LEN(Sheet2.A2). That's FALSE too. The problem is that you have unexpected characters in Sheet2. =DEC2HEX(UNICODE('Price List'.B3);4) is 0030 as expected but =DEC2HEX(UNICODE(Sheet2.A2);4) is 200E. Cells A2:A21 begin with invisible character U+200E (Left-to-Right Mark), not U+0030 (zero). Use Edit → Find&Replace → More options, select Regular Expressions, Search for \u200E, Replace with «nothing», Replace All. I recommend unchecking Regular expressions before clicking Close.

You should set all of the part number cells to use text formatting and then convert any numeric values in them to text. First set Format → Cells → Category → Text (or preferably use a style). You accomplish numeric to text conversion with [Tutorial] Text to Columns. In the Text Import dialog, set the column type to Text, not Standard. These part numbers are codes and should be stored as text. You won't be doing any arithmetic with part numbers, like taking square roots. Always store codes as text.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[brubble51a]

Much Thanks Mr. Programmer !!!
That solved the problem I was having.

[brubble51a]

Can Mr. Programmer contact me through email?