VLOOKUP for Cost of Goods changes

[rainydaze999]

Hello,

I have a VLOOKUP problem which involves tracking price changes for a cost of goods calculation in a spreadsheet used to track sales, revenues, and profits. Each item sold has a unique ID number. I need to track cost data & take price changes into account within the VLOOKUP (or other) function. Sales data & calculations in question are in one tab of the Excel 2013 Workbook. Cost data is in another tab of the same Excel Workbook.

Each purchase I make contains the following information: Item ID#, Item Description, Item Cost, Date of Purchase. Each purchase is represented in a row. What I need to do is to return Item Cost for an Item ID# when the Date of Sale is less than or equal to Date of Purchase. If multiple rows which match Item ID and the Date of Purchase is less than the Date of Sale, the row with the latest Date of Purchase should be returned.

Each sale contains Item ID#, Item Description, Date of Sale.

Can anyone provide advice on the Excel syntax for this function?

Thanks in advance!

 

[jamtay317]

hello and welcome to the form, can you please provide some data?

 

[ExcelTactics]

If your purchase data can be sorted in reverse chronological order (newest orders first), this type of solution should work for you... Example data:
Excel 2010

	A	B	C
1	Data
2	Item	Purchase Date	Price
3	12345	1/4/2014	$2.75
4	23456	12/13/2013	$1.00
5	12345	12/5/2013	$5.00
6	34567	11/7/2013	$6.00
7	12345	10/15/2013	$3.50

<tbody>
</tbody>

Sheet2​

And output:
Excel 2010

	F	G	H
1	Output
2	Item	Sale Date	Price
3	12345	10/30/2013	$3.50
4	12345	12/25/2013	$5.00
5	12345	1/7/2014	$2.75

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula H3 {=INDEX($A$3:$C$7,MATCH(1,($A$3:$A$7=F3)*($B$3:$B$7<=G3),0),3)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This solution has been adapted from How to VLOOKUP with Multiple Criteria Using INDEX and MATCH at ExcelTactics.com.

 

[rainydaze999]

Thanks much for the help. I think I am understanding how this function works in general, but how I have set it up is returning incorrect values. This is how my sheet is currently set up:

Sheet 1 (Sales & Calculations)
Date - Item Number - Item Name - Quantity - Revenue - Cost


Sheet 2 (Purchasing)
Date - Item Number - Item Name - Cost Per Unit

I am performing the MATCH/INDEX in Sheet 1.

Unfortunately I can't paste a screenshot of the spreadsheets here - I can only speak in general terms. (I am a pharmacy - customer privacy is extremely important).

The syntax I'm using is as follows (I am doing the Ctrl-Shft-Enter to get the {} brackets once I finish typing the function):

=H2*(INDEX(Purchasing!$A$2:$J$529,MATCH(1,(Purchasing!$A$2:$A$529=G2)*(Purchasing!$B$2:$B$529<=C2),0),10))

As I understand it, the first "1" tells Excel that I want to perform a TRUE Boolean match on the two functions that follow; The "0" that follows the functions tells Excel not to look in a specific row, the "10" tells Excel to return the value from relative column 10. I think this is where I'm getting tripped up. Can you help me clarify my understanding?

 

[ExcelTactics]

Hi RainyDaze,

I'm glad that the guide has sent you in the right direction! You are mostly correct on the formula inputs:

You are correct that the first "1" looks for a TRUE on the following conditionals.
You are correct that the "10" returns the 10th relative column, which in your example would be Column J.

The "0", however, is a term that tells MATCH to look for an exact match only, rather than returning the closest match greater or less than the requested term. Since we are looking for a hard TRUE, we need it to be exact, so zero is the appropriate input here.

I assume that in your example, H2 holds the quantity that you want to multiply by the unit price to get the total cost...

Troubleshooting
I believe that your problem is in your conditional terms. In my example, the Item Number was in Column A, so we wanted to look at an exact match (Item Number in the current row = Item Number we are looking for). My Purchase Date was in Column B, so we wanted to look at the price that was closest without going over the sale date (Date in current row is <= Date we are looking for).

In your data headers, it appears that your columns are switched, so your conditional terms (= and <=) will need to be switched as well. Also make sure that the Purchase Date is sorted in reverse chronological order.

Hope this helps!