Use INDEX MATCH to Return Previous Week's Sales in Excel
Question details
Return sales for a selected week and the preceding week using spreadsheet lookup formulas.

- Product
- Microsoft Excel and WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A selected date is used to look up sales in a table on another sheet.
- Observed behavior
- The user wants the current week's sales and either the preceding row's sales or the sales from exactly seven days earlier.
Check that B5 contains the selected date, Sheet2 column C contains real dates, and column D contains the corresponding sales. If you plan to use the preceding row, sort the complete source table by date in ascending order and check for missing weeks.
Return sales from the row before the selected week
Use this approach when the preceding row represents the previous week and the selected date is not the first entry.
In the cell for current-week sales, enter =INDEX(Sheet2!$D$1:$D$21,MATCH($B$5,Sheet2!$C$1:$C$21,0)).
In the cell for previous-week sales, enter =INDEX(Sheet2!$D$1:$D$21,MATCH($B$5,Sheet2!$C$1:$C$21,0)-1).
Select the date in B5 and verify that its matching row on Sheet2 has an earlier data row immediately above it.

Look up sales from exactly seven days earlier
Use this approach when weeks may be missing and you need the previous calendar week's date rather than the preceding row.
Look up previous-week sales in WPS Spreadsheets
Open your sales workbook in WPS Spreadsheets and use INDEX and MATCH to retrieve the preceding row or the date exactly seven days earlier.
- 1. Open the workbook: In WPS Spreadsheets, open the sales workbook and locate the selected date in B5 and the source table on Sheet2.
- 2. Enter the current-week formula: Select the current-week sales cell and enter =INDEX(Sheet2!$D$1:$D$21,MATCH($B$5,Sheet2!$C$1:$C$21,0)).
- 3. Enter the previous-week formula: Select the previous-week sales cell and enter =IFERROR(INDEX(Sheet2!$D$1:$D$21,MATCH($B$5-7,Sheet2!$C$1:$C$21,0)),"Not found").
- 4. Check the results: Compare the displayed sales with the dates and sales in Sheet2 columns C and D, then save the workbook.

Frequently Asked Questions
Why does the previous-row formula return the wrong week?
The formula returns the row immediately above the matched date. Sort the complete table by date and check for missing weeks, or use MATCH($B$5-7,Sheet2!$C$1:$C$21,0) to find the date exactly seven days earlier.
What happens if the selected date is the first entry?
There is no preceding row within the lookup range, so do not use the MATCH result minus one for that date. Use the seven-day lookup with IFERROR if you want to display Not found when an earlier date is absent.
Why does the formula display Not found when the date appears in the table?
Check that B5 and the dates in Sheet2 column C are real dates rather than text. An exact match also requires any stored time components to match.
Can I use XLOOKUP for the date seven days earlier?
In a version of Excel that supports XLOOKUP, enter =XLOOKUP($B$5-7,Sheet2!$C$1:$C$21,Sheet2!$D$1:$D$21,"Not found",0) to look for an exact date match.




