first number larger than given number

[rehabman]

I have a column of numbers in column A. Numbers are all positive and greater than zero, but they are not in numerical order. In cell B3 I have a number. I want to look in column A from row 4 to the end and find the first number that is greater than the number in B3. In cell C3 I want to place the number of rows down in column A this is. For example:

A B C
1 3
2 6
3 5 6
4 2
5 4
6 4
7 1
8 8
9 4

Since the number in B3 is 6 and the first time the number in Column A exceeds 6 is in A8, which is 5 rows below b3, I want to place the number 8 in cell C3. I could do this with VLOOKUP if column A was in numerical order, but it is not.

Thanks for any help.

 

[pgc01]

Hi
Welcome to the board

Try in C3:

=INDEX(A3:A100,MATCH(TRUE,A3:A100>B3,0))

This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

 

[andiemac]

If the values in column A repeat, and you want to always catch the first one below the row you are in try

=MATCH(B1,A1:$A$9999,0)+ROW()

If it's possible that there is not a match that is below the row you are in you may want to add something to handle errors - #N/A

 

[rehabman]

pgc, I pasted that into C3 and got a #NA response.

 

[rehabman]

andiemac, I'm not looking for a match, I'm looking for the first instance when the value in A exceeds the value in the reference cell in B.

 

[pgc01]

pgc, I pasted that into C3 and got a #NA response.

THis is an array formula. Did you confirm the formula with CTRL+SHIFT+ENTER as I wrote?

 

[pgc01]

I want to look in column A from row 4 to the end and find the first number that is greater than the number in B3

Hi

The search should only start at the next row, so the range should be:

A4:A100

Looking at the values you posted, that does not change the result of the initial test.

 

[rehabman]

A match type of 0 will find only an exact match. (My example was misleading since I used whole numbers so there was an exact match. In my worksheet, the numbers have two digits to the right of the decimal and the number in column B will not usually be an exact match.) Using a match type of 1 or -1 requires the numbers to be in numerical order.

 

[pgc01]

A match type of 0 will find only an exact match. (My example was misleading since I used whole numbers so there was an exact match. In my worksheet, the numbers have two digits to the right of the decimal and the number in column B will not usually be an exact match.) Using a match type of 1 or -1 requires the numbers to be in numerical order.

My formula in C3 is simply displaying the first number in column A, after row 4, that is bigger than B3. It doesn't matter if the numbers have decimals.

I still think that you are not entering the formula as an array formula, but please try the same thing, but with the formula tweaked so that you can enter it the usual way.

In C3:

=INDEX(A3:A100,MATCH(TRUE,INDEX(A3:A100>B3,0),0))


<TABLE style="BORDER-BOTTOM-COLOR: #cccccc; BORDER-RIGHT-WIDTH: 2px; BORDER-TOP-COLOR: #cccccc; BORDER-COLLAPSE: collapse; FONT-FAMILY: Arial,Arial; BACKGROUND: #fff; BORDER-TOP-WIDTH: 2px; BORDER-BOTTOM-WIDTH: 2px; BORDER-RIGHT-COLOR: #cccccc; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #cccccc; BORDER-LEFT-WIDTH: 2px" border=1 cellPadding=1><TBODY><TR><TH style="BORDER-BOTTOM-COLOR: #888888; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px"> </TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>A</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>B</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>C</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>D</TH></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">3</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">2</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">6</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">3</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">5</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">6</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">8</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">2</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">5</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">6</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">7</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">8</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">8</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">9</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">10</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"> </TD></TR><TR><TD style="PADDING-LEFT: 1em; BACKGROUND: #9cf" colSpan=5>[Book1]Sheet1</TD></TR></TBODY></TABLE>

 

[rehabman]

Here is a portion of my actual worksheet:

<table border="0" cellpadding="0" cellspacing="0" width="192"><col style="width:48pt" width="64"> <col style="width:48pt" span="2" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt; width:48pt" align="right" height="20" width="64">75.00</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">74.37</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">74.00</td> <td align="right">73.3</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">73.12</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">71.25</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">71.12</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">71.50</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">71.87</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">72.00</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">71.12</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">71.75</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">72.25</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">70.75</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">71.50</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">72.25</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">72.12</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">71.00</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">72.37</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">71.62</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">71.37</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">72.37</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">72.75</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">75.00</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">74.50</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">74.75</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">73.75</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">74.25</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">75.00</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">75.50</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">75.12</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">75.25</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">76.00</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">76.75</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">77.75</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">77.25</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">76.62</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">76.50</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">76.25</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">75.50</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">74.50</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">75.00</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">75.00</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">76.12</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">76.62</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">76.62</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">75.50</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">76.00</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">77.50</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">78.25</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">78.37</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">78.00</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">77.62</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">78.00</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">78.37</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">77.50</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">77.50</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">77.37</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">77.12</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">77.87</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">76.75</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">77.50</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">77.87</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">77.75</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">76.75</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">76.50</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">75.62</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">75.75</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">76.37</td> <td>
</td> <td>
</td> </tr> </tbody></table>
The first column is column A and the 73.3 is in cell B3. Since 75.00 is the first number to exceed 73.3 and it is 20 rows down, I want to have the number 20 in cell C3.