# VLOOKUP Not Consistant

#### zaHawk

##### New Member
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.

#### Attachments

• snap3743.png
14.3 KB · Views: 7

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
=INDEX(\$N\$1:\$N\$12300,MATCH(F5,\$M\$1:\$M\$12300,0))

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.

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

Replies
11
Views
496
Replies
4
Views
387
Replies
6
Views
518
Replies
7
Views
564
Replies
0
Views
210

1,217,757
Messages
6,138,435
Members
450,137
Latest member
HANHAN

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

### Which adblocker are you using?

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

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