Search product price by latest date

[hpernaf]

Hi everyone!


I am new here in the forum and I looked for some solution to my problem and I did not find anything similar, so I decided to create a new post.


I have a workbook with two worksheets "STOCK" and "SALES REGISTRATION".


In Stock, I give all of my products that I buy to sell, entering the date of purchase, the product code, the description, the amount paid and the amount that will be sold. Note that the same product may change price according to the date of entry.

In "Sales Registration" I enter all sales of my products by the date of Sale, Product code, product, amount paid and value sold.

In this case, in columns D and E (Paid Value and Sold Value) I need to load their values according to the most current price in my stock.


That is, whenever I make a new sale, Excel should look in my inventory to see what is the latest price for that product according to my date of sale.


I even managed to do this using the INDEX and MATCH functions as the matrix functions. But since my record sales will be large, the spreadsheet turned out to be extremely heavy and slow to pull values.


My question is: Is there a way to do this without the use of dot matrix functions? I tried something like vlookup and match but I also could not.


I'm leaving the worksheet attached. If anyone can help me, I'll be very grateful.




https://drive.google.com/file/d/1sMqrL_07RSajItXG_wVYVaOOcYr2crjc/view?usp=sharing

 

[steve the fish]

Index match would be no good here, nor vlookup, since i presume the most current price is at the bottom of the list. Is that correct?

 

[hpernaf]

Index match would be no good here, nor vlookup, since i presume the most current price is at the bottom of the list. Is that correct?

In general, yes. But the most current price may also be in the middle of the list.

 

[Phuoc]

Try this formula in D3:

=LOOKUP(1,0/('STOCK '!$A$3:$A$11<=$A3)/('STOCK '!$B$3:$B$11=$B3),'STOCK '!D$3:D$11)

Copy across and down.

 

[sandy666]

something like this?

Date	Product Code	Product	Amount paid	Sold value
30/06/2019​	111	Mouse	11​	22​
30/06/2019​	222	Keyboard	21​	42​
30/06/2019​	333	RAM memory	52​	104​

or post expected result

 

[MARZIOTULLIO]

SALES REGISRATION

D3=IF($A3<>"",IF('SALES REGISTRATION'!$A3=MAX(IF('STOCK '!$B$3:$B$11='SALES REGISTRATION'!$B3,IF('STOCK '!$C$3:$C$11='SALES REGISTRATION'!$C3,'STOCK '!$A$3:$A$11))),SUMIFS('STOCK '!D$3:D$11,'STOCK '!$A$3:$A$11,'SALES REGISTRATION'!$A3,'STOCK '!$B$3:$B$11,'SALES REGISTRATION'!$B3,'STOCK '!$C$3:$C$11,'SALES REGISTRATION'!$C3),""),"")

Control + shift +enter

copy across and down

 

[hpernaf]

SALES REGISRATION

D3=IF($A3<>"",IF('SALES REGISTRATION'!$A3=MAX(IF('STOCK '!$B$3:$B$11='SALES REGISTRATION'!$B3,IF('STOCK '!$C$3:$C$11='SALES REGISTRATION'!$C3,'STOCK '!$A$3:$A$11))),SUMIFS('STOCK '!D$3:D$11,'STOCK '!$A$3:$A$11,'SALES REGISTRATION'!$A3,'STOCK '!$B$3:$B$11,'SALES REGISTRATION'!$B3,'STOCK '!$C$3:$C$11,'SALES REGISTRATION'!$C3),""),"")

Control + shift +enter

copy across and down

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">That got me.
Thanks for your help!
</body>

 

[sandy666]

To be ok for post #5 here is PowerQuery solution

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Purchase date", type date}, {"Product Code", type text}, {"Product", type text}, {"Amount paid", type number}, {"Sold value", Int64.Type}}),
    Group = Table.Group(Type, {"Product Code", "Product"}, {{"Date", each List.Max([Purchase date]), type date}, {"Count", each _, type table}}),
    LastAmount = Table.AddColumn(Group, "Amount paid", each List.Last([Count][Amount paid])),
    LastSold = Table.AddColumn(LastAmount, "Sold value", each List.Last([Count][Sold value])),
    ROC = Table.SelectColumns(LastSold,{"Date", "Product Code", "Product", "Amount paid", "Sold value"})
in
    ROC[/SIZE]