Forum Discussion
Solved
How to retrieve the latest unit cost
Hello, I am sharing a condensed version of a pricing file that lists from Cols A to C Item Codes and pricing by date. The goal is to retrieve the latest unit cost for a particular Item Code. Here is...
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.
try this:
=LET(sorted,SORT($A$2:.$C$999,,-1),XLOOKUP(F5#,CHOOSECOLS(sorted,2),CHOOSECOLS(sorted,3),,,1))