How to find the first / last positive / negative number in Excel?
When working with a column of numbers that contains both positive and negative values, you may often need to quickly locate the first or last positive or negative number in the range. This can be especially helpful for data analysis, trend detection, or identifying specific entry points in large datasets. Relying on manual inspection for large data sets is inefficient and error-prone. Fortunately, Excel provides several practical methods to streamline this task, allowing you to extract the precise values you need with formulas or automation. Below, you'll find multiple solutions suitable for different scenarios, including advanced approaches ideal for repeated or large-scale operations.
Find the first positive / negative number with array formula
Find the last positive / negative number with array formula
VBA macro to find the first / last positive / negative number
Find the first positive / negative number with array formula
To extract the first positive or negative number from a series of values, you can use Excel's array formulas. This method is suitable for users who need a quick solution for moderate data ranges and are comfortable with formulas, especially in environments where additional add-ins or macros are restricted. The array approach updates automatically if your source data changes, making it well-suited for dynamic lists. Here is how you can implement it:
1. Select a blank cell, enter the following array formula to get the first positive number:
=INDEX(A2:A18,MATCH(TRUE,A2:A18>0,0))Here, A2:A18 refers to the data list you want to search through. This formula locates the first cell in the range with a value greater than 0, then returns the content of that cell. See the following screenshot:
2. After typing the formula, press Ctrl + Shift + Enter simultaneously, instead of just pressing Enter. This will properly execute the array formula and return the first positive number from your list, as displayed in the example below:
Tip: To retrieve the first negative number instead, simply use this formula (remember to press Ctrl + Shift + Enter after typing):
=INDEX(A2:A18,MATCH(TRUE,A2:A18<0,0))In both formulas, changing the condition (>0 for positive, <0 for negative) allows you to target the required number type. Please note that array formulas do not support empty cell references, so ensure your data range does not include blank cells for consistent results. If all numbers are either positive or negative, the formula may return an error—consider adding an IFERROR function if you want to suppress errors and display a custom message.
Notice: In recent versions of Excel (Office 365 and Excel 2021 and later), you may not need to use Ctrl + Shift + Enter; just pressing Enter may be sufficient because of dynamic arrays support.
Find the last positive / negative number with array formula
If your goal is to identify the last positive or negative value in a column, you can use a different array formula. This approach is suitable for quickly analyzing ending trends or locating the most recent data point of a specific type. Note that this method dynamically reflects data updates, which is especially valuable when you regularly append new numbers to the list.
1. Select a blank cell next to your data column and input this array formula to find the last positive number:
=LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 >0, $A$2:$A$18))This formula works by leveraging LOOKUP’s behavior of returning the last numeric match found for an extremely large number. Here, IF($A$2:$A$18 >0, $A$2:$A$18) filters only the positive numbers, and LOOKUP then returns the last occurrence. See this illustrated below:
2. Confirm the formula by pressing Ctrl + Shift + Enter (unless your Excel version supports dynamic arrays). The result will show the last positive value present in the specified range, as demonstrated below:
To return the last negative number, use the following array formula instead, also with Ctrl + Shift + Enter:
=LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 <0, $A$2:$A$18))If no positive or negative value is found, the formula will return an error (#N/A). To handle such cases gracefully, wrap the formula in IFERROR. For example:
=IFERROR(LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 >0, $A$2:$A$18)), "No match found")It is important to avoid merged cells or combined text/number formats in your range, as they may disrupt the formula’s calculation results. Always verify your data integrity before using these methods for best accuracy.
VBA macro to find the first / last positive / negative number
If you frequently need to find the first or last positive or negative number in multiple ranges or very large datasets, automating this task with a VBA macro can save significant time and reduce manual errors. This solution allows you to search a selected range and instantly retrieve the required value, making it ideal for batch processing or repetitive analytical tasks. The VBA approach is particularly helpful in scenarios where complex criteria or customized workflows are needed, although it does require basic familiarity with Excel’s Developer tools.
1. Click Developer > Visual Basic to open the Microsoft Visual Basic for Applications window. Then, in the VBA editor, click Insert > Module, and copy the following code into the new module:
Sub FindFirstOrLastPosNegNumber()
Dim rng As Range
Dim cell As Range
Dim result As Variant
Dim firstPos As Variant, firstNeg As Variant
Dim lastPos As Variant, lastNeg As Variant
Dim selType As String
On Error Resume Next
Set rng = Application.InputBox("Select the data range", "KutoolsforExcel", Selection.Address, Type:=8)
If rng Is Nothing Then Exit Sub
selType = Application.InputBox("Type 'FirstPos' for first positive, 'FirstNeg' for first negative, 'LastPos' for last positive, or 'LastNeg' for last negative:", "KutoolsforExcel", "FirstPos", Type:=2)
If selType = "" Then Exit Sub
firstPos = Empty
firstNeg = Empty
lastPos = Empty
lastNeg = Empty
' Find first positive and first negative
For Each cell In rng
If IsNumeric(cell.Value) Then
If firstPos = Empty And cell.Value > 0 Then
firstPos = cell.Value
End If
If firstNeg = Empty And cell.Value < 0 Then
firstNeg = cell.Value
End If
If cell.Value > 0 Then
lastPos = cell.Value
End If
If cell.Value < 0 Then
lastNeg = cell.Value
End If
End If
Next cell
Select Case UCase(selType)
Case "FIRSTPOS"
result = firstPos
Case "FIRSTNEG"
result = firstNeg
Case "LASTPOS"
result = lastPos
Case "LASTNEG"
result = lastNeg
Case Else
result = "Invalid input"
End Select
If IsEmpty(result) Then
MsgBox "No matching value found in the selected range.", vbInformation, "KutoolsforExcel"
Else
MsgBox "Result: " & result, vbInformation, "KutoolsforExcel"
End If
End Sub2. To execute the macro, press F5 (or click the 
Run button), and follow these steps:
- A dialog will prompt you to select your number range (for example, A2:A18).
- Next, enter your search type: type FirstPos for the first positive number, FirstNeg for the first negative number, LastPos for the last positive number, or LastNeg for the last negative number (not case sensitive).
- After entering your choice and confirming, the result will be displayed in a message box.
Tips:
- This macro can handle any contiguous number range selected by the user, allowing for flexibility in data layout.
- If the specified type does not match any number in the range, you will get a notification instead of an error.
- Make sure macros are enabled in your Excel for the VBA code to work.
- If your data includes non-numeric values, the macro will ignore them during processing.
Troubleshooting and suggestions: For all solutions, always confirm your selection contains the intended range and does not include headers. If you use large ranges, consider limiting the size to avoid calculation or performance delays, especially when using array formulas or macros.
If you find yourself frequently performing this task or want more customization, consider combining multiple criteria in your macro or creating a dedicated button for easier access. Always save your work before trying new VBA scripts and test on backup copies if you're new to programming.
Related articles:
How to find the first / last value greater than X in Excel?
How to find highest value in a row and return column header in Excel?
How to find the highest value and return adjacent cell value in Excel?
How to find the max or min value based on criteria in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in