VLOOKUP Not Consistant

[zaHawk]

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

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)

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...

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.

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.

 

[Fluff]

How about
=INDEX($N$1:$N$12300,MATCH(F5,$M$1:$M$12300,0))

 

[zaHawk]

Thank You Fluff ... I tried it.. but gives me an error.

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]

Looks as though you need to change the commas to semi-colons.