# Search product price by latest date

#### hpernaf

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

#### steve the fish

##### Well-known Member
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

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

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

Last edited:

#### MARZIOTULLIO

##### Well-known Member
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
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.
</body>

#### sandy666

##### Well-known Member
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]``````

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

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