Excel Formula to Return the Last Week With Sales Over 100
Question details
Return the week number associated with the last sales quantity greater than 100.

- 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.
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.
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.
Click an empty cell where you want the week number to appear.
Type =LOOKUP(2,1/(B8:B59>100),A8:A59) in the cell or formula bar, then press Enter.
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.

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. Open your workbook: In WPS Spreadsheets, open the workbook containing the week and sales columns.
- 2. Select a result cell: Click an empty cell where you want to display the matching week number.
- 3. Enter the formula: Type =LOOKUP(2,1/(B8:B59>100),A8:A59) and press Enter.
- 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.

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.




