Function Problems

How to Find the Last Month-End Balance in Excel with XLOOKUP

Khadija Khan Oct 9, 2026 868 views

Question details

Retrieve the account balance for the latest transaction date within a specific month using the XLOOKUP function.

How to Find the Last Month-End Balance in Excel with XLOOKUP

Product: Excel
Device & OS: not provided
Scenario: Calculating month-end financial reports where the last transaction does not always fall on the final calendar day of the month.
Observed behavior: The formula needs to search for the exact end-of-month date and automatically fall back to the most recent preceding transaction date to return the correct balance.

Before you start

Verify that your transaction dates are stored as valid date formats rather than text, and identify the cell containing your target month for the lookup.

Solution 1Recommended

Combine XLOOKUP and EOMONTH to Retrieve the Balance

By utilizing XLOOKUP's match mode set to -1 alongside the EOMONTH function, you can locate the last transaction on or before the month's final day.

The EOMONTH function calculates the absolute final day of a given month. Since financial transactions rarely happen exactly on the last calendar day (e.g., on weekends), an exact match lookup will often fail.

XLOOKUP solves this by allowing a match mode of '-1', which instructs the formula to find an exact match or the next smaller item (the closest preceding date).

Define the target month cell

Assume cell F2 contains a date within the month you want to query (for example, '10/1/2023' for October).

Enter the XLOOKUP formula

Select the cell where you want the balance to appear (e.g., G2) and type: =XLOOKUP(EOMONTH(F2,0), $A$2:$A$8, $D$2:$D$8, , -1)

Understand the formula components

Here, EOMONTH(F2,0) calculates the last day of the month. $A$2:$A$8 is your date column, $D$2:$D$8 is your balance column, and -1 is the exact match or next smaller item parameter.

Apply and execute

Press Enter to execute the formula. The correct ending balance for the last recorded transaction in that month will be displayed.

Combine XLOOKUP and EOMONTH to Retrieve the Balance

Formula Tip: Leaving the fourth argument blank (the comma before the -1) simply means the formula will skip the 'if not found' argument and proceed directly to the match mode setting.

WPS Spreadsheet Solution

Manage Month-End Finances Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas like XLOOKUP and EOMONTH, allowing you to instantly extract correct financial balances with high precision.

1. Open your financial ledger: Launch WPS Spreadsheet and open your document containing the dates and balances.
2. Input the formula: Select the result cell and type =XLOOKUP(EOMONTH(F2,0), $A$2:$A$8, $D$2:$D$8, , -1).
3. Calculate instantly: Press Enter to retrieve the accurate month-end balance without compatibility issues.

100% compatible with Microsoft Excel formulas and functions.Free and lightweight tool for accounting and financial analysis.Seamlessly handles dates, XLOOKUP match modes, and complex datasets.Clean, intuitive interface similar to what you are already used to.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP formula return a #N/A error?

This error occurs if there are no dates in your lookup array (column A) that are equal to or earlier than the end of the selected month. Ensure your dataset includes transactions leading up to that month.

Can I use VLOOKUP instead of XLOOKUP for this calculation?

While possible, VLOOKUP requires your data to be strictly sorted in ascending order and uses an approximate match (TRUE), which is less robust. XLOOKUP is highly recommended because it searches efficiently without requiring sorted data when using exact or next-smaller match modes.

How do I ensure Excel recognizes my transaction dates properly?

Select your date column, right-click, and choose 'Format Cells'. Select a valid 'Date' format. If the dates are currently stored as text, use the 'Text to Columns' feature under the Data tab to convert them into real date values.

Excel & Data Problems