Search for price with date criteria

[CESAREX]

Hello everyone,


I need your help with a problem!

I have two spread sheet:


Cost&Price

Sales

When I type the product code in Sales spread sheet I need the Price to appear correctly, taking into account the Date (Sales) and Update_Dat (Cost&Price).

FYI: Concat = product_code;vector_dat

The flag does not matter in this function, I just need to know the active price as explained above. PROCV works when the date of sale coincides with the price update date, but for the others it presents an error.

I've tried several different operations, but I do not have much knowledge of index formula, etc.


If anyone can help, I will be very grateful.

Thanks in advance!

 

[wideboydixon]

Try this:

Book1
	A	B	C	D	E	F	G	H
1	Concat	Update_dat	Vector_dat	Product_code	Product	Cost	Price	Flag
2	143160	03/01/2018	43160	1	Cabernet Sauvignon	$ 95.00	$ 290.00	Hist
3	243160	03/01/2018	43160	2	Malbec	$ 102.00	$ 340.00	Hist
4	343160	03/01/2018	43160	3	Merlot	$ 126.00	$ 390.00	Hist
5	443160	03/01/2018	43160	4	Pinot Noir	$ 80.00	$ 170.00	Hist
6	543160	03/01/2018	43160	5	Chardonnay	$ 120.00	$ 380.00	Hist
7	643160	03/01/2018	43160	6	Tannat	$ 228.00	$ 540.00	Act
8	343174	03/15/2018	43174	3	Merlot	$ 150.00	$ 450.00	Act
9	443179	03/20/2018	43179	4	Pinot Noir	$ 80.00	$ 150.00	Hist
10	443185	03/26/2018	43185	4	Pinot Noir	$ 94.00	$ 190.00	Hist
11	543193	04/03/2018	43193	5	Chardonnay	$ 135.00	$ 390.00	Act
12	243193	04/03/2018	43193	2	Malbec	$ 91.00	$ 340.00	Act
13	143212	04/22/2018	43212	1	Cabernet Sauvignon	$ 95.00	$ 340.00	Act
14	443212	04/22/2018	43212	4	Pinot Noir	$ 80.00	$ 170.00	Hist
15	443218	04/28/2018	43218	4	Pinot Noir	$ 91.00	$ 185.00	Act
Cost&Price

Book1
	A	B	C	D	E	F	G
1	Sale code	Sale period cod	Date	Weekend	Month	Product Code	Price
2	1	1	01/03/2018	Nao	3	1	$ 290.00
3	2	1	01/03/2018	Nao	3	2	$ 340.00
4	3	2	02/03/2018	Nao	3	4	$ 170.00
5	4	4	04/03/2018	Sim	3	5	$ 380.00
6	5	5	05/03/2018	Nao	3	2	$ 340.00
7	6	9	09/03/2018	Nao	3	3	$ 390.00
Sales

Cell Formulas
Range	Formula
G2	{=INDEX('Cost&Price'!$G:$G,MAX(IF('Cost&Price'!$D:$D=$F2,IF('Cost&Price'!$B:$B<=$C2,ROW('Cost&Price'!$G:$G)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[CESAREX]

Hey

Unfortunately the formula does not work for me. I tried even just with the example I gave and it also did not work.

When I using Ctrl + Shift + Enter, Excel displays an error message and does not accept the formula.


Thank you anyway.

 

[wideboydixon]

Strange. That was taken from a working example. Perhaps the tab names are different?

WBD