VLOOKUP for Cost of Goods changes

rainydaze999

New Member
Joined
Jan 7, 2014
Messages
2
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!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

ExcelTactics

Board Regular
Joined
Sep 4, 2013
Messages
146
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
ABC
1Data
2ItemPurchase DatePrice
3123451/4/2014$2.75
42345612/13/2013$1.00
51234512/5/2013$5.00
63456711/7/2013$6.00
71234510/15/2013$3.50

<tbody>
</tbody>
Sheet2

And output:
Excel 2010
FGH
1Output
2ItemSale DatePrice
31234510/30/2013$3.50
41234512/25/2013$5.00
5123451/7/2014$2.75

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

Array Formulas
CellFormula
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

New Member
Joined
Jan 7, 2014
Messages
2
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

Board Regular
Joined
Sep 4, 2013
Messages
146
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,552
Members
417,151
Latest member
ChickenTenderer

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top