Function Problems

Use INDEX MATCH to Return Previous Week's Sales in Excel

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Return sales for a selected week and the preceding week using spreadsheet lookup formulas.

Use INDEX MATCH to Return Previous Week's Sales in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Find current-week sales

In the cell for current-week sales, enter =INDEX(Sheet2!$D$1:$D$21,MATCH($B$5,Sheet2!$C$1:$C$21,0)).

2
Find the preceding row's sales

In the cell for previous-week sales, enter =INDEX(Sheet2!$D$1:$D$21,MATCH($B$5,Sheet2!$C$1:$C$21,0)-1).

3
Check the result

Select the date in B5 and verify that its matching row on Sheet2 has an earlier data row immediately above it.

Return sales from the row before the selected week
Check for missing weeks: This formula returns the preceding row, not necessarily the date seven days earlier. If the selected date is the first entry, there is no preceding row within the lookup range.
WPS Spreadsheets

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. 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. 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. 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. 4. Check the results: Compare the displayed sales with the dates and sales in Sheet2 columns C and D, then save the workbook.
Compare current-week and previous-week sales with familiar formulasOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interface to review dates and results
microsoft office alternative - wps office

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.