# VLOOKUP for Cost of Goods changes

#### rainydaze999

##### New Member
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?

### 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

#### jamtay317

##### Well-known Member
hello and welcome to the form, can you please provide some data?

#### ExcelTactics

##### Board Regular
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

</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)}

</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
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

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):

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
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!

Replies
14
Views
882
Replies
3
Views
107
Replies
1
Views
80
Replies
3
Views
172
Replies
3
Views
225

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.

### Which adblocker are you using?

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

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