Table of Contents
You can use the following formula to find the first negative value in a particular range in Excel:
=XLOOKUP(-1,SIGN(B2:B13),A2:B13)
This particular formula will look for the first cell in the range B2:B13 that contains a negative value and return the entire row from the range A2:B13 as a result.
The following example shows how to use this formula in practice.
Example: How to Find First Negative Value in Range in Excel
Suppose we have the following dataset that shows the total profits made by various employees at some company:
Notice that some of the values in the Net Profits column are positive while others are negative.
Suppose that we would like to find the first negative value in the Net Profits column.
We can type the following formula into cell D2 to do so:
The formula returns the values from both the Employee and Net Profits columns that correspond to the first negative value from the Net Profits columns.
We can manually verify that Eric is indeed the first employee with a negative value in the Net Profits column:
Note that since we used A2:B13 as the last argument in the XLOOKUP function, we returned both values from column A and column B.
However, we could instead use A2:A13 as the last argument to only return the name of the employee with the first negative value in the Net Profits column:
How This Formula Works
Recall the formula that we used to find the first negative value in the Net Profits column:
=XLOOKUP(-1,SIGN(B2:B13),A2:B13)
The XLOOKUP function returns the first matching value from a column.
In this formula, we first use the SIGN function to convert each value in the range B2:B13 to either 1 or -1 to indicate if each value is positive or negative.
We then use the XLOOKUP function to look up the first value equal to -1, which returns the first negative value from the Net Profits column.