VLookup Help & Guidance

[Wildie16]

Hi Guys, I am new to the forum as I have a little help that I need and although I am ok at Excel, this is past my expertise.

Essentially, it is to support my new e-commerce website. We receive a CSV file from our supplier, but we are not selling all their product lines, so on a Google Doc we have a list of around 4000 SKU's, which will get uploaded into our magento powered e-commerce site.

The problem I have, is that the stock levels and the pricing can change daily - and I want an Excel formula/vlookup/macro, to look for the SKU in the CSV feed from the supplier, and update the Stock and Price columns for that SKU in my Excel file automatically?

What is the best way to do this? Or is there a way to do it?

I appreciate any help!

(I did see a VLookup tutorial online, but didn't match what I wanted to do really, so came here for help!)

[Special-K]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[Wildie16]

Hi - thanks for the reply.

Please find an example file attached.

Sheet 1 is the 'Master Price List' in effect.
Sheet 2 is how our feed comes from our supplier.

Many of the columns in sheet 2 are not values that change, so they are a constant. What does change is price and stock, and that is what I need to import into my website on a daily/weekly basis.

I have named the columns the same in the Master Price List, as they come over in the feed from the supplier. Can Excel look for the Part number in Sheet 2 and then update the relevant Price and Stock boxes?

For example:

Cell A2 in Sheet Master is CO5330-WH. Can Excel find that part number in Cell F17 on the CSV Feed sheet (by itself, I don't want to tell it where that part number is as with 4000 product lines, I cannot do that for all.) - and then take the Cost Price (E17) and the Stock (C17) and put them into the relevant cells in the 'Master' sheet (Cell B2 for price, and C2 for Stock)

Please advise

[63falcondude]

Try these two formulas copied down the columns:

B2 =INDEX('CSV Feed'!E:E,MATCH(A2,'CSV Feed'!F:F,0))
C2 =INDEX('CSV Feed'!C:C,MATCH(A2,'CSV Feed'!F:F,0))

[Wildie16]

Thanks for those, but they do not work. It comes up with Err:508

Any ideas?

[AliGW]

Your profile data is somewhat ambiguous - which spreadsheet software are you using?

[Wildie16]

I am using OpenOffice Calc

[AliGW]

Then this is not an Excel question, which is probably why you are getting the error message! I've moved your thread to the other platforms section of the forum.

[Wildie16]

Thankyou!

Sorry, I thought (or I was told) that it works the same as Excel, it was just pre-installed on the laptops we bought

[63falcondude]

Sorry, I thought that this was for Excel.

I am not familiar with OpenOffice Calc so I will not be able to help here.

[AliGW]

Some things may work the same as Excel (basic formulae), but you were in the wrong forum section for OpenOffice queries.