# excel question, finding the row number of a value
**URL:** https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311
**Category:** Factual Questions
**Created:** [August 21, 2017, 9:42pm UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311 "2017-08-21T21:42:08Z")
**Posts on this page:** 15
**Page:** 1
### Author:  [@rbroome](https://boards.straightdope.com/u/rbroome)
#### Post date: [August 21, 2017, 9:42pm UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/1 "2017-08-21T21:42:08Z")
I have a column that contains a series of positive numbers (I used the technique that OLDGUY so helpfully provided) that is terminated by a negative number. I need to scan through this column to find the row number of the negative number.
The idea is to specify the range of rows in the column and identify the maximum value in the range.
i.e.
in C(m,3) show the maximum value of the data in E(m,5) to E(n,5)
I can easily find m ( row() ), now I need to find n. Then I plan to Max(m,n).
I am willing to paste a formula in C(m,3) for multiple m’s, and the columns are all fixed.
---
### Author:  [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)
#### Post date: [August 21, 2017, 11:51pm UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/2 "2017-08-21T23:51:16Z")
There’ll be a more elegant solution for the SDMB shortly, but for quick and dirty …
If your column of numbers is in COLA use =ROW() in ColB to specify the row number of cells in COLA.
MIN(A:A) will identify the negative value
VLOOKUP (minvalue,A:B,2 false) will give the row of the negative number
---
### Author:  [@rbroome](https://boards.straightdope.com/u/rbroome)
#### Post date: [August 22, 2017, 2:24am UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/3 "2017-08-22T02:24:56Z")
thanks.
I am not sure that will do what I need.
I need B in your example-that is the row number I am searching for but do not know.
Let me lay it out again, perhaps I can do a better job explaining.
In ColE I have a series of positive numbers of unknown length but always ending in -1.
Ex:
row: colE
1 0
2 5
3 7
4 12
.
.
.
n -1
I want a function that, when placed in row 1 col b, will show the value 12. That is the contents of the cell one row up from the -1. I can manually place the function in row 1 col b.
This process repeats:
row colE
n+1 2
. 5
. 11
. 13
. 20
n+5 -1
manually place a copy of the function in row n+1, col B and show the value 20.
I thought this would be relatively simple, but I haven’t gotten it yet.
---
### Author:  [@Blunt](https://boards.straightdope.com/u/Blunt)
#### Post date: [August 22, 2017, 2:38am UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/4 "2017-08-22T02:38:48Z")
Just to make sure I’m understanding, is the -1 always in the row immediately after the last “good” value, or are there possibly blanks. If you’re always trying to find the value of the cell immediately before the -1 in row E, try the following formula:
=INDIRECT(“E”&MATCH(-1,E:E,0)-1)
This uses the indirect function to reference the cell, we already know its column is E so that is static and then we use the match function to get the row number of the -1, and subtract 1 from that to get the row immediately above it.
---
### Author:  [@Blunt](https://boards.straightdope.com/u/Blunt)
#### Post date: [August 22, 2017, 2:53am UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/5 "2017-08-22T02:53:18Z")
Also, forgot to mention, if the “negative number” isn’t always -1, but can be any negative number (and is the only negative number in the column), you can replace the -1 within the match function with min(E:E) to find the actual negative number.
---
### Author:  [@The\_Lurker\_Above](https://boards.straightdope.com/u/The_Lurker_Above)
#### Post date: [August 22, 2017, 3:18am UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/6 "2017-08-22T03:18:59Z")
A possibly silly question.
Is the largest number always in the the row above the ‘-1’ value row?
If yes, wouldn’t putting =E2 in cell B1, filter the whole thing for ‘-1’ in column B give you a compact list of the maximum value in each section?
It’s not pretty or good long term but quick and conceptually simple.
---
### Author:  [@The\_Lurker\_Above](https://boards.straightdope.com/u/The_Lurker_Above)
#### Post date: [August 22, 2017, 3:45am UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/7 "2017-08-22T03:45:02Z")
> [@The\_Lurker\_Above](#):
>
> It’s not pretty or good long term but quick and conceptually simple.
Okay, this one stuck with me so here’s a ‘nicer’ solution that doesn’t depend on the max value being in the row above the -1.
This assumes that the values are in column E, with a -1 in E1 to start us off, and that you can use column D as a helper column, and you want the results in B.
Cells in column D contain =IF(E2\<0,D1+1,D1) starting in D2; this is to have an identifier for each section. Cells in column B contain =IF(E1=-1,MAXIFS(E:E,D: D,D2),“”) starting in B2; this only displays a value in in row below a -1 in E, and displays the max value for the section identified in D. If this is just for display you can hide D, if you’re going to pivot off this I’d replace the empty string in B with 0.
I know functions over an entire column (ie E:E) are bad form, ideally replace with a table column reference or named range.
---
### Author:  [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)
#### Post date: [August 22, 2017, 4:13am UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/8 "2017-08-22T04:13:25Z")
[QUOTE=Original Question]
I need to scan through this column to find the row number of the negative number.
[/quote]
Which was done.
[QUOTE=Revised Question]
I want a function that, when placed in row 1 col b, will show the value 12
[/quote]
To do that you’ll need to use the INDIRECT function after you’ve determined the range via the first stage
---
### Author:  [@rbroome](https://boards.straightdope.com/u/rbroome)
#### Post date: [August 22, 2017, 2:10pm UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/9 "2017-08-22T14:10:08Z")
Thanks for all the suggestions.
Here are a couple of issues I am still stuck on.
First, the flag is always -1 and I am interested in the number immediately preceding the flag.
The main issue is that there are multiple -1s in the list. I can’t just search down E:E.
That is the stumbling block for me.
At least, that is what I believe. I have tried searching through E:E and it hasn’t worked. Perhaps there is a way to search E:E such that it stops on the first -1 found.
I have to automatically specify a range (all the rows between the earlier -1 and the latter -1), and find the maximum value in that range. That the max value always precedes the latter -1 is a bonus, but not a requirement.
How does one find the range Em:En? Where m is the row where the function resides, so I do know that variable.
One key rule is that all I care about is the next -1 in the list, I don’t care where the rest of the -1s are. That should make the problem easy(ier), but I can’t figure it out because while I don’t care about them, the other -1s do exist.
---
### Author:  [@rbroome](https://boards.straightdope.com/u/rbroome)
#### Post date: [August 22, 2017, 2:28pm UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/10 "2017-08-22T14:28:53Z")
INDIRECT(“E”&MATCH(-1,E:E,0)-1)
works for the first series, but it never searches past the first -1.
When I place the function further down the list, say row 47, it still finds the first -1, obviously since it is the same function as before.
When I try to specify the start row of the range, say E47:E, I get an error.
---
### Author:  [@The\_Lurker\_Above](https://boards.straightdope.com/u/The_Lurker_Above)
#### Post date: [August 22, 2017, 3:06pm UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/11 "2017-08-22T15:06:59Z")
Here’s a downloadable example of my suggestion from post 7 [Google Sheets Download](https://docs.google.com/spreadsheets/d/1MKeGi65CoxjgdNVSSSIdI3NxbUT77qw7HvsW_PgCZJY/pub?output=xlsx). Assuming I understand your requirements this should do what you’re asking. E is the column with the values you’re testing, D is a helper column you can hide, B is the column with the max value in E between that row and the next -1, but only displaying when the previous row has a -1 in E.
When I went to download it myself for testing #name errors popped up when I enabled editing, but you can view the formulas without doing that (and not enabling editing on strange spreadsheets you download is best practices anyway). If you do enable editing you can select the cell with the error hit enter and it will recalculate.
---
### Author:  [@rbroome](https://boards.straightdope.com/u/rbroome)
#### Post date: [August 22, 2017, 8:49pm UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/12 "2017-08-22T20:49:48Z")
> [@The\_Lurker\_Above](#):
>
> Here’s a downloadable example of my suggestion from post 7 [Google Sheets Download](https://docs.google.com/spreadsheets/d/1MKeGi65CoxjgdNVSSSIdI3NxbUT77qw7HvsW_PgCZJY/pub?output=xlsx). Assuming I understand your requirements this should do what you’re asking. E is the column with the values you’re testing, D is a helper column you can hide, B is the column with the max value in E between that row and the next -1, but only displaying when the previous row has a -1 in E.
>
> When I went to download it myself for testing #name errors popped up when I enabled editing, but you can view the formulas without doing that (and not enabling editing on strange spreadsheets you download is best practices anyway). If you do enable editing you can select the cell with the error hit enter and it will recalculate.
Thanks! I hadn’t tried your suggestion yet. Thank you for taking the trouble to work up an example.
And I need to learn more about “Google Sheets Download”, I haven’t used that before. It works very well.
---
### Author:  [@rbroome](https://boards.straightdope.com/u/rbroome)
#### Post date: [August 22, 2017, 8:57pm UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/13 "2017-08-22T20:57:05Z")
I am afraid I don’t use Office 365 so I don’t have access to the maxifs function. I think I can simulate it though. I am using MS Excel for the Mac 2011.
---
### Author:  [@The\_Lurker\_Above](https://boards.straightdope.com/u/The_Lurker_Above)
#### Post date: [August 22, 2017, 11:06pm UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/14 "2017-08-22T23:06:33Z")
> [@rbroome](#):
>
> I am afraid I don’t use Office 365 so I don’t have access to the maxifs function. I think I can simulate it though. I am using MS Excel for the Mac 2011.
Yeah, the newer versions of Excel have some nice features.
A little googling/testing and I think you can duplicate the results the MAXIF function by replacing it with {=IF(E1=-1,MAX(IF(D: D=D2,E:E,0)),“”)}. The curly braces means you need to enter it as an array formula (if you haven’t used array formulas you’ll want to check your help files on exactly how. In the Windows versions you need to hit CONTROL + SHIFT + ENTER, Mac may or may not use that method). It works on both versions of Excel I have (and with different syntax on Google Sheets) so hopefully it works on your version.
---
### Author:  [@rbroome](https://boards.straightdope.com/u/rbroome)
#### Post date: [August 23, 2017, 2:20am UTC](https://boards.straightdope.com/t/excel-question-finding-the-row-number-of-a-value/794311/15 "2017-08-23T02:20:18Z")
> [@The\_Lurker\_Above](#):
>
> Yeah, the newer versions of Excel have some nice features.
>
> A little googling/testing and I think you can duplicate the results the MAXIF function by replacing it with {=IF(E1=-1,MAX(IF(D: D=D2,E:E,0)),“”)}. The curly braces means you need to enter it as an array formula (if you haven’t used array formulas you’ll want to check your help files on exactly how. In the Windows versions you need to hit CONTROL + SHIFT + ENTER, Mac may or may not use that method). It works on both versions of Excel I have (and with different syntax on Google Sheets) so hopefully it works on your version.
Thanks!
I will try that.