[Solved] VLOOKUP returns incorrect data

[Sanman]

Hi there brilliant minds,

After some excellent advice from this forum, I managed to get my list making spreadsheet working, extracting data from a second sheet and transferring it to the first sheet using Vlookup.
It worked brilliantly , until it didn't.
I input a part number I wished to have a description filled in, "4587" which is an animal hitching accessory. However the data returned was for value "45870" Sticker sheet.
vlookup added a zero to the end of my search value.
Could someone shed some light onto why this might be happening? and how do I prevent it from adding extra zeros to my search value.
I thought the $ was to keep it all absolute?
=VLOOKUP(A2;$parts.A$2:B$60000; 2; 0) or
=VLOOKUP($A21;$parts.A$2:B$60000; 2; 0)
gave same result.

regards

[Hagar Delest]

Strange, works fine for me with LibreOffice.
When I type 4587 the next cell is ok and it does display the result for 45870 only if I type 45870.
There may be some autocompletion from AOO but that would be a nasty one since both numbers have been typed yet. Thus no rationale to fill in with the longest one.

Calc.png (25.5 KiB) Viewed 501 times

[Sanman]

Thanks for you prompt reply.

Yes, I hope nothing like you mentioned but a simple fix (fingers crossed).

But I also noted that the 5-digit number is SECOND in its look up sequence, assuming it goes from top to bottom.

[Hagar Delest]

It may be my English but I still don't understand your problem. Can you make a screenshot of your issue?
Note that you reference $parts.A$2:B$60000, it should be $parts.A$1:B$60000, else you'll never see the result for the ref. 4584.

[Sanman]

You hit the nail on the head in your previous post. That you didn't have an issue when using LibreOffice.
when I input the search value of "4587" in cell A21 of inventory Sheet, The data that is returned in cell B21 is the data that is attributed to Cell B27942 A "45870", B Sticker Sheet.
I should be getting data attributed to my actual search value of "4587"
Cell A27941 A"4587". B "Animal/Creature accessory".
somehow, the VLOOKUP added a zero to my search value to make my value, "45870", instead of my requested "4587".
OK
Hold up a sec,

I just opened the file I sent as an attachment and the data was returned correctly. However, that attachment only has a data base of 10 items on the "parts" sheet, not the 60000 on my computer.

Still on my computer with the entire data base, the above issue occurs.

I shall send a screen shot.

[robleyd]

What is in B28?

Can you share the version of the file that has the error?

[Sanman]

Hi Robleyd,

I repeated the value of 4587 into B28 again, to prove to myself it was still giving the same result.

The actual file I have is MASSIVE because of the parts sheet with 57694 lines.
it is well over the attachment limit of 128kb.
The parts sheet is merely a download from Rebrickable website called Parts.csv.zip,

If you know of a way to squash it smaller, I can try to get it under the limit.

[Sanman]

OOOKAAAY,

just realised I'm as thick as two short planks.

I neglected to read beyond "sticker sheet' cos its referring to set 133-1 DOH!
Whilst 45870 is referring to sticker sheet 3545-1

I looked up the actual sticker sheet and lo! the part ID
is in actual fact ALSO 4587.
So i accept this is a MY problem and error to somehow figure out and I apologise to VLOOKUP for thinking it failed me.

Im gonna call this SOLVED as it's not really an issue with the app. More an issue with the one driving it.
sorry all and thank you for your input.

[Sanman]

So here is the offending line..A46 with an identical part ID as line A27941.

UGH!

Once again thankyou all for your input.

[Hagar Delest]

With such long lists of references, better make a sanity check first, to ensure there is no duplicates in your column A.

[Sanman]

LOL you are SOOOOOOO right.
Definitely will do that next time this happens.
regards