[Solved] VLOOKUP problem working with cell formatting

Discuss the spreadsheet application

[Solved] VLOOKUP problem working with cell formatting

Postby brubble51a » Sun May 02, 2021 8:44 pm

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
Attachments
help - UPLOAD.ods
(26.81 KiB) Downloaded 32 times
Last edited by Hagar Delest on Sun May 02, 2021 11:08 pm, edited 1 time in total.
Reason: tagged solved.
Office ver : 7.1.2.2
OP: Windows 10
brubble51a
 
Posts: 3
Joined: Sun May 02, 2021 8:32 pm

Re: Vlookup problem working with cell formatting - possibly

Postby MrProgrammer » Sun May 02, 2021 9:42 pm

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

brubble51a wrote: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

brubble51a wrote: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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 4008
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

SOLVED

Postby brubble51a » Sun May 02, 2021 10:36 pm

Much Thanks Mr. Programmer !!!
That solved the problem I was having.
Office ver : 7.1.2.2
OP: Windows 10
brubble51a
 
Posts: 3
Joined: Sun May 02, 2021 8:32 pm

Re: [Solved] VLOOKUP problem working with cell formatting

Postby brubble51a » Sun May 02, 2021 11:41 pm

Can Mr. Programmer contact me through email?
Office ver : 7.1.2.2
OP: Windows 10
brubble51a
 
Posts: 3
Joined: Sun May 02, 2021 8:32 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests