How to Get Last Entry By Month And Year in Excel

Why This Task Matters in Excel

In real-world workbooks, information rarely arrives in perfect chronological order. Sales transactions, manufacturing quality checks, help-desk tickets, medical readings, or stock deliveries may all be entered by different users at different times, sometimes even back-dated. When the month closes and management asks “What was the last sale logged in March 2024?” or “Which technician performed the final inspection in September?”, you need a reliable, repeatable way to pull the most recent record that belongs to a specific month and year.

A single worksheet can hold thousands (or millions, if you use the 64-bit version) of timestamped rows. Manually scrolling to the bottom of a filtered range might work in a small list, but it becomes error-prone and painfully slow as data grows. Automating the retrieval of the last entry lets you:

• Produce month-end dashboards without manual intervention
• Verify that daily production logs actually include an entry for every calendar day
• Feed the result into additional calculations such as inventory valuation or sales commission
• Build audit trails that capture the last action taken within each fiscal period

Because the requirement is so common across industries—finance, inventory, human resources, project management—Excel provides multiple techniques to achieve it. Which one you pick depends on your version of Excel, whether you have access to Excel 365’s dynamic array functions, and how comfortable you feel mixing legacy functions such as LOOKUP with newer LET and FILTER combinations.

Failing to master this skill can lead to month-end numbers that are off by one record, incorrect KPIs, and hours lost in manual double-checking. Learning to get the last entry by month and year also strengthens your understanding of conditional filtering, array manipulation, and error handling—skills that transfer directly to pivot tables, Power Query, and VBA solutions.

Best Excel Approach

For users on Microsoft 365 or Excel 2021, the most concise and transparent solution is a dynamic array formula that filters the data first and then picks the last item from the filtered list. The formula below pulls the last sales amount from [B2:B1000] where the transaction date in [A2:A1000] falls in the target month and year located in cells [E2] and [F2].

=LET(
    dates,   A2:A1000,
    values,  B2:B1000,
    mm,      E2,          /* target month as a number 1-12 */
    yyyy,    F2,          /* target year, 4-digit */
    picked,  FILTER(values,(MONTH(dates)=mm)*(YEAR(dates)=yyyy)),
    INDEX(picked,ROWS(picked))
)

Why this approach is best:

• LET assigns names to portions of the calculation, improving readability and speeding calc time because Excel evaluates each named block only once.
• FILTER creates an in-memory sub-array that contains only records matching both the month and the year.
• INDEX … ROWS picks the last row of that filtered sub-array. Because ROWS returns the count of filtered items, INDEX automatically targets the final one.
• The entire formula spills zero or one value; no helper columns are required.

When to use this versus alternatives:

• Choose this method if you have Excel 365 or 2021, especially when you expect the same logic to be used repeatedly in different parts of the workbook.
• If you must remain compatible with Excel 2019 or earlier, use the legacy LOOKUP technique described later.
• For massive tables (above 100,000 rows) where performance is critical, consider a PivotTable or Power Query step instead of an on-sheet formula.

Alternative legacy method (works in any version from Excel 2007 onward):

=LOOKUP(2,1/((MONTH($A$2:$A$1000)=E2)*(YEAR($A$2:$A$1000)=F2)),$B$2:$B$1000)

LOOKUP exploits the fact that dividing 1 by a TRUE/FALSE array yields 1 or #DIV/0!. LOOKUP searches for the numeric 2, which will never be found, so it lands on the last numeric 1 in the array—precisely the last record that matches both month and year.

Parameters and Inputs

• Date range (required): A contiguous column that holds actual Excel dates such as [A2:A1000]. These must be true serial numbers, not text strings pretending to be dates.
• Value range (required): The column from which you want to retrieve the final item, for instance sales figures in [B2:B1000] or employee names in [C2:C1000].
• Target month (required): Either a numeric value 1-12 or a cell that already contains that number. Text strings like \"Mar\" must be converted with MONTH(DATEVALUE(\"Mar 1\")).
• Target year (required): A 4-digit year, e.g., 2024.
• Optional parameters: You can extend the FILTER criteria to handle additional conditions such as product line or region by multiplying further logical tests, e.g., (Region=\"North\").
• Data preparation: Ensure there are no blank cells inside the date range. If blanks exist, wrap the MONTH and YEAR calls in IF(ISNUMBER()) to avoid #VALUE! errors.
• Validation rules: The month must be 1-12, the year should be a valid integer between 1900 and 9999, and date cells must contain numbers.
• Edge cases: If no row matches the given month and year, FILTER returns a #CALC! error. Wrap the entire statement in IFERROR to trap it gracefully and return \"No match\".

Step-by-Step Examples

Example 1: Basic Scenario

Imagine a simple sales log in [A1:B11] with headers “Date” and “Amount.”

Goal: Return the last sales amount recorded in April 2024.

Step 1 – Store target month/year:
E\2 = 4 (April), F\2 = 2024.

Step 2 – Enter the LET formula in cell G2:

=LET(
    dates,   A3:A11,
    values,  B3:B11,
    mm,      E2,
    yyyy,    F2,
    picked,  FILTER(values,(MONTH(dates)=mm)*(YEAR(dates)=yyyy)),
    INDEX(picked,ROWS(picked))
)

Step 3 – Press Enter. G2 returns 525.

Why it works: FILTER pulls [445,277,525] (the three amounts in April). ROWS(picked) equals 3, so INDEX returns the third item, 525.

Common variations:

• If you need the date instead of the amount, swap values with dates in the LET definition.
• If you’d like “No sales” when no match exists, wrap the outermost clause in IFERROR(…,\"No sales\").

Troubleshooting tip: If G2 shows #NAME?, confirm you are on Excel 365 or 2021; older Excel versions do not recognize LET or FILTER.

Example 2: Real-World Application

Scenario: A chain of stores keeps daily closing inventory in columns:

Management wants to know the last recorded inventory for SKU-A in Store L-101 for March 2024, because that figure feeds directly into the month-end replenishment calculation.

Step 1 – Define dynamic named ranges (optional but neat):

• rngDate → =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
• rngStore → similar approach for store column
• rngSKU → item column
• rngUnits → units column

Step 2 – In G4 type the criteria:
Month = 3, Year = 2024, Store = \"L-101\", SKU = \"SKU-A\".

Step 3 – Enter the multi-criteria dynamic array formula in H4:

=LET(
    d, rngDate,
    s, rngStore,
    k, rngSKU,
    u, rngUnits,
    last, FILTER(u,
          (MONTH(d)=G4) *
          (YEAR(d)=H4) *
          (s=I4) *
          (k=J4)),
    INDEX(last,ROWS(last))
)

The result, 287, spills into H4. That value can be used in an automatic safety stock formula further down the sheet.

Integration with other features: Because the LET formula spills a single value, you can reference it directly in a PivotTable calculated field or pass it into a Power BI dataflow that reads the Excel sheet.

Performance consideration: Even in a 100,000-row table, dynamic arrays calculate almost instantaneously because Excel’s calculation engine is highly optimized for vectorized operations. If the sheet is still sluggish, store the data as an actual Excel Table and limit the ranges to [#All] inside LET so that volatility stays low.

Example 3: Advanced Technique

Edge case: Your company now tracks two shifts per day, each with a timestamp down to minutes. You must fetch the very last shift entry inside each month-year, but also want to spill the results for every month of an entire year without rewriting the formula 12 times.

Step 1 – Convert the raw range into a structured Table named tblShifts with columns [ShiftDate] and [UnitsProduced].

Step 2 – Create a list of months in K2:K13, with actual dates representing the first day of each month (use `=DATE(`2024,ROW(A1),1)).

Step 3 – Array-enter the following formula in L2 and let it spill downward:

=LET(
  d, tblShifts[ShiftDate],
  u, tblShifts[UnitsProduced],
  mm, MONTH(K2#),     /* entire spill array of months */
  yy, YEAR(K2#),
  filt, MAP(mm,yy, LAMBDA(m,y,
          FILTER(u,(MONTH(d)=m)*(YEAR(d)=y))
        )),
  INDEX(filt, ROWS(filt))
)

Explanation:

• MAP iterates through each pair of month/year in K2# (the spill of 12 items), returning a nested array where each element holds all matching shift units.
• INDEX(filt, ROWS(filt)) operates on each nested array inside MAP, effectively grabbing its last element.
• The formula spills a column of 12 last-entry numbers that stay perfectly synced with the list in column K.

Professional tips:

• Use the formula in a Named Formula called LastUnitsByMonth; then chart it directly without cluttering the sheet.
• Explicitly declare variables inside LET for d and u because that reference is reusable across the entire spill, improving performance.

Tips and Best Practices

1. Store date and value ranges as Excel Tables so formulas use structured references like tblSales[Date]; this automatically expands as new rows are appended.
2. Wrap your main formula in IFERROR(result,\"No matching entry\") to prevent ugly error messages in dashboards.
3. Keep month and year criteria in separate helper cells; this lets slicers or data-validation drop-downs drive the formula for instant what-if analysis.
4. Use dynamic arrays for clarity: FILTER + INDEX + ROWS is often faster to understand and maintain than a single line of nested legacy functions.
5. Document your logic with comments inside the LET block; a quick Alt+Enter after each comma makes the formula self-explanatory for colleagues.
6. If you anticipate dates typed as text, preprocess the column with VALUE or Power Query to convert them to real serial dates before relying on MONTH and YEAR.

Common Mistakes to Avoid

• Using text dates: MONTH(\"03-31-2024\") returns #VALUE! in some regional settings. Convert to real dates or use DATEVALUE first.
• Mixing columns of unequal length: If your date column has 1,000 rows but the value column has 998, INDEX will return incorrect items. Make ranges symmetrical.
• Forgetting to anchor ranges in legacy LOOKUP formulas. Omitting absolute references causes ranges to shift as you copy formulas, leading to erratic results.
• Neglecting the year filter. If you only test MONTH(date)=3 and your data spans multiple years, you will erroneously pick March 2023 entries while querying 2024.
• Omitting IFERROR. When there’s no match—say, a new fiscal year with no records yet—FILTER throws #CALC!. Trap it to keep reports clean.

Alternative Methods

When to use which:

• PivotTables excel at quick summaries for managers who just need a printed report.
• Power Query is unbeatable on multi-million-row CSV imports.
• AGGREGATE is a good fallback if FILTER is unavailable and you dislike the LOOKUP trick.
• Dynamic arrays remain the most elegant balance for day-to-day interactive workbooks.

FAQ

When should I use this approach?

Use it whenever you need the most recent piece of information within a defined calendar bucket—month-end balances, last maintenance record in a quarter, or the final daily reading inside each billing cycle. Dynamic arrays make the workbook self-updating as new records arrive.

Can this work across multiple sheets?

Yes. Point the date and value ranges to another worksheet like Sheet2!A2:A1000. Make sure both ranges stay the same size. For many sheets, consolidate the data in Power Query or a master Table first; otherwise you’ll build fragile 3-D references.

What are the limitations?

FILTER requires contiguous ranges and may spill #CALC! when no result exists. Legacy LOOKUP cannot return spill arrays, only single cells. All formula methods rely on correct date serials; mixed data types will break them.

How do I handle errors?

Wrap the entire formula in IFERROR or use the newer IFNA for #N/A-specific trapping. Example:

=IFERROR( LET(… formula …), "No entry found" )

You can also build a conditional format that turns the cell red when it displays the fallback text, drawing attention to missing data.

Does this work in older Excel versions?

The dynamic array method requires Excel 365 or 2021. Excel 2019 and earlier must use LOOKUP, AGGREGATE, or a PivotTable. All formulas shown are compatible down to Excel 2007 when you choose the legacy variant.

What about performance with large datasets?

Dynamic arrays handle tens of thousands of rows effortlessly because the calculation engine processes arrays in memory. For hundreds of thousands or more, store the data as an Excel Table to limit recalculation only to the used range, or push the heavy lifting into Power Query / Power Pivot where columnar storage is optimized.

Conclusion

Getting the last entry by month and year is a foundational skill that unlocks accurate month-end reporting, audit compliance, and dynamic dashboards. Whether you choose modern dynamic arrays or a tried-and-true LOOKUP workaround, mastering this pattern trains you to think in terms of conditional filters and array manipulations—cornerstones of advanced Excel proficiency. Experiment with the formulas in your own data, graduate to Table-based structured references, and soon these once-manual lookups will refresh themselves every time a colleague presses Save. Use what you’ve learned here as a springboard into more sophisticated time-series analysis, rolling averages, and automated KPI tracking.