[Solved] VLOOKUP finds incorrect match

[adventurehouse]

I hope someone can help me, as this has me stumped. I have an antique booth with my wife, and I created a spreadsheet to keep track of sales and inventory. I have a sheet marked as Inventory, and another on another tab marked Sales 2024. My wife is a former banker, but she knows nothing about computers and spreadsheets. So I tried to make things easier, and I've had some errors that I can't figure out.

My Inventory sheet has several columns including SKU, Vendor, Brand, Description, Qty, Retail and Cost.
My Sales Sheet has similar structure with a few additional fields. We enter the SKU number of a sale and VLOOKUP populates the Vendor, Description, Retail and Cost fields. The problem is that VLOOKUP finds the wrong SKU at what appears to be random times in the Inventory and populates the wrong information into the Sales sheet.

For the life of me, I can't figure out why.

If you look at the sample spreadsheet, and go down to the bottom of the Sales 2024, you'll find a SKU # of 45219, 45216, 45217 and the description should be John Carter Graphic Novel, instead it shows Vintage Gold Three Flower Earring Tree, which is SKU #2840.

Could it be that I mix four digit SKU's and five and six digit SKU's? I'm completely perplexed.

[MrProgrammer]

The problem is that VLOOKUP finds the wrong SKU at what appears to be random times in the Inventory and populates the wrong information into the Sales sheet.

[Tutorial] VLOOKUP questions and answers, Q11/A11

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.

[adventurehouse]

It's because I didn't give the last parameter a 0. Thanks.

[DiGro]

Probably caused by the not-sorted SKU's in column A of the sheet Inventory.
The five-digits numbers seem not sorted, whereas the four-digits are.

If you sort the data, based on column A and add ";1" at the end of the expressions in column D, you should be allright.

Not sorting causes to find non-exact matches, whereas in sorted columns only exact matches are found.

Make sure you always use sorted SKU's

antique_booth_small_sample_sorted.ods

(111.78 KiB) Downloaded 45 times

EDITED: sorry, was busy typing. Saw you already found it

[adventurehouse]

This indeed is correct. I made a change so the VLOOKUP is non-sorted, as my wife adds new SKU's all the time, without resorting the spreadsheet.

Thanks for the help.