| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup with value between 2 cells and return greater value.
I am using Vlookup to get a value. The starting cell value I have falls
between 2 different cells in my lookup. It is giving me the cell of lesser value but I need the cell with the greater value, What is the formula for this. I'm using =Lookup(b9,L22:L44). The value of cell B9 may be 16533 and say L30 = 16525 and L31 = 16540. How can I get it to return cell L31. Thanks Chris |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup with value between 2 cells and return greater value.
Chris
Try this - There is probably a better way so I am monitering this to see if anyone has something not as clunky. I have had this crop up and this is what I came up with. =OFFSET(L22,MATCH(B9,L22:L44,1)-IF(ISERROR(MATCH(B9,L22:L44,0)),0,1),0) The MATCH(B9,L22:L44,1) give the position in the array of the closest match less than or equal to B9. The -If(ISERROR(MATCH(B9,L22:L44,0),0,1) subtracts 1 if the match is exact. If B9 is less than the first number in the array this will error so you may need to add a condition for that like this =IF(B9<L22,L22,OFFSET....) If B9 is greater than L44 then you would get 0 or whatever is in cell L45 so if this occurs you may need another condition like this =IF(B9<L22,L22,IF(B9L44,L44,OFFSET....)) -- If this helps, please remember to click yes. "Chris" wrote: I am using Vlookup to get a value. The starting cell value I have falls between 2 different cells in my lookup. It is giving me the cell of lesser value but I need the cell with the greater value, What is the formula for this. I'm using =Lookup(b9,L22:L44). The value of cell B9 may be 16533 and say L30 = 16525 and L31 = 16540. How can I get it to return cell L31. Thanks Chris |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup with value between 2 cells and return greater value.
I would avoid the use of offset as it will make your function volatile and
increase the calculation overhead... Index would be better (IMO). I would attack this in one of 2 ways... One is to reverse the sort order of your lookup range and use a decending match like this... =INDEX(L22:L44, MATCH(B9, L22:L44, -1)) The other is to use a bit more complicated formula. Essentially it works like this. Determine if you have an exact match. If so then the lookup is easy. If not then add 1 to your match to get the next highest value... =IF(ISNA(MATCH(B9, L22:L44, 0)), INDEX(L22:L44, MATCH(B9, L22:L44, 1)+1), INDEX(L22:L44, MATCH(B9, L22:L44, 1))) -- HTH... Jim Thomlinson "Paul C" wrote: Chris Try this - There is probably a better way so I am monitering this to see if anyone has something not as clunky. I have had this crop up and this is what I came up with. =OFFSET(L22,MATCH(B9,L22:L44,1)-IF(ISERROR(MATCH(B9,L22:L44,0)),0,1),0) The MATCH(B9,L22:L44,1) give the position in the array of the closest match less than or equal to B9. The -If(ISERROR(MATCH(B9,L22:L44,0),0,1) subtracts 1 if the match is exact. If B9 is less than the first number in the array this will error so you may need to add a condition for that like this =IF(B9<L22,L22,OFFSET....) If B9 is greater than L44 then you would get 0 or whatever is in cell L45 so if this occurs you may need another condition like this =IF(B9<L22,L22,IF(B9L44,L44,OFFSET....)) -- If this helps, please remember to click yes. "Chris" wrote: I am using Vlookup to get a value. The starting cell value I have falls between 2 different cells in my lookup. It is giving me the cell of lesser value but I need the cell with the greater value, What is the formula for this. I'm using =Lookup(b9,L22:L44). The value of cell B9 may be 16533 and say L30 = 16525 and L31 = 16540. How can I get it to return cell L31. Thanks Chris |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup with value between 2 cells and return greater value.
Hi!
Try =INDEX($L$22:$L$44,MIN(IF(ABS($L$22:$L$44-B9)=MIN(ABS($L$22:$L$44-B9)),ROW($L$22:$L$44)-ROW($L$22)+1)),1) it is an array formula so must be enter with control+shift+enter "Chris" wrote: I am using Vlookup to get a value. The starting cell value I have falls between 2 different cells in my lookup. It is giving me the cell of lesser value but I need the cell with the greater value, What is the formula for this. I'm using =Lookup(b9,L22:L44). The value of cell B9 may be 16533 and say L30 = 16525 and L31 = 16540. How can I get it to return cell L31. Thanks Chris |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup with value between 2 cells and return greater value.
Jim - The Index Function works nicely. Do you know of any good references on
volitility and calc overhead? I have some large workbooks and could probably benefit from this information. Paul -- If this helps, please remember to click yes. "Jim Thomlinson" wrote: I would avoid the use of offset as it will make your function volatile and increase the calculation overhead... Index would be better (IMO). I would attack this in one of 2 ways... One is to reverse the sort order of your lookup range and use a decending match like this... =INDEX(L22:L44, MATCH(B9, L22:L44, -1)) The other is to use a bit more complicated formula. Essentially it works like this. Determine if you have an exact match. If so then the lookup is easy. If not then add 1 to your match to get the next highest value... =IF(ISNA(MATCH(B9, L22:L44, 0)), INDEX(L22:L44, MATCH(B9, L22:L44, 1)+1), INDEX(L22:L44, MATCH(B9, L22:L44, 1))) -- HTH... Jim Thomlinson "Paul C" wrote: Chris Try this - There is probably a better way so I am monitering this to see if anyone has something not as clunky. I have had this crop up and this is what I came up with. =OFFSET(L22,MATCH(B9,L22:L44,1)-IF(ISERROR(MATCH(B9,L22:L44,0)),0,1),0) The MATCH(B9,L22:L44,1) give the position in the array of the closest match less than or equal to B9. The -If(ISERROR(MATCH(B9,L22:L44,0),0,1) subtracts 1 if the match is exact. If B9 is less than the first number in the array this will error so you may need to add a condition for that like this =IF(B9<L22,L22,OFFSET....) If B9 is greater than L44 then you would get 0 or whatever is in cell L45 so if this occurs you may need another condition like this =IF(B9<L22,L22,IF(B9L44,L44,OFFSET....)) -- If this helps, please remember to click yes. "Chris" wrote: I am using Vlookup to get a value. The starting cell value I have falls between 2 different cells in my lookup. It is giving me the cell of lesser value but I need the cell with the greater value, What is the formula for this. I'm using =Lookup(b9,L22:L44). The value of cell B9 may be 16533 and say L30 = 16525 and L31 = 16540. How can I get it to return cell L31. Thanks Chris |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup with value between 2 cells and return greater value.
http://www.decisionmodels.com/index.htm
-- HTH... Jim Thomlinson "Paul C" wrote: Jim - The Index Function works nicely. Do you know of any good references on volitility and calc overhead? I have some large workbooks and could probably benefit from this information. Paul -- If this helps, please remember to click yes. "Jim Thomlinson" wrote: I would avoid the use of offset as it will make your function volatile and increase the calculation overhead... Index would be better (IMO). I would attack this in one of 2 ways... One is to reverse the sort order of your lookup range and use a decending match like this... =INDEX(L22:L44, MATCH(B9, L22:L44, -1)) The other is to use a bit more complicated formula. Essentially it works like this. Determine if you have an exact match. If so then the lookup is easy. If not then add 1 to your match to get the next highest value... =IF(ISNA(MATCH(B9, L22:L44, 0)), INDEX(L22:L44, MATCH(B9, L22:L44, 1)+1), INDEX(L22:L44, MATCH(B9, L22:L44, 1))) -- HTH... Jim Thomlinson "Paul C" wrote: Chris Try this - There is probably a better way so I am monitering this to see if anyone has something not as clunky. I have had this crop up and this is what I came up with. =OFFSET(L22,MATCH(B9,L22:L44,1)-IF(ISERROR(MATCH(B9,L22:L44,0)),0,1),0) The MATCH(B9,L22:L44,1) give the position in the array of the closest match less than or equal to B9. The -If(ISERROR(MATCH(B9,L22:L44,0),0,1) subtracts 1 if the match is exact. If B9 is less than the first number in the array this will error so you may need to add a condition for that like this =IF(B9<L22,L22,OFFSET....) If B9 is greater than L44 then you would get 0 or whatever is in cell L45 so if this occurs you may need another condition like this =IF(B9<L22,L22,IF(B9L44,L44,OFFSET....)) -- If this helps, please remember to click yes. "Chris" wrote: I am using Vlookup to get a value. The starting cell value I have falls between 2 different cells in my lookup. It is giving me the cell of lesser value but I need the cell with the greater value, What is the formula for this. I'm using =Lookup(b9,L22:L44). The value of cell B9 may be 16533 and say L30 = 16525 and L31 = 16540. How can I get it to return cell L31. Thanks Chris |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup with value between 2 cells and return greater value.
Jim - Thank you very much - This was very informative
-- If this helps, please remember to click yes. "Jim Thomlinson" wrote: http://www.decisionmodels.com/index.htm -- HTH... Jim Thomlinson "Paul C" wrote: Jim - The Index Function works nicely. Do you know of any good references on volitility and calc overhead? I have some large workbooks and could probably benefit from this information. Paul -- If this helps, please remember to click yes. "Jim Thomlinson" wrote: I would avoid the use of offset as it will make your function volatile and increase the calculation overhead... Index would be better (IMO). I would attack this in one of 2 ways... One is to reverse the sort order of your lookup range and use a decending match like this... =INDEX(L22:L44, MATCH(B9, L22:L44, -1)) The other is to use a bit more complicated formula. Essentially it works like this. Determine if you have an exact match. If so then the lookup is easy. If not then add 1 to your match to get the next highest value... =IF(ISNA(MATCH(B9, L22:L44, 0)), INDEX(L22:L44, MATCH(B9, L22:L44, 1)+1), INDEX(L22:L44, MATCH(B9, L22:L44, 1))) -- HTH... Jim Thomlinson "Paul C" wrote: Chris Try this - There is probably a better way so I am monitering this to see if anyone has something not as clunky. I have had this crop up and this is what I came up with. =OFFSET(L22,MATCH(B9,L22:L44,1)-IF(ISERROR(MATCH(B9,L22:L44,0)),0,1),0) The MATCH(B9,L22:L44,1) give the position in the array of the closest match less than or equal to B9. The -If(ISERROR(MATCH(B9,L22:L44,0),0,1) subtracts 1 if the match is exact. If B9 is less than the first number in the array this will error so you may need to add a condition for that like this =IF(B9<L22,L22,OFFSET....) If B9 is greater than L44 then you would get 0 or whatever is in cell L45 so if this occurs you may need another condition like this =IF(B9<L22,L22,IF(B9L44,L44,OFFSET....)) -- If this helps, please remember to click yes. "Chris" wrote: I am using Vlookup to get a value. The starting cell value I have falls between 2 different cells in my lookup. It is giving me the cell of lesser value but I need the cell with the greater value, What is the formula for this. I'm using =Lookup(b9,L22:L44). The value of cell B9 may be 16533 and say L30 = 16525 and L31 = 16540. How can I get it to return cell L31. Thanks Chris |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return first value greater than zero | Excel Worksheet Functions | |||
| Need Vlookup to return a value of $0.00 in blank cells | Excel Worksheet Functions | |||
| Need Vlookup to return a value of $0.00 in blank cells | Excel Worksheet Functions | |||
| vlookup must return a value that is greater than | Excel Worksheet Functions | |||
| How do you return the sum of two cells in a vlookup? | Excel Worksheet Functions | |||
Linear Mode
