Function Problems

How to Use Excel Formulas to Return the Previous Month's Value

Algirdas Jasaitis Oct 16, 2026 869 views

Question details

The user needs to retrieve data for the preceding month based on a selected month from a drop-down list using spreadsheet formulas.

How to Return the Previous Month's Value Using Formulas in Excel

Product: Excel
Device & OS: not provided
Scenario: Retrieving dynamic data from a chronological dataset when a specific month is selected via a drop-down list.
Observed behavior: The goal is to automatically calculate and display the value corresponding to the month immediately preceding the user's selection.

Before you start

Ensure your dataset is sorted in chronological order and that you have identified or named your data ranges (e.g., 'REVENUE') before applying these formulas.

Solution 1Recommended

Use INDEX and MATCH to Return the Previous Month

The INDEX and MATCH combination provides a flexible way to retrieve the row immediately preceding your selected month without relying on sequential numeric ID columns.

This method searches for the selected month, finds its exact row number, and subtracts 1 to pull the data from the row directly above it.

Select the destination cell

Click on the cell where you want the preceding month's value to be displayed.

Enter the formula

Type `=INDEX(REVENUE,MATCH($J$2,INDEX(REVENUE,,1),FALSE)-1,6)`. Replace 'REVENUE' with your actual table array or named range.

Adjust references and apply

Ensure `$J$2` points to your drop-down list cell and `6` represents the correct column index you want to return. Press Enter to apply the formula.

Use INDEX and MATCH to Return the Previous Month

Understanding the Minus One (-1): Subtracting 1 from the MATCH result shifts the row reference one step up, effectively capturing the preceding month's data.

Solution 2

Use a Modified VLOOKUP for Sequential Numeric Values

If your first column uses sequential numeric values to represent months (e.g., 1 for January, 2 for February), you can subtract 1 directly inside a VLOOKUP formula.

Powerful Spreadsheet Alternative

Use WPS Spreadsheet to Master Advanced Formulas

WPS Spreadsheet fully supports advanced lookups and reference functions like VLOOKUP, INDEX, and MATCH. It provides an intuitive interface to build dynamic dashboards and handle drop-down list data flawlessly.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the revenue and month data.
2. Initiate the formula: Click the destination cell and begin typing your `=INDEX` or `=VLOOKUP` formula.
3. Utilize syntax tooltips: Follow the built-in formula syntax tooltips to accurately select your lookup value, array, and return column.
4. Calculate the result: Press Enter to instantly calculate and display the previous month's value.

100% compatible with Microsoft Excel formulas and .xlsx file formatsBuilt-in formula suggestions to prevent syntax and reference errorsLightweight application that runs smoothly on Windows, Mac, and LinuxFree to use with comprehensive data analysis tools

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP returning an #N/A error when selecting January?

If January is the first month in your dataset (e.g., ID 1), subtracting 1 looks for ID 0, which doesn't exist. You can wrap your formula in an IFERROR function, like `=IFERROR(VLOOKUP($J$2-1,REVENUE,6,FALSE), "No Previous Month")`, to handle the first month gracefully.

What if my dataset is sorted in reverse chronological order?

If your most recent months are at the top of your spreadsheet, you need to change the `-1` in your INDEX and MATCH formula to a `+1`. This will move the reference one row down instead of up to find the previous month.

How do I create the drop-down list for the months?

Go to the Data tab on your ribbon, click on Data Validation, choose 'List' from the Allow criteria, and select the range of cells containing your month names.

Excel & Data Problems