Page 1 of 1

[Solved] VLOOKUP returns incorrect data

Posted: Thu May 01, 2025 11:45 am
by 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

Re: VLOOKUP search value returning incorrect data

Posted: Thu May 01, 2025 11:54 am
by 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
Calc.png (25.5 KiB) Viewed 665 times

Re: VLOOKUP search value returning incorrect data

Posted: Thu May 01, 2025 11:59 am
by 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.

Re: VLOOKUP search value returning incorrect data

Posted: Thu May 01, 2025 12:47 pm
by 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.

Re: VLOOKUP search value returning incorrect data

Posted: Thu May 01, 2025 1:19 pm
by 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.

Re: VLOOKUP search value returning incorrect data

Posted: Thu May 01, 2025 1:24 pm
by robleyd
What is in B28?

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

Re: VLOOKUP search value returning incorrect data

Posted: Thu May 01, 2025 1:59 pm
by 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.

Re: VLOOKUP search value returning incorrect data

Posted: Thu May 01, 2025 2:09 pm
by 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.

[SOLVED]Re: VLOOKUP search value returning incorrect data

Posted: Thu May 01, 2025 2:19 pm
by Sanman
So here is the offending line..A46 with an identical part ID as line A27941.

UGH!

Once again thankyou all for your input.

Re: [Solved] VLOOKUP returns incorrect data

Posted: Thu May 01, 2025 2:52 pm
by Hagar Delest
With such long lists of references, better make a sanity check first, to ensure there is no duplicates in your column A.

Re: [Solved] VLOOKUP returns incorrect data

Posted: Tue May 06, 2025 1:13 pm
by Sanman
LOL you are SOOOOOOO right.
Definitely will do that next time this happens.
regards