Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine the number of units based on given condition? | Excel Worksheet Functions | |||
smallest missing number | Excel Discussion (Misc queries) | |||
Changing chart color based on number entered | Charts and Charting in Excel | |||
Creating a certain number of entries based on a number in a cell | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |