How to Retrieve the Latest Unit Cost by Item Code in Excel
Question details
The user needs to find a way to look up the most recent unit cost for specific item codes in a spreadsheet without pulling incorrect prices from different items that happen to share the same date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Pulling up-to-date pricing or inventory costs from a dataset containing a history of different item codes, dates, and unit costs.
- Observed behavior
- Using a lookup formula that relies only on the maximum date returns the wrong unit cost when multiple different item codes have entries on that exact same latest date.
Ensure your dataset contains separate columns for Date (Column A), Item Code (Column B), and Unit Cost (Column C), and verify that all dates are formatted as actual date values rather than text.
Use INDEX, MATCH, and MAXIFS (Recommended Array Formula)
The cleanest and most robust method to retrieve the latest cost. It checks both the item code and its respective maximum date directly within the formula without requiring additional helper columns.
This formula uses the MAXIFS function to determine the latest date specific to the target item code. Then, it evaluates two conditions at once: whether the item code matches and whether the date matches the maximum date found.
Click on the cell where you want the latest unit cost to appear (for example, next to an item code you typed in cell F5).
Type the following formula: =INDEX(C:C, MATCH(1, (B:B=F5)*(A:A=MAXIFS(A:A,B:B,F5)), 0)). Here, Column A is Dates, Column B is Item Codes, Column C is Unit Costs, and F5 is the specific item code you are looking up.
Press Enter. If you are using an older version of Excel that does not support dynamic arrays, you must press Ctrl+Shift+Enter to execute it as an array formula.
Use a Helper Column with Unique Keys
Ideal for older spreadsheet software where complex array formulas might slow down performance or are unsupported. This creates a combined lookup key using the item code and the date.
Use Dynamic Arrays (LET, SORT, and XLOOKUP)
An advanced approach that generates an entirely automated, non-duplicated list of item codes alongside their latest costs in a single run.
Retrieve the Latest Unit Cost Seamlessly with WPS Spreadsheet
WPS Office provides robust native support for advanced array formulas, MAXIFS, and XLOOKUP functions, allowing you to easily look up complex dynamic data and solve your unit cost lookup problems exactly as you would in Microsoft Excel.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the inventory or pricing data.
- 2. Select the destination cell: Click the specific cell where you want to display the retrieved latest unit cost.
- 3. Apply the lookup formula: Enter =INDEX(C:C, MATCH(1, (B:B=F5)*(A:A=MAXIFS(A:A,B:B,F5)), 0)) referencing your actual columns.
- 4. Confirm to calculate: Press Enter to instantly evaluate the multi-condition formula and display the correct unit cost.
Frequently Asked Questions
Why does a standard VLOOKUP return the wrong latest cost?
A standard VLOOKUP only searches for the first match it encounters from the top down. If your data isn't perfectly sorted in descending order by date, VLOOKUP will grab an older cost. Additionally, standard VLOOKUP cannot evaluate multiple criteria (such as matching both the item code and the item's max date) simultaneously.
What should I do if my formula returns an #N/A error despite the data existing?
An #N/A error in an INDEX/MATCH combination usually means an exact match wasn't found. This often happens if your item codes contain hidden trailing spaces, or if your dates are formatted as text. Use the TRIM() function to clean spaces or convert the dates to numerical date formats.
Can I use XLOOKUP instead of INDEX and MATCH for multiple conditions?
Yes. If your spreadsheet supports it, you can use XLOOKUP with boolean logic. The syntax would be =XLOOKUP(1, (B:B=F5)*(A:A=MAXIFS(A:A,B:B,F5)), C:C), which searches for the value '1' (TRUE) where both the item code and maximum date conditions are met.
