Lookup First Negative Value in Excel: 3 Quick Ways
- Average speed
- km/h
- Result
- Finish time
- 50 min
- You entered
- Distance
- 10 km
- You entered
This article explains how to find the first negative value in a list or column using Excel, Google Sheets, or similar spreadsheet software. You’ll learn three reliable methods—INDEX/MATCH, LOOKUP, and an array formula—and how to choose the right one for your data. The answer to the direct question is: use the formula =INDEX(A:A,MATCH(TRUE,INDEX(A:A<0,0),0)) for a column of numbers, which returns the first negative value in the order it appears.
Formula
The core formula for finding the first negative value in a column (say, column A, rows 1 to 100) is:
=INDEX(A1:A100, MATCH(TRUE, INDEX(A1:A100<0, 0), 0))
Here’s what each part does:
- INDEX(A1:A100, …) – returns the value at a specific position within the range.
- MATCH(TRUE, …) – finds the position of the first TRUE in an array of TRUE/FALSE values.
- INDEX(A1:A100<0, 0) – creates an array where each cell is TRUE if the corresponding cell in A1:A100 is negative, and FALSE otherwise. The
0row argument tells Excel to return the entire column array. - The final
0in MATCH means “exact match.”
In Google Sheets, the same formula works. If you want the position (row number) instead of the value, use just =MATCH(TRUE, INDEX(A1:A100<0, 0), 0).
Worked Example
Suppose column A contains the values: 10, 5, -3, 7, -8, 2. You want the first negative value. Here’s how the formula evaluates:
- The comparison
A1:A6<0produces: FALSE, FALSE, TRUE, FALSE, TRUE, FALSE. MATCH(TRUE, ...)finds the first TRUE, which is at position 3.INDEX(A1:A6, 3)returns the value in A3, which is -3.
So the answer is -3. The same logic works for any range; just adjust the cell references.
Why You Might Need This
Finding the first negative value is common in financial data (e.g., first month with a loss), inventory (first negative stock), temperature logs (first below-zero reading), or any dataset where you need to locate the first occurrence of a condition. The formula is especially useful when your data is unsorted and you can’t use a simple lookup like VLOOKUP, which requires sorted data for approximate matches.
If your data is sorted in ascending order (e.g., -5, -2, 0, 3, 8), you can use a simpler approach: =INDEX(A:A, MATCH(TRUE, A:A>=0, 0) - 1) would give the last negative before the first non-negative, but the INDEX/MATCH method above works regardless of order.
How to Choose the Right Method
There are three main methods, each with its own strengths:
- INDEX/MATCH with an array (recommended) – Works in both Excel and Google Sheets without special entry (in Excel 365, it’s dynamic; in older Excel, you may need Ctrl+Shift+Enter). It finds the first negative value directly.
- LOOKUP with a clever trick –
=LOOKUP(2, 1/(A1:A100<0), A1:A100)returns the last negative value, not the first. To get the first, you’d need to reverse the range, which is messy. So this method is better for finding the last negative. - Array formula with MIN and IF –
=MIN(IF(A1:A100<0, ROW(A1:A100)))gives the row number of the first negative (as an array formula). Then you can use INDEX to get the value. This works but is more complex.
For most users, the INDEX/MATCH formula is the best balance of simplicity and reliability. If you’re using Excel 365 or Google Sheets, it works as a normal formula. In older Excel (2019 and earlier), you may need to press Ctrl+Shift+Enter to confirm it as an array formula.
Common Mistakes and How to Avoid Them
- Forgetting to use Ctrl+Shift+Enter in older Excel – If you get a #VALUE! error, try confirming the formula with Ctrl+Shift+Enter instead of just Enter.
- Using the wrong range – Make sure the range in the formula matches your data exactly. Including empty cells is fine, but if your range is too small, you might miss the first negative.
- Confusing “first” with “minimum” – The first negative is the one that appears first in the list, not the most negative (e.g., -100 vs -1). If you need the smallest value, use
=MIN(IF(A1:A100<0, A1:A100))as an array formula. - Not handling “no negative values” – If there are no negatives, the formula returns #N/A. To avoid this, wrap it in IFERROR:
=IFERROR(INDEX(A:A, MATCH(TRUE, INDEX(A:A<0,0),0)), "No negatives found").
Alternatives for Large Datasets or Special Cases
If your dataset is huge (tens of thousands of rows), the array formula might slow down your spreadsheet. In that case, consider using a helper column: add a column with formula =A1<0, then use =MATCH(TRUE, B1:B100, 0) to get the row number, and =INDEX(A:A, row) to get the value. This avoids array calculations and is faster.
For data that is already sorted ascending, you can use =INDEX(A:A, MATCH(TRUE, A:A>=0, 0) - 1) to get the last negative before the first non-negative, but that gives the last negative, not the first. If your data is sorted descending (e.g., 10, 5, -3, -8), then the first negative is simply the first value that is less than 0, so you could use =INDEX(A:A, MATCH(TRUE, A:A<0, 0)) as well, but the standard formula already handles that.
If you need to find the first negative in a row (horizontal data), just change the ranges accordingly: =INDEX(1:1, MATCH(TRUE, INDEX(1:1<0, 0), 0)).
Frequently Asked Questions
Q: What if my data has text or blank cells?
Blank cells are treated as 0, so they won’t trigger a negative match. Text cells will cause the comparison to return FALSE (since text is not less than 0), so they are ignored. The formula will still find the first numeric negative value.
Q: Can I use this formula in Google Sheets?
Yes, the same INDEX/MATCH formula works in Google Sheets without any special entry. Just type it normally and press Enter.
Q: How do I get the row number instead of the value?
Replace the outer INDEX with just the MATCH part: =MATCH(TRUE, INDEX(A1:A100<0, 0), 0). This returns the relative row number within the range (e.g., 3 for the third cell). If you need the absolute row number in the sheet, add the starting row minus 1: =MATCH(TRUE, INDEX(A1:A100<0, 0), 0) + 0 (if starting at row 1, it’s the same). For a range starting at row 5, use =MATCH(...) + 4.
Q: What if there are no negative values?
The formula returns #N/A. To display a friendly message, wrap the formula in IFERROR: =IFERROR(INDEX(A:A, MATCH(TRUE, INDEX(A:A<0,0),0)), "No negatives").
Q: Does this work with conditional formatting to highlight the first negative?
Yes, you can use a formula in conditional formatting to highlight the first negative cell. Select your range, go to Conditional Formatting > New Rule > Use a formula, and enter =AND(A1<0, COUNTIF($A$1:A1, "<0")=1) (adjust for your range). This highlights the first negative cell only.
Q: Can I find the first negative value that is also below a certain threshold, like -10?
Yes, modify the comparison to A1:A100<-10. For example, =INDEX(A:A, MATCH(TRUE, INDEX(A:A<-10,0),0)) finds the first value less than -10.
Related
Formula used
in t | Finish time | min | 50 | 0 in d | Distance | km | 10 | 0 out pace | Average pace | min/km | t / d | 2 out speed | Average speed | km/h | d / (t / 60) | 2
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.