How to retrieve the latest unit cost

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,
Olufemi