Find the first or last positive value in a list

To find the first or last positive value in a list (row or column), use the XLOOKUP, SIGN, INDEX, or MATCH functions.

Generic Formula to lookup the first positive value

XLOOKUP simplifies everything so that we will use it as default. To find the first positive value in a range, use this formula where “rng” is the range of cells where we try to find the first positive value:

=XLOOKUP(1,--(rng > 0), rng)

Based on the generic formula above, enter the cell F4:

=XLOOKUP(1,--(profit>0), data)

As usual, it is worth using named ranges; “profit” refers to range D3:D10, and “data” refers to B3:C10.

double negative formula with xlookup

The result is the 4th row in the range since this is the first positive number in the Profit column. The XLOOKUP-based formula will return an array that contains the result and the corresponding ID.

How to find the first positive value

All examples use the same logic: lookup the first positive value in a range on different Excel versions.

XLOOKUP and SIGN functions

First, let us see the way to find the first positive value using the SIGN and XLOOKUP functions. The SIGN function works simply: it returns the sign of a number. So we want to find the first positive value and use “1” as an argument. Note: if you find negative values or zeros, use the -1 or 0 arguments; the method is the same.

The point is that you can use the output of the SIGN functions as a lookup value! Enter the following formula in cell F3:

=XLOOKUP(1,SIGN(profit), data)
lookup first positive value sign function

Lookup the last positive value in a list

If you want to find the last positive number, add extra parameters to the XLOOKUP function. Apply the 6th argument to control the search mode.




find lookup the last positive value

INDEX and MATCH to get the first positive value

If you are not using Microsoft 365 subscription, there is an alternative way to get the same result as the example above. The following INDEX and MATCH combination works fine with Excel 2013, Excel 2016, or Excel 2019.

Here is the formula to lookup the first positive value in a range using INDEX + MATCH:

INDEX and MATCH formula to lookup first positive value

Evaluate the formula from the inside out! The “–(profit>0)” part will convert boolean values (TRUE or FALSE) to 0 and 1.

convert true or false to 0 or 1

Apply the MATCH function to extract the position of the first positive value from the lookup array. The result array looks like this:

={0, 0, 0, 1, 0, 1, 1, 0}

We find the positions where the cell value = 1. So, the array contains three values that are greater than 0. The following formula will get the first position where the profit > 0. It is the 4th row in the table.

=MATCH(1,--(profit>0),0) = 4
MATCH formula to get the position

The INDEX function uses the result as a row_num argument:


Find the last positive value using the LOOKUP function

The LOOKUP function searches for a value from a one-row or one-column range or an array.

Configure the function arguments like this:

  • lookup value = 2
  • lookup vector = 1/(profit>0)
  • result vector = profit


lookup the last positive value

Note: the result vector is an optional argument.

Lookup first positive value using INDEX and FILTER functions

In the following example, we will list all matching values using the INDEX and FILTER functions. The result array contains two rows; the result includes all columns in the source table.

FILTER function arguments:

  • array: data
  • include: profit > 0


=FILTER(data, profit>0)
filter all matches

The result contains all records where the profit > 0, so we get all cells with positive values. Therefore, we have three records, but we need to use only the last one to get the last positive value in the array.

formula to get last positive value

The ROWS function will get the position of the last positive item in the array. Finally, the INDEX function will use the position as an argument the get the proper result:


Related Formulas and Functions

Istvan Vozar

Istvan is the co-founder of Visual Analytics Ltd. He writes blog posts and helps people to reach the top in Excel.