Forum Discussion
How to retrieve the latest unit cost
Hello ShamsM​
Yes your question makes perfect sense, and you can absolutely adapt the optional formula to work directly with the source Item Code list in Column B without needing a separate unique list in Column F.
If you're using Excel 365 or Google Sheets, this formula works row-by-row to retrieve the latest unit cost for each item code:
=INDEX(C:C, MATCH(1, (B:B=B2)*(A:A=MAXIFS(A:A,B:B,B2)), 0))This formula:
- Finds the latest date for the item code in B2
- Matches both the item code and that latest date
- Returns the correct unit cost from Column C
In older versions of Excel, you may need to enter this as an array formula using Ctrl+Shift+Enter.
Hello Olufemi,
Thank you very much for sharing the new and improved formula. Instead of creating the unique item code list in Column F, am I able to adapt the Optional formula in Column H to point directly to the source Item Code List in Column B? The new formula should be looking at the intersection of the Order Date and Item Code to bring in the latest unit cost.
Hope this makes sense. Thank you.
Shams.
Here is a single cell dynamic formula.
=LET(ItemCode,UNIQUE(B2:B759),REDUCE({"Unique Item Code","Latest Unit Cost"},ItemCode,
LAMBDA(a,x,VSTACK(a,INDEX(SORT(FILTER(A2:C759,B2:B759=x),1,-1),1,{2,3})))))