Match by product ID number, then by date

[anto80mtl]

Hello

I am looking for a formula that contains both an exact match criteria to locate a product ID number, and then a "<=" match to find the last date the product's price was updated. The formula would allow me to link pricing information from one table to another on separate worksheets.


Table 1: "Price Tracker" where I enter a date, product ID and price. Every time there is a new price for the same product, I start a new row with a new date, the same product ID and an updated price.

Table 2: "Stock Tracker" where I enter a date, product ID and quantity. I would like to use a formula that will search Table 1 to match the product ID and then return the price that is/was in effect based on the date when the stock entry was made.


=INDEX($D$2:$D$17,MATCH(MIN(ABS(IF($A$2:$A$17=G2,$C$2:$C$17,999999)-I2)),ABS(IF($A$2:$A$17=G2,$C$2:$C$17,999999)-I2),0))

This array formula above that I found in this thread partially works, but it does not match the right price if I have more than 1 price update in Table 1.

I can supply a sample excel file to help illustrate what I'd like to accomplish.

Thanks in advance!

[niladri2005]

Please attach a sample excel file

[anto80mtl]

Here is the sample workbook with the 2 tables on separate worksheets. The formula should evaluate the "Date", "Type" and "Product ID" from the Price Tracker worksheet and return the value to the Stock Tracker worksheet in the column highlighted in yellow. Feel free to let me know if you need more clarification.

Thank you again for your help!

[niladri2005]

it is good one. I know it can be done. Give me some time please. I will be back soon

[icestationzbra]

may be this in 'Stock Tracker'!D2, drag-filled down:

Please Login or Register  to view this content.

[anto80mtl]

Wow thanks icestationzbra, you're formula is exactly what I need, well almost!

The only thing left to include a reference for in the formula is: to only include entries (i.e. rows) in the query that have a Type set to "Cost". In this case, the values that will appear in the Stock Tracker worksheet will be costs from the Price Tracker worksheet, thus ignoring entries with Type "Price".

Thanks again for your help!

[anto80mtl]

Any ideas on how to get the formula to consider only rows with Type "Cost"?
Thanks

[anto80mtl]

Thank you icestationzbra, your Excel skills have really helped me in this situation!