Function Problems

How to Find First and Last Positive Values with Excel Formulas

Maira Mehtab Sep 27, 2026 870 views

Question details

The user needs a formula to find the start date associated with the first positive value and the end date associated with the last positive value in a dataset.

How to Find the First and Last Positive Values in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing time-phased data where the goal is to extract the date boundaries (first start date and last end date) for periods that have values greater than zero.
Observed behavior: The user wants to successfully output the exact start and end dates corresponding to the first and last positive numbers using functions like XLOOKUP.

Before you start

Verify that your version of Excel or WPS Spreadsheet supports the XLOOKUP function, as it is required for the most efficient solution. Ensure your data ranges are aligned in parallel columns.

Solution 1Recommended

Use the XLOOKUP Function

XLOOKUP is the most efficient way to evaluate an array for a logical condition (like values greater than zero) and search either from top-to-bottom or bottom-to-top.

The XLOOKUP function allows you to search for a TRUE condition within an array. By modifying the search_mode argument, you can instruct the formula to find either the very first instance or the very last instance that matches your criteria.

Locate the First Positive Value Start Date

Select the cell where you want the start date to appear. Assuming your start dates are in A2:A100 and your values are in B2:B100, enter the formula: =XLOOKUP(TRUE, B2:B100>0, A2:A100) and press Enter. This performs a default top-down search for the first positive value.

Locate the Last Positive Value End Date

Select a different cell for the end date. Assuming your end dates are in C2:C100 and your values remain in B2:B100, enter the formula: =XLOOKUP(TRUE, B2:B100>0, C2:C100, "", 0, -1) and press Enter. The '-1' tells XLOOKUP to search bottom-up for the last positive value.

Formula Adjustment Tip: Make sure to adjust the ranges (A2:A100, B2:B100, C2:C100) in the formulas to perfectly match the rows of your actual worksheet layout.

Solution 2

Use INDEX and LOOKUP (For Older Versions)

If you are using an older version of Excel that lacks XLOOKUP, you can use a combination of INDEX, MATCH, and the classic LOOKUP function.

Advanced Formulas in WPS Spreadsheet

Analyze Data Quickly with XLOOKUP in WPS Office

WPS Spreadsheet fully supports the powerful XLOOKUP function natively. You can effortlessly locate the first and last positive values in your financial or time-phased datasets without switching software.

1. Open Your Data in WPS Spreadsheet: Launch WPS Office and open the workbook containing your dates and numerical values.
2. Input the First Value Formula: Click the target cell and type =XLOOKUP(TRUE, B2:B100>0, A2:A100) to extract the first positive value's start date.
3. Input the Last Value Formula: In a new cell, type =XLOOKUP(TRUE, B2:B100>0, C2:C100, "", 0, -1) to retrieve the last positive value's end date.

100% compatible with Microsoft Excel formulas, including XLOOKUP and dynamic arraysFree to download and use with a highly intuitive user interfaceProcess large datasets quickly without lagSeamless file compatibility with .xlsx and .xls formats

microsoft office alternative - wps office

Frequently Asked Questions

Why is my XLOOKUP formula returning a #N/A error?

This error appears when there are zero positive values found in your lookup array (e.g., B2:B100). You can prevent this error by adding a custom message in the 'if_not_found' argument, such as =XLOOKUP(TRUE, B2:B100>0, A2:A100, "No positive values found").

Can I adapt this formula to find the first negative value instead?

Yes. To find negative values, simply change the logical test operator in your formula. Replace B2:B100>0 with B2:B100<0.

What does the -1 mean at the end of the second XLOOKUP formula?

The -1 is placed in the sixth argument of the XLOOKUP function, which specifies the search mode. A value of -1 commands the formula to search bottom-to-top (from the last item to the first), enabling it to easily extract the very last matching value in a dataset.

Excel & Data Problems