Formula question

  • Thread starter Thread starter Mike
  • Start date Start date
M

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
 
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
 
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(D3:D10,COUNTIF(D3:D10,"<"&D2)+1),D3:D10,0))
 
Mike said:
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.
Click to expand...


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