Formula question

[Mike]

I would like to know what formula I should use to find
the following:

I need to know what row a particular coulmn of numbers
exceeds a particular cell.

ie

Cell A1 =2,000

Column B, has cumaltive numbers
C represents a particular level

see below:

B1=200 C1=101
B2=500 C2=102
B3=900 C3=103
B4=1,500 c4=104
B5=1,800 C5=105
B6=2,100 C6=106
B7=3,000 C7=107

etc
I need to have a formula that looks down column B and
finds the first cell that has a number greater than what
is in cell A1 (2,000) and then displays what is in the
corresponding column C. In this case B6 is greater than
A1 - so I wold like to display what is in C6 or 106.



TIA,

Mike

 

[Peo Sjoblom]

What if there is an exact match?

=INDEX(C1:C10,MATCH(SMALL(B1:B10,COUNTIF(B1:B10,"<"&A1)+1),B1:B10,0))

will find exact match, if no exact match is present, find the nearest
greater value

 

[Don Guillett]

Use the MATCH function to find the row. Then use that formula in an INDEX
function to find the value in col c.
something like this I found from Harlan Grove

Change my D to B and my E to C
=INDEX(E3:E10,MATCH(SMALL(D310,COUNTIF(D310,"<"&D2)+1),D310,0))

 

[Paul]

I would like to know what formula I should use to find
the following:

I need to know what row a particular coulmn of numbers
exceeds a particular cell.

ie

Cell A1 =2,000

Column B, has cumaltive numbers
C represents a particular level

see below:

B1=200 C1=101
B2=500 C2=102
B3=900 C3=103
B4=1,500 c4=104
B5=1,800 C5=105
B6=2,100 C6=106
B7=3,000 C7=107

etc
I need to have a formula that looks down column B and
finds the first cell that has a number greater than what
is in cell A1 (2,000) and then displays what is in the
corresponding column C. In this case B6 is greater than
A1 - so I wold like to display what is in C6 or 106.

Try this:
=INDEX(C1:C10,MATCH(A1,B1:B10)+1)

 

[Guest]

thanks for all the responses

mike