Forum Discussion

ShamsM's avatar
ShamsM
Copper Contributor
Oct 16, 2025
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...
excel
Formulas and Functions
  • OlufemiO's avatar
    OlufemiO
    Oct 18, 2025

    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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 18, 2025

Slightly modified m_tarler​ formula to return everything in one run

=LET(
   headers,  {"Unique Item Code","Latest Unit Cost"},
   data,     DROP($A:.$C, 1),
   sorted,   SORT(data,,-1),
   codesRaw, CHOOSECOLS(sorted,2),
   codes,    SORT(UNIQUE( codesRaw )),
   VSTACK( headers,
     HSTACK( codes,
             XLOOKUP(codes,CHOOSECOLS(sorted,2),CHOOSECOLS(sorted,3),,,1)
     )
   )
)

Resources