Find the latest cost of an item based on sales date

[devtr]

Please help.
Two worksheets: item_cost and item_sales.
item_cost has items and cost with dates.

MarketItems.xlsx
	A	B	C	D
1	cost_date	item_name	category	cost
2	1/1/2021	Body armor	Soda	$ 2.00
3	1/1/2021	dasani 20 oz	Water	$ 1.25
4	1/1/2021	Dunkin donuts iced coffee	Iced tea or coffee	$ 1.50
5	1/1/2021	Gold peak tea	Iced tea or coffee	$ 2.25
6	1/1/2021	Minute maid 20oz	Juice	$ 1.50
7	1/1/2021	Monster 16oz	Energy drinks	$ 2.25
8	1/1/2021	Peace tea	Iced tea or coffee	$ 1.25
9	1/1/2021	Powerade	Soda	$ 2.25
10	1/1/2021	Coca-cola 20oz	Soda	$ 1.25
11	1/1/2021	Sprite 20oz	Soda	$ 1.25
12	1/1/2021	Fanta orange 20oz	Soda	$ 1.25
13	1/1/2021	Diet Coke 20oz	Soda	$ 1.25
14	1/5/2021	Body armor	Soda	$ 3.00
item_cost

Cell Formulas
Range	Formula
C2:C14	C2	=IFERROR(INDEX(tblItems[category], MATCH([@[item_name]], tblItems[item_name],0),1), "")

Named Ranges
Name	Refers To	Cells
rngItem	=tblItems[item_name]	C2:C14

Cells with Data Validation
Cell	Allow	Criteria
B2:B14	List	=rngItem

item_sales has sales data per date.

MarketItems.xlsx
	A	B	C	D	E	F	G	H	I
1	sales_date	item_name	category	cost	price	quantity	total_cost	total_sales	gross_profit
2	1/1/2021	Body armor	Soda	$ 3.00	$ 3.00	5	$ 15.00	$ 15.00	$ -
3	1/1/2021	dasani 20 oz	Water	$ 1.25	$ 2.00	2	$ 2.50	$ 4.00	$ 1.50
4	1/1/2021	Dunkin donuts iced coffee	Iced tea or coffee	$ 1.50	$ 2.50	1	$ 1.50	$ 2.50	$ 1.00
5	1/1/2021	Gold peak tea	Iced tea or coffee	$ 2.25	$ 3.00	2	$ 4.50	$ 6.00	$ 1.50
6	1/1/2021	Minute maid 20oz	Juice	$ 1.50	$ 2.50	2	$ 3.00	$ 5.00	$ 2.00
7	1/1/2021	Monster 16oz	Energy drinks	$ 2.25	$ 3.00	5	$ 11.25	$ 15.00	$ 3.75
8	1/1/2021	Peace tea	Iced tea or coffee	$ 1.25	$ 2.00	1	$ 1.25	$ 2.00	$ 0.75
9	1/1/2021	Powerade	Soda	$ 2.25	$ 3.00	1	$ 2.25	$ 3.00	$ 0.75
10	1/1/2021	Coca-cola 20oz	Soda	$ 1.25	$ 2.00	5	$ 6.25	$ 10.00	$ 3.75
11	1/1/2021	Sprite 20oz	Soda	$ 1.25	$ 2.00	2	$ 2.50	$ 4.00	$ 1.50
12	1/1/2021	Fanta orange 20oz	Soda	$ 1.25	$ 2.00	2	$ 2.50	$ 4.00	$ 1.50
13	1/1/2021	Diet Coke 20oz	Soda	$ 1.25	$ 2.00	2	$ 2.50	$ 4.00	$ 1.50
item_sales

Cell Formulas
Range	Formula
C2:C13	C2	=IFERROR(INDEX(tblItems[category], MATCH([@[item_name]], tblItems[item_name],0),1), "")
D2:D13	D2	=IFERROR(LOOKUP(2,1/(tblItemCost[item_name]=[@[item_name]]),tblItemCost[cost]), 0)
E2:E13	E2	=IFERROR(LOOKUP(2,1/(tblItemPrice[item_name]=[@[item_name]]),tblItemPrice[price]), 0)
G2:G13	G2	=[@quantity]*[@cost]
H2:H13	H2	=[@quantity]*[@price]
I2:I13	I2	=[@[total_sales]]-[@[total_cost]]

Named Ranges
Name	Refers To	Cells
rngItem	=tblItems[item_name]	C2:C13

Cells with Data Validation
Cell	Allow	Criteria
B2:B13	List	=rngItem

To get the cost from the item_cost worksheet, I used lookup as:
=IFERROR(LOOKUP(2,1/(tblItemCost[item_name]=[@[item_name]]),tblItemCost[cost]), 0)
Which brings the latest cost from item_cost.
I want to get the cost based on item_sales's date for the item. Cost_date has to be the latest date but it cannot be greater then sales_date for the given item. (Less then or equal to is oaky)
For example, if sales_date is 01/01/2021 and item_name is Body armor, cost will be $2. Which has the 01/01/2021 for cost_date.
Using the above Lookup formula, it brings the cost of $3 from 01/05/2021.
Thank you in advance!

 

[alz]

Vlookup can not handle?

 

[devtr]

Vlookup can not handle?

I tried. But don't know how to apply cost_date less then or equal to sales_date along with item_name. Thanks.

 

[alz]

S4_ReferencingRanges_Start.xlsm
	A	B	C	D
1	cost_date	item_name	category	cost
2	01/01/2021	Body armor	1	2
3	01/01/2021	dasani 20 oz	1	1.25
4	01/01/2021	Dunkin donuts iced coffee	1	1.5
5	01/01/2021	Gold peak tea	1	2.25
6	01/01/2021	Minute maid 20oz	1	1.5
7	01/01/2021	Monster 16oz	1	2.25
8	01/01/2021	Peace tea	1	1.25
9	01/01/2021	Powerade	1	2.25
10	01/01/2021	Coca-cola 20oz	1	1.25
11	01/06/2021	Body armor	1	1.25
12	01/01/2021	Fanta orange 20oz	1	1.25
13	01/01/2021	Diet Coke 20oz	1	1.25
14	01/05/2021	Body armor	1	3
15
16	sales_date	item_name	category	cost
17	01/01/2021	Body armor	1	2
18	01/01/2021	dasani 20 oz	1	1.25
19	01/01/2021	Dunkin donuts iced coffee	1	1.5
20	01/01/2021	Gold peak tea	1	2.25
21	01/01/2021	Minute maid 20oz	1	1.5
22	01/01/2021	Monster 16oz	1	2.25
23	01/01/2021	Peace tea	1	1.25
24	01/01/2021	Powerade	1	2.25
25	01/01/2021	Coca-cola 20oz	1	1.25
26	01/01/2021	Sprite 20oz	1	#NUM!
27	01/01/2021	Fanta orange 20oz	1	1.25
28	01/01/2021	Diet Coke 20oz	1	1.25
Sheet4

Cell Formulas
Range	Formula
D17:D28	D17	=INDEX(tblItemCost[cost],AGGREGATE(14,6,IF((tblItemCost[cost_date]<=A17)*(tblItemCost[item_name]=B17),ROW(tblItemCost[cost])-ROW(tblItemCost[[#Headers],[cost]])),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Try this!

 

[devtr]

=INDEX(tblItemCost[cost],AGGREGATE(14,6,IF((tblItemCost[cost_date]<=A17)*(tblItemCost[item_name]=B17),ROW(tblItemCost[cost])-ROW(tblItemCost[[#Headers],[cost]])),1))

What is 14 and 6 after AGGREGATE?

 

[devtr]

What is 14 and 6 after AGGREGATE?

Thanks. It's working. Appreciate your time.

 

[Dave Patton]

check Excel's help for Aggregate

try the following without Array Enter

=INDEX(TblItemCost[cost],AGGREGATE(14,6,MATCH(1,1/((TblItemCost[cost_date]<=A17)*(TblItemCost[item_name]=B17)),0),1))

 

[alz]

Thanks. It's working. Appreciate your time.

It is part of aggregate function. Thanks for the feedback!

Here is the shorter version. No Need Ctrl+shift +enter

=INDEX(tblItemCost[cost],AGGREGATE(14,6,((tblItemCost[cost_date]<=A18)*(tblItemCost[item_name]=B18)*(ROW(tblItemCost[cost])-ROW(tblItemCost[[#Headers],[cost]]))),1))