# Thread: Find next whole number in list Thanks: 0 Likes:  2 Post #5340513 (1)Post #5340680 (1)

1. ## Re: Find next whole number in list

If you want the number of rows between the first real whole number and the second real whole number, you could use this array-entered** formula...

=SMALL(IF(ISNUMBER(A2:A15),ROW(A2:A15)),2)-SMALL(IF(ISNUMBER(A2:A15),ROW(A2:A15)),1)-1

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

2. ## Re: Find next whole number in list

A more compact version of this ^^^ formula was suggested in Post # 11.

3. ## Re: Find next whole number in list

I couldn't get 9 or 10 to work. I don't have a basic understanding of Aggregate to tweak it. THe other switching to my sheet, I could not get it to work. #21 works but it only works for the first and second.
I don't know how many whole numbers are before the one I am searching for. I need it to find a given Number, find the next integer, and tell me how many rows are in between

4. ## Re: Find next whole number in list

Let's throw a couple more into the mix. Basically the same formula but D2 requires Ctrl+Shift+Enter while E2 does not.
Note that I have changed the sample data slightly as you said
I don't know how many whole numbers are before the one I am searching for.
Offset

 A B C D E 1 Id 2 834e name 7 7 3 666 Family 4 834g name 5 835 Family 6 832f name 7 473m name 8 835c name 9 835d name 10 835e name 11 835f name 12 839 Family 13 832g name 14 346g name 15 836c name 16 921a name 17 922b name 18 888 Family 19 923c name

 Cell Formula D2 {=MATCH(TRUE,ISNUMBER(INDEX(A:A,MATCH(MySearch,A:A,0)+1):A7000),0)} E2 =MATCH(TRUE,INDEX(ISNUMBER(INDEX(A:A,MATCH(MySearch,A:A,0)+1):A7000),0),0)
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

5. ## Re: Find next whole number in list

Thank you. That is a home run. Sorry to all that I was not clearer from the beginning

6. ## Re: Find next whole number in list

Originally Posted by ManUBlueJay
Thank you. That is a home run. Sorry to all that I was not clearer from the beginning
You're welcome.