Morning all, hope you're all well during the **** time.
I have a slight issue that I'm struggling with, in a nutshell Vlookup is not working as expected in the following way:
I have Tab A which has an exported list of SKU's from a stock control system. These SKUs are a mixture, some are pure numbers and some are a mixture of numbers and letters.
Calc is putting a ' at the beginning of cells that are pure numbers and is saying that these are numbers formatted as text, this is fine and we've been using this for years with no problems.
However, in tab 2 is a column with SKUs from another system which are also a mixture of numbers and numbers mixed with letters.
I am ok ish with Vlookup and have been using it on OOCalc for years but lately it will NOT recognise a Vlookup of a number in Tab2 if the EXACT same number in Tab 1 is formatted as text.
I am using =vlookup(a2;tab1.A$2;B$999;2;1) which has always worked but it simply returns #n/a now.
The odd thing is that if I copy the number from Tab2, and paste it into Tab1, it matches up immediately.
I wonder if a setting has changed in the latest release of OOCalc but I've checked everything and can't find anything obvious. Sample file attached.
The number I've used as an example in the attached sheet is 211611483 which you can see is on both tabs
Cheers
Vlookup not working where numbers are a mixed format
-
- Posts: 17
- Joined: Sat Jan 30, 2016 4:49 pm
- Location: Herefordshire
Vlookup not working where numbers are a mixed format
- Attachments
-
- OOCalcForumExample.ods
- (19.73 KiB) Downloaded 141 times
Office 4.1.1
Vista / Windows 10
Vista / Windows 10
Re: Vlookup not working where numbers are a mixed format
The text "123" and the number 123 belong to different categories of values. This has nothing to do with formatting.
[Tutorial] Ten concepts that every Calc user should know
[Tutorial] Ten concepts that every Calc user should know
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 17
- Joined: Sat Jan 30, 2016 4:49 pm
- Location: Herefordshire
Re: Vlookup not working where numbers are a mixed format
Thanks, but I get that and vLookup has always (I think) dealt with this without a problem.
I've just found this page which I've tried and it seems to work, but I have a large file that I need to test this with.
https://exceljet.net/formula/vlookup-wi ... s-and-text
I've just found this page which I've tried and it seems to work, but I have a large file that I need to test this with.
https://exceljet.net/formula/vlookup-wi ... s-and-text
Office 4.1.1
Vista / Windows 10
Vista / Windows 10
Re: Vlookup not working where numbers are a mixed format
No. You have to enter the lookup value as text. Everything would be easier if you could handle database data with a database application. A spreadsheet is not a database application.deutscheparts wrote:Thanks, but I get that and vLookup has always (I think) dealt with this without a problem.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Vlookup not working where numbers are a mixed format
The VALUE of the STRING
'211611483
in the column "A" on "Tab1" of your sample file is zero.
The VALUE of the VLOOKUP is 211611483 in the sheet Tab2.
These values never will match.
'211611483
in the column "A" on "Tab1" of your sample file is zero.
The VALUE of the VLOOKUP is 211611483 in the sheet Tab2.
These values never will match.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: Vlookup not working where numbers are a mixed format
You know for sure that SKU is always a text - this has been so for many years. No problems! Make VLOOKUP () search for the exactly text
Code: Select all
=VLOOKUP(TRIM(A1);Tab1.A4:D28;2;1)
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: Vlookup not working where numbers are a mixed format
=vlookup(a2;tab1.A$2;B$999;2;1) is wrong anyway. Enter the text '12345 (with leading apostrophe) into Tab2.A1. The formula result in B1 is "SEAL" although 12345 does not match any entry on Tab1.
[Tutorial] VLOOKUP questions and answers
[Tutorial] VLOOKUP questions and answers
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice