Find the latest cost of an item based on sales date

devtr

New Member
Joined
Jan 23, 2021
Messages
4
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please help.
Two worksheets: item_cost and item_sales.
item_cost has items and cost with dates.

MarketItems.xlsx
ABCD
1cost_dateitem_namecategorycost
21/1/2021Body armorSoda$ 2.00
31/1/2021dasani 20 ozWater$ 1.25
41/1/2021Dunkin donuts iced coffeeIced tea or coffee$ 1.50
51/1/2021Gold peak teaIced tea or coffee$ 2.25
61/1/2021Minute maid 20ozJuice$ 1.50
71/1/2021Monster 16ozEnergy drinks$ 2.25
81/1/2021Peace teaIced tea or coffee$ 1.25
91/1/2021PoweradeSoda$ 2.25
101/1/2021Coca-cola 20ozSoda$ 1.25
111/1/2021Sprite 20ozSoda$ 1.25
121/1/2021Fanta orange 20ozSoda$ 1.25
131/1/2021Diet Coke 20ozSoda$ 1.25
141/5/2021Body armorSoda$ 3.00
item_cost
Cell Formulas
RangeFormula
C2:C14C2=IFERROR(INDEX(tblItems[category], MATCH([@[item_name]], tblItems[item_name],0),1), "")
Named Ranges
NameRefers ToCells
rngItem=tblItems[item_name]C2:C14
Cells with Data Validation
CellAllowCriteria
B2:B14List=rngItem


item_sales has sales data per date.

MarketItems.xlsx
ABCDEFGHI
1sales_dateitem_namecategorycostpricequantitytotal_costtotal_salesgross_profit
21/1/2021Body armorSoda$ 3.00$ 3.005$ 15.00$ 15.00$ -
31/1/2021dasani 20 ozWater$ 1.25$ 2.002$ 2.50$ 4.00$ 1.50
41/1/2021Dunkin donuts iced coffeeIced tea or coffee$ 1.50$ 2.501$ 1.50$ 2.50$ 1.00
51/1/2021Gold peak teaIced tea or coffee$ 2.25$ 3.002$ 4.50$ 6.00$ 1.50
61/1/2021Minute maid 20ozJuice$ 1.50$ 2.502$ 3.00$ 5.00$ 2.00
71/1/2021Monster 16ozEnergy drinks$ 2.25$ 3.005$ 11.25$ 15.00$ 3.75
81/1/2021Peace teaIced tea or coffee$ 1.25$ 2.001$ 1.25$ 2.00$ 0.75
91/1/2021PoweradeSoda$ 2.25$ 3.001$ 2.25$ 3.00$ 0.75
101/1/2021Coca-cola 20ozSoda$ 1.25$ 2.005$ 6.25$ 10.00$ 3.75
111/1/2021Sprite 20ozSoda$ 1.25$ 2.002$ 2.50$ 4.00$ 1.50
121/1/2021Fanta orange 20ozSoda$ 1.25$ 2.002$ 2.50$ 4.00$ 1.50
131/1/2021Diet Coke 20ozSoda$ 1.25$ 2.002$ 2.50$ 4.00$ 1.50
item_sales
Cell Formulas
RangeFormula
C2:C13C2=IFERROR(INDEX(tblItems[category], MATCH([@[item_name]], tblItems[item_name],0),1), "")
D2:D13D2=IFERROR(LOOKUP(2,1/(tblItemCost[item_name]=[@[item_name]]),tblItemCost[cost]), 0)
E2:E13E2=IFERROR(LOOKUP(2,1/(tblItemPrice[item_name]=[@[item_name]]),tblItemPrice[price]), 0)
G2:G13G2=[@quantity]*[@cost]
H2:H13H2=[@quantity]*[@price]
I2:I13I2=[@[total_sales]]-[@[total_cost]]
Named Ranges
NameRefers ToCells
rngItem=tblItems[item_name]C2:C13
Cells with Data Validation
CellAllowCriteria
B2:B13List=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!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
Vlookup can not handle?
 

devtr

New Member
Joined
Jan 23, 2021
Messages
4
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
S4_ReferencingRanges_Start.xlsm
ABCD
1cost_dateitem_namecategorycost
201/01/2021Body armor12
301/01/2021dasani 20 oz11.25
401/01/2021Dunkin donuts iced coffee11.5
501/01/2021Gold peak tea12.25
601/01/2021Minute maid 20oz11.5
701/01/2021Monster 16oz12.25
801/01/2021Peace tea11.25
901/01/2021Powerade12.25
1001/01/2021Coca-cola 20oz11.25
1101/06/2021Body armor11.25
1201/01/2021Fanta orange 20oz11.25
1301/01/2021Diet Coke 20oz11.25
1401/05/2021Body armor13
15
16sales_dateitem_namecategorycost
1701/01/2021Body armor12
1801/01/2021dasani 20 oz11.25
1901/01/2021Dunkin donuts iced coffee11.5
2001/01/2021Gold peak tea12.25
2101/01/2021Minute maid 20oz11.5
2201/01/2021Monster 16oz12.25
2301/01/2021Peace tea11.25
2401/01/2021Powerade12.25
2501/01/2021Coca-cola 20oz11.25
2601/01/2021Sprite 20oz1#NUM!
2701/01/2021Fanta orange 20oz11.25
2801/01/2021Diet Coke 20oz11.25
Sheet4
Cell Formulas
RangeFormula
D17:D28D17=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!
 
Solution

devtr

New Member
Joined
Jan 23, 2021
Messages
4
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

=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?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,664
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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))
 
  • Like
Reactions: alz

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,130,022
Messages
5,639,596
Members
417,100
Latest member
Simon123456789

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top