Lookup Based on Next Smallest Number
 

I have three columns of data

Column A Column B Column C
247,156 247,156 .40
0 247,156 .40
0 247,156 .40
245,156 494,312 .70
247,156 741,468 .30

Column B is a running total of column A.

I need to extract the value from column C based on where a value falls
within the ranges identified in column B.

For example, if the criteria value is 500,000, I would want to extract .70.
If the criteria value is 230,000, I would want to extract .40. If the
criteria value is 750,000, I would want to extract .30.

Is the following formula the best way to get this data:

Hlookup(large(b1:b5,countif(b1:b5,""&d1)+1),b1:c5 ,2,false)

Will I have a problem with this formula since column B has 3 values that are
equal?

Thanks

Lookup Based on Next Smallest Number
 

Based on your sample data it appears that the value in column B is *always*
increasing or the same so in effect it is sorted ascending. So, try this:

=IF(D1="","",IF(D1<B1,C1,VLOOKUP(D1,B1:C5,2)))

Biff

"M Moore" wrote in message
...
I have three columns of data

Column A Column B Column C
247,156 247,156 .40
0 247,156 .40
0 247,156 .40
245,156 494,312 .70
247,156 741,468 .30

Column B is a running total of column A.

I need to extract the value from column C based on where a value falls
within the ranges identified in column B.

For example, if the criteria value is 500,000, I would want to extract
.70. If the criteria value is 230,000, I would want to extract .40. If
the criteria value is 750,000, I would want to extract .30.

Is the following formula the best way to get this data:

Hlookup(large(b1:b5,countif(b1:b5,""&d1)+1),b1:c5 ,2,false)

Will I have a problem with this formula since column B has 3 values that
are equal?

Thanks