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: 3

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,989
Office Version
  1. 365
Platform
  1. Windows
How about
=INDEX($N$1:$N$12300,MATCH(F5,$M$1:$M$12300,0))
 

zaHawk

New Member
Joined
Apr 21, 2011
Messages
41
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,989
Office Version
  1. 365
Platform
  1. Windows
Looks as though you need to change the commas to semi-colons.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,498
Messages
5,832,055
Members
430,109
Latest member
govivek

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
Top