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
[Solved] VLOOKUP problem working with cell formatting
-
- Posts: 3
- Joined: Sun May 02, 2021 8:32 pm
[Solved] VLOOKUP problem working with cell formatting
- Attachments
-
- help - UPLOAD.ods
- (26.81 KiB) Downloaded 100 times
Last edited by Hagar Delest on Sun May 02, 2021 11:08 pm, edited 1 time in total.
Reason: tagged solved.
Reason: tagged solved.
Office ver : 7.1.2.2
OP: Windows 10
OP: Windows 10
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Vlookup problem working with cell formatting - possibly
Hi, and welcome to the forum. Thank you for the attachment.
1. Types of data in Ten concepts that every Calc user should know
[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.
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.brubble51a wrote:cells formatted as Text
1. Types of data in Ten concepts that every Calc user should know
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).brubble51a wrote:I cannot find any reason for the vLookup function to …
[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, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
-
- Posts: 3
- Joined: Sun May 02, 2021 8:32 pm
SOLVED
Much Thanks Mr. Programmer !!!
That solved the problem I was having.
That solved the problem I was having.
Office ver : 7.1.2.2
OP: Windows 10
OP: Windows 10
-
- Posts: 3
- Joined: Sun May 02, 2021 8:32 pm
Re: [Solved] VLOOKUP problem working with cell formatting
Can Mr. Programmer contact me through email?
Office ver : 7.1.2.2
OP: Windows 10
OP: Windows 10