To display data from two months ago in Excel, use the
EDATE function to calculate the target date and combine it with
VLOOKUP or
XLOOKUP to retrieve the corresponding value. This approach ensures that your spreadsheet dynamically references historical data based on a rolling timeline rather than static cell ranges. By automating this process, you eliminate manual errors associated with copying and pasting old figures into new report templates.
Understanding the EDATE Function
The core of this solution relies on the
EDATE function, which is specifically designed to add or subtract months from a given date. Unlike simple arithmetic operations that might fail when crossing year boundaries (such as moving from January to November),
EDATE correctly handles month rollovers and leap years.
The syntax for
EDATE requires two arguments:
- Start_date: The reference date from which you want to count backward.
- Months: The number of months to move forward or backward. To go back two months, you enter -2.
For example, if your current reporting date is in cell
A1, the formula
=EDATE(A1, -2) will return the date exactly two months prior. If
A1 contains
2023-10-15, the result will be
2023-08-15. This calculated date serves as the key identifier for looking up your historical data.
Retrieving Data Using VLOOKUP
Once you have the target date, you need to find the associated data point in your dataset. The most common method for this is using
VLOOKUP. Assume your data is stored in a table where Column A contains dates and Column B contains the values you wish to extract.
You can construct a formula that combines
EDATE directly inside the
VLOOKUP lookup value argument. This creates a dynamic reference that updates automatically as your "current" date changes.
=VLOOKUP(EDATE(CurrentDateCell, -2), DataRange, ColumnIndex, FALSE)
In this structure:
CurrentDateCell is the cell containing today's date or the end of the current period.DataRange is the full range of your source table, including both the date column and the value column.ColumnIndex specifies which column in the range holds the data you want (e.g., 2 if the value is in the second column).FALSE ensures an exact match is required, preventing Excel from returning approximate results.
This combination allows you to pull specific records without manually identifying row numbers. It is particularly useful for financial reports where you need to compare current performance against the same period last year or the previous month.
Alternative Approach with XLOOKUP
If you are using a newer version of Excel (Microsoft 365 or Excel 2021+),
XLOOKUP offers a more robust alternative to
VLOOKUP. It does not require you to specify a column index number and searches from left to right by default, making it less prone to breaking if columns are inserted or deleted.
The syntax for retrieving two-months-ago data using
XLOOKUP is:
=XLOOKUP(EDATE(CurrentDateCell, -2), DateColumnRange, ValueColumnRange)
Here, you explicitly define the array of dates to search through (
DateColumnRange) and the array of values to return (
). This separation of search and return arrays provides greater flexibility and clarity compared to VLOOKUP.
Additionally, XLOOKUP allows you to handle cases where no exact match is found by adding a fourth argument. For instance, you could return "No Data" instead of an error if the specific date two months ago is missing from your log.
Handling Missing Dates and Errors
In real-world datasets, not every day may have an entry. If the date calculated by EDATE does not exist in your source table, both VLOOKUP and XLOOKUP will return an #N/A error. To create a cleaner report, you can wrap these formulas in the IFERROR function.
=IFERROR(VLOOKUP(EDATE(A1, -2), A:B, 2, FALSE), "Data Unavailable")
This checks if the lookup fails and displays a custom message instead of the technical error code. This is especially important for automated dashboards where users expect clean visual outputs rather than debugging information. See how to ignore blank cells in formula contexts to further refine your data handling strategies.
Another consideration is ensuring that your date formats are consistent. If your source dates are stored as text strings rather than actual date serial numbers, EDATE may not work as expected. Always verify that the output of EDATE matches the format of the keys in your lookup table. You can use the TODAY() function as the CurrentDateCell if you always want to look back from the present moment.
Frequently Asked Questions
What happens if I am in January and look back two months?
EDATE correctly handles the year boundary, returning a date in December of the previous year. For example, if today is January 15, 2024, two months ago is November 15, 2023. No additional logic is required to manage the year change.
Can I use this formula to get data from two weeks ago instead?
No, EDATE is strictly for months. To go back two weeks, use simple subtraction: =TODAY() - 14. You would then use this resulting date in your VLOOKUP or XLOOKUP just as you did with the monthly calculation.
Why do I get an #N/A error even though the date exists?
This usually occurs due to a mismatch in data types. Ensure that the dates in your source table are formatted as true Excel dates, not as text. You can check this by selecting a cell; if the alignment is right-aligned by default, it is likely a date. If left-aligned, it may be text.
How do I make the formula look at the first day of the month instead of today?
Use the EOMONTH function combined with zero days offset to find the last day of the previous month, or adjust EDATE to point to the first day. For example, =EDATE(TODAY(), -2) & "-01" can help construct a start-of-month reference if your data is aggregated monthly.
Is there a way to look back two months from a specific historical date rather than today?
Yes, simply replace TODAY() or CurrentDateCell with the reference cell containing that historical date. The EDATE function calculates relative to whatever date you provide as the starting point, making it highly flexible for retrospective analysis.