Search product price by latest date

hpernaf

New Member
Joined
Jul 1, 2019
Messages
5
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,792
Office Version
365
Platform
Windows
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?
 

Phuoc

Board Regular
Joined
Apr 29, 2016
Messages
144
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,799
something like this?

DateProduct CodeProductAmount paidSold value
30/06/2019​
111Mouse
11​
22​
30/06/2019​
222Keyboard
21​
42​
30/06/2019​
333RAM memory
52​
104​

or post expected result
 
Last edited:

MARZIOTULLIO

Well-known Member
Joined
Aug 22, 2015
Messages
767
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

New Member
Joined
Jul 1, 2019
Messages
5
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,799
To be ok for post #5 here is PowerQuery solution

Code:
[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]
 

Forum statistics

Threads
1,082,139
Messages
5,363,364
Members
400,731
Latest member
Jackserver

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top