Auto Populate My Cost Per Order on Orders Workbook Based on SKU from Price List Workbook

Hi,

I have searched through both major excel forum sites and could not find an exact solution to my question and have been trying to solve this for quite a while now so any help is very much appreciated.

I have two workbooks, one is the list of items I sold “orders” and the other is my cost of goods “price list”. I would like to have my SKUs in my “orders” workbook automatically grab my cost from the “price list” workbook based on the SKU number and paste its price on the same row (in a blank cell) on the column next to it. My end goal is to find out my COGS for the month and have a breakdown of it by SKU.

- I am able to export all of my orders for the month in a workbook. The workbook lists the SKU number in one column, and the quantity sold in the next.

- I have my “price list” in a separate workbook. The price list contains only my SKU number and my cost for the item each in individual columns.

- I would like to know how to have excel scan for the SKU in the “price list” workbook and bring in/input my cost of that SKU/item into the “orders” workbook. If there was more than 1 quantity of an item sold than it should calculate how much I sold based on the “quantity” column in that same sheet.

- Note: I do not want the “orders” workbook to refer to a SKU’s price based on a SKU cell’s location because the SKU cell location may change. Would it be possible for the “orders” workbook to search for the SKU and bring in the cost solely based on the SKU number matching one another from the “price list” workbook?

- Note: I am using Excel For Mac 2011

- I have attached a sample of both files to maybe help get a better understanding of what I’m trying to do.

Apologies ahead of time for my extremely limited knowledge of Excel. I hope this all made sense! A step-by-step explanation on how to get this done would be great.

Thank you!

You can do a VLOOKUP across workbooks. The workbook with the data to look up doesn't even have to be open. However, it must remain in the same location with the same name once the formula is established. The other trick is knowing how big to make the range to look up. Here the range is A11:B34. If you add items to the list you will have to change the formula.

=VLOOKUP(A3,'C:\Users\dflak\Temp\[Price List.xls]Inventory List'!$A$11:$B$34,2,FALSE)

If the price list were in the same book with the orders, then you could assign a named dynamic range to it that will grow or shrink with the data. You can do this across workbooks like so: =VLOOKUP(A2,'Price List.xls'!Price_List,2,FALSE)
where Price_List is defined as: Price_List =OFFSET('Inventory List'!$A$11,0,0,COUNTA('Inventory List'!$A:$A)-4,2)
However, if you are using a named dynamic range, the workbook containing the range must be open in the same instance of Excel.

A final question. You have 2011 which means you could use XLSX format. Is there a reason for providing XLS format? Do you have the option of downloading the data as CSV files? You can import CSV files into an XLSX file and do all your work there.

I believe that the #REF is coming from the table_array argument, '[Price List.xls]Inventory List'!$A$11:$A$34, to VLOOKUP. You are only referencing one column, A, however the result of the col_index_no argument, COLUMN('[Price List.xls]Inventory List'!$B$11:$B$34), is looking for values in a 2nd column. Try modifying the formula to read:

Formula:

Please Login or Register  to view this content.

OR

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

Try pasting the following formula into C2, then double click the fill handle (lower right corner) to have the formula automatically copy down column C:

Formula:

Please Login or Register  to view this content.

You'll notice that in C12 the value is 2.5 meaning that the price of item GKWC-324-16 is $1.25 and two were purchased.
Let me know if you have any questions.

You're Welcome, thank you for the feedback and marking the thread as 'Solved'. I hope that you have a blessed day.

Modify the formula in post #6 so that it reads:

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.