VLOOKUP Not Consistant

zaHawk

New Member
Joined
Apr 21, 2011
Messages
41
Hi

We have a single worksheet where we have captured a suppliers price list and all relevant data, that we will need to eventually save to a CSV file so we can import it into a new system package that we will be doing our sales from.

It has take 1 person months/years to capture most of the info and creating codes for over 10,000 (tip of the iceberg) products ...etc.
Then when that list was done, I could finally capture the Latest Prices from said supplier (large range of Fasteners/bolts.nuts/screws etc).
Needless to say.. this was another several weeks mammoth task getting the right prices to the right products.

Now comes a new problem. An old file was dug up with the Suppliers Stock Code & the WEIGHT of each product (for shipping costs).
We need to assign each weight into the previously completed table, without having to do line for line o_O

The good thing is both have the Suppliers Stock Code.. so VLOOKUP would be the perfect solution.. but it is not consistent through 10,000 lines.
I copied the 2 new columns (Suppliers Stock Code and the Weight) onto the same TAB as the original to ease the VLOOKUP.

For some reason is starts off fine but lower down the rows it becomes incorrect. At least 40% is incorrect.
Sometimes with batches of continuous lines with the same weight value.

Formulas that I have tried are :
=VLOOKUP(F5;$M$1:$N$12300;2)
=VLOOKUP(F5;$M:$N;2)

snap3742.png


The Supplier Stock Codes in Column "M" are from the long list - and in this sample do not correspond, as they are out of view much further down.
The Weights in Column "i" are Correct in this case.. as I said.. it starts off well... but lower much further down... many batches correct & many wrong too.

Here are some more samples on whats gone wrong...
snap3744.png


Could part of the problem be that part codes are duplicated .. with 1-4 extra characters ?
I did try find Exact match formulas but was unsuccessful. Here is the sample as well.. for above errors.

snap3745.png


Well... that is the crux of my problem. I have a feeling of also the "*" playing wild card with the formula.
How would I come around that ?

I looked into INDEX & MATCH ... separate and together .. but seems mostly for Tables.

Then I thought XLOOKUP would solve my problem, but found out still in Beta... or selected Office 365 Users.

Can you help me with an easier way/solution... even if its a Macro ?

Much Appreciated.
 

Attachments

  • snap3743.png
    snap3743.png
    14.3 KB · Views: 8

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
How about
=INDEX($N$1:$N$12300,MATCH(F5,$M$1:$M$12300,0))
 
Upvote 0
Thank You Fluff ... I tried it.. but gives me an error.

snap3748.png


However... I do have good news.. after being such a nitwit.
I needed it to be an exact match.. and left out the important criteria for it
=VLOOKUP(F5;$M$1:$N$12300;2;FALSE) ?‍♂️

I will revert back here to see your Index+Match solution as well.

THANK YOU.
 
Upvote 0
Looks as though you need to change the commas to semi-colons.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top