VLOOK UP Problem

I am trying to do a Vlook up of costs using the "Margin INV file" and trying to match the prices in the "View all items code flle". The problem I am running into is that the item codes are mixture of numbers and letters, so the vlook doesn't really work properly. I have been unseccessfully trying to figure this out for more than an hour. Can someone please shed some light no how to do this.

Thanks

Not sure exactly what you're trying to do, but my guess is you need to fut the ",FALSE" argument into the formula to make it look for exact matches and ignore the order of your data.

I took a look at your file and am still uncertain what you want to do here. You do not have any actual prices in the View Items by Ad Code file, just a column where you intend to put them so what are you matching?. V Lookup sould not have an issue due to the letters and numbers though.

From what you showed me I took a guess that you want to put the v lookup of the cost in column D of the View Items file based on item number in column A with your data range in the Margin INV Cost file.

The formula I used is VLOOKUP(A2, '[Margin_INV_Costs(1).xlsx]Sheet1'!$A:$C,3,0)

Then in price I just multipled the value in cost by the Sum of Order_QTY or C*D.

In the instances where the formula returns #N/A it seems that the item numbers cannot be found in the Margin INV Costs. I tried to clear all spaces from the Margin INV table as well as the View Items table and do a find on the item number. In instances when a cost was returned the item number was found and in instances when it was not, the item number could not be found.