Function Problems

Excel Formula to Return the Last Week With Sales Over 100

Amos GikundaAmos Gikunda Nov 18, 2026 869 views

Question details

Return the week number associated with the last sales quantity greater than 100.

Return the Last Week With Sales Over 100 in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Week numbers are in column A and sales quantities are in column B.
Observed behavior
The desired result is the week number in column A on the last row where the corresponding sales quantity in column B exceeds 100.
Before you start

Check that week numbers and sales quantities occupy corresponding rows and that the sales values are numeric. Adjust the example ranges A8:A59 and B8:B59 to match your data.

Solution 1Recommended

Use LOOKUP to Return the Last Matching Week

Use this formula when you need the week number from the last row with sales greater than 100.

The formula evaluates the sales range and returns the corresponding value from the week range. “Last” means the lowest matching row in the specified range, not necessarily the highest week number.

1
Select a result cell

Click an empty cell where you want the week number to appear.

2
Enter the LOOKUP formula

Type =LOOKUP(2,1/(B8:B59>100),A8:A59) in the cell or formula bar, then press Enter.

3
Adjust the ranges or threshold

Replace B8:B59 and A8:A59 with matching sales and week ranges. To use a variable threshold, replace 100 with a cell reference such as D1.

Use LOOKUP to Return the Last Matching Week
If no sales value matches: The formula returns #N/A when no value is greater than the threshold. To display a message instead, use =IFERROR(LOOKUP(2,1/(B8:B59>100),A8:A59),"No match").
WPS Spreadsheets

Find the Last Matching Week in WPS Spreadsheets

Open your workbook in WPS Spreadsheets and use the same LOOKUP formula to return the week associated with the last sales quantity over 100.

  1. 1. Open your workbook: In WPS Spreadsheets, open the workbook containing the week and sales columns.
  2. 2. Select a result cell: Click an empty cell where you want to display the matching week number.
  3. 3. Enter the formula: Type =LOOKUP(2,1/(B8:B59>100),A8:A59) and press Enter.
  4. 4. Match the formula to your data: Edit the formula bar to use your actual week and sales ranges, or replace 100 with a threshold cell reference.
Find the last qualifying sales row with a LOOKUP formulaOpen and edit Microsoft Excel XLSX and XLS filesChange the threshold by referencing a worksheet cell
microsoft office alternative - wps office

Frequently Asked Questions

Does the formula include a sales value of exactly 100?

No. The condition >100 includes only values greater than 100. Change it to >=100 if 100 should also qualify.

How can I change the threshold without editing the formula?

Enter the threshold in a cell such as D1, then use =LOOKUP(2,1/(B8:B59>D1),A8:A59).

Why does the formula return #N/A?

The result is #N/A if no sales value in the specified range exceeds the threshold. Check the sales values and range, or wrap the formula in IFERROR to display a message.

Does the formula return the highest week number?

Not necessarily. It returns the week number from the last matching row in the specified range. Sort the data by week first if the last row should also represent the latest week.