Function Problems

How to Return the Current Month's Sales in Excel (Using INDIRECT and Alternatives)

Rana Garcia Oct 21, 2026 868 views

Question details

The user needs a dynamic Excel formula to automatically retrieve the latest monthly sales for a specific region as new month columns are progressively added.

How to Dynamically Return the Current Month's Sales in Excel

Product: Excel 365
Device & OS: not provided
Scenario: Setting up a dynamic sales report where entering a country name automatically pulls its most recent monthly sales data from a continuously updating table.
Observed behavior: The user initially attempted to use INDIRECT to solve the problem, but faced errors and is looking for the correct syntax or a more efficient alternative.

Before you start

Ensure your sales data is organized in a clear tabular format with regions in the first column and months formatted consistently in the column headers. Consider converting your dataset into an official Excel Table for better dynamic referencing.

Solution 1Recommended

Use INDEX and XMATCH with a Formatted Table (Recommended)

A highly efficient, dynamic approach replacing INDIRECT by matching the region row and the current month's header within an Excel Table.

While INDIRECT is often requested for dynamic ranges, it is a volatile function that can slow down performance in large workbooks. Using INDEX combined with XMATCH on a formatted Excel table is cleaner, faster, and automatically adjusts as new columns are added.

Format Data as Table

Select your entire data range and press Ctrl + T to convert it into a Table. Name the table 'TableSales' in the Table Design tab.

Prepare Headers

Ensure your header row contains valid month names (e.g., Jan, Feb, Mar) that can be evaluated to match the current month dynamically.

Enter the Formula

In your target output cell (e.g., B16), enter the formula: =INDEX(TableSales, XMATCH(B15, TableSales[Region]), XMATCH(TEXT(TODAY(),"MMM"), TableSales[#Headers])).

Reference the Target Cell

Ensure that 'B15' in the formula points to the specific input cell where the user types the country or region name.

Use INDEX and XMATCH with a Formatted Table (Recommended)

Performance Tip: Using structured Table References (like TableSales[Region]) makes the formula inherently dynamic without relying on volatile functions like INDIRECT.

Solution 2

Use INDIRECT, MATCH, and COUNTA (Alternative)

Uses the INDIRECT function combined with COUNTA to build a dynamic reference to the last populated column.

Manage Dynamic Sales Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like INDEX, MATCH, XMATCH, and INDIRECT, making it incredibly easy to track monthly sales dynamically without compatibility issues.

1. Open Your Sales Data: Launch WPS Spreadsheet and open your existing sales data workbook.
2. Format as Table: Highlight your data range and click 'Format as Table' from the Home tab to create a dynamic dataset.
3. Apply Lookup Formulas: Input your dynamic =INDEX() or =INDIRECT() formulas exactly as you would in standard Excel.
4. Get Instant Results: Press Enter to instantly retrieve the latest monthly sales for any selected region, updating in real-time.

Fully compatible with Microsoft Excel formulas and table references.Lightweight and fast execution, even with complex data sets and array formulas.Includes a robust set of dynamic array functions built-in.Free to download and use for all your daily data analysis needs.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDIRECT formula return a #REF! error?

A #REF! error usually occurs if the text string passed to the INDIRECT function does not form a valid cell reference, if there are typos in the string concatenation, or if the formula refers to an external workbook that is currently closed.

Why is INDEX and MATCH often recommended over INDIRECT?

INDIRECT is a volatile function, meaning it recalculates every time any change is made anywhere in the workbook. This can significantly slow down performance in large files. INDEX and MATCH are non-volatile and only recalculate when their specific input data changes.

Can I look up the last populated cell in a row without knowing the current month name?

Yes, you can use the LOOKUP function to find the last non-empty value in a row regardless of the month header. For example, =LOOKUP(2, 1/(B2:Z2<>""), B2:Z2) will return the last entered value in row 2.

Excel & Data Problems