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

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,613
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
50,613
Office Version
  1. 365
Platform
  1. Windows
Looks as though you need to change the commas to semi-colons.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,206
Messages
5,570,889
Members
412,345
Latest member
avelraza
Top