Forum Discussion
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 the link: https://docs.google.com/spreadsheets/d/1wm9Ry7PGqM0qirY6GeKu1F9ZHzR3kFrj/edit?usp=drive_link&ouid=103354753371375324640&rtpof=true&sd=true
Note: the formula in Cells F5:G48 may return N/A because of the link that automatically opens up as Google Sheets.
In Cells F5:G48, I have attempted to retrieve the latest Unit Cost by first bringing in Unique Item Code from the repetitive Item Code in Column B. Unfortunately, the unit cost coming in for most of the items is incorrect. For example, Item Code: ABR00054 should have a latest unit cost from 12/6/2023 of $42.98. However, the xlookup(maxifs) function that I am using is bringing in $4.53.
I thought that the formula shown in Cells G5:G48 would work but something is scrambling the formula logic. Can you please point out if the formula is incompatible with the source data layout as presented in Cols A to C? Please consider highlighting another formula that will be able to provide me with the data that I am looking for. Thank you.
Regards,
Shams.
4 Replies
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})))))
Hi ShamsM​,
Thanks for sharing your question and the spreadsheet very clear and helpful!
You're absolutely right that the formula using XLOOKUP(MAXIFS(...)) can return incorrect results.
The issue is that it matches only the latest date, not ensuring it belongs to the same item code.
So if another item shares that date, it may return the wrong unit cost like ABR00054 returning $4.53 instead of $42.98.To solve this, I created a helper column and used a more precise formula that matches both the item code and the latest date.
Step 1: Add a helper column
In a new column (e.g., Column D), enter:
=B2 & "|" & TEXT(A2,"yyyy-mm-dd")This creates a unique key like ABR00054|2023-12-06.
Step 2: Use this formula to retrieve the latest unit cost
=INDEX(C:C, MATCH(F5 & "|" & TEXT(MAXIFS(A:A,B:B,F5),"yyyy-mm-dd"), D:D, 0))
This ensures the lookup matches both the item code and its latest date.Optional: No Helper Column Version
If you're using Excel 365 or Google Sheets with dynamic arrays, you can use:
=INDEX(C:C, MATCH(1, (B:B=F5)*(A:A=MAXIFS(A:A,B:B,F5)), 0))Working Excel File
I've attached a cleaned-up Excel file with the correct formulas and structure. You can test it directly and see that ABR00054now correctly returns $42.98 from 12/6/2023, and other items like CLN00027 return 755, which is the correct latest value.
Let me know if you'd like help adapting this to Google Sheets or optimizing it for larger datasets.
Best regards,
Olufemitry this:
=LET(sorted,SORT($A$2:.$C$999,,-1),XLOOKUP(F5#,CHOOSECOLS(sorted,2),CHOOSECOLS(sorted,3),,,1))
