Vlookup not working where numbers are a mixed format

Discuss the spreadsheet application
Post Reply
deutscheparts
Posts: 17
Joined: Sat Jan 30, 2016 4:49 pm
Location: Herefordshire

Vlookup not working where numbers are a mixed format

Post by deutscheparts »

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
Attachments
OOCalcForumExample.ods
(19.73 KiB) Downloaded 141 times
Office 4.1.1
Vista / Windows 10
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Vlookup not working where numbers are a mixed format

Post by Villeroy »

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
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
deutscheparts
Posts: 17
Joined: Sat Jan 30, 2016 4:49 pm
Location: Herefordshire

Re: Vlookup not working where numbers are a mixed format

Post by deutscheparts »

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
Office 4.1.1
Vista / Windows 10
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Vlookup not working where numbers are a mixed format

Post by Villeroy »

deutscheparts wrote:Thanks, but I get that and vLookup has always (I think) dealt with this without a problem.
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.
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
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Vlookup not working where numbers are a mixed format

Post by Zizi64 »

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.
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.
User avatar
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

Post by JohnSUN-Pensioner »

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
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Vlookup not working where numbers are a mixed format

Post by Villeroy »

=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
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
Post Reply