Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup Hi / Lo
I'm trying to do what i think is quite a simple function. I have a data array (B5:B13) with the following values in it: 14 40 45 59 70 79 90 100 280 my reference cell is C16 and is currently set to 63. I would like cell C18 to display the closest match below 63 (in this example 59), i have acheived this with the forumula =LOOKUP(C16,B5:B13,B5:B13). But i want cell C19 to display the closest value above 63 i.e. 70. I have tried the offset function but can't appear to get it to work, and would prefer a lookup function if possible. Thanks in advance, Chris -- chrisabberton ------------------------------------------------------------------------ chrisabberton's Profile: http://www.excelforum.com/member.php...o&userid=16872 View this thread: http://www.excelforum.com/showthread...hreadid=474744 |
#2
|
|||
|
|||
an index(match())
combo will probably do whhat you want =index(range,match(63,range,1)) for value less than 63 and =index(range,match(63,range,-1)) for vlaue above 63 "chrisabberton" wrote: I'm trying to do what i think is quite a simple function. I have a data array (B5:B13) with the following values in it: 14 40 45 59 70 79 90 100 280 my reference cell is C16 and is currently set to 63. I would like cell C18 to display the closest match below 63 (in this example 59), i have acheived this with the forumula =LOOKUP(C16,B5:B13,B5:B13). But i want cell C19 to display the closest value above 63 i.e. 70. I have tried the offset function but can't appear to get it to work, and would prefer a lookup function if possible. Thanks in advance, Chris -- chrisabberton ------------------------------------------------------------------------ chrisabberton's Profile: http://www.excelforum.com/member.php...o&userid=16872 View this thread: http://www.excelforum.com/showthread...hreadid=474744 |
#3
|
|||
|
|||
You could try
=INDEX(B5:B13,MATCH(C16,B5:B13)+1,1) HTH On Mon, 10 Oct 2005 08:49:26 -0500, chrisabberton wrote: I'm trying to do what i think is quite a simple function. I have a data array (B5:B13) with the following values in it: 14 40 45 59 70 79 90 100 280 my reference cell is C16 and is currently set to 63. I would like cell C18 to display the closest match below 63 (in this example 59), i have acheived this with the forumula =LOOKUP(C16,B5:B13,B5:B13). But i want cell C19 to display the closest value above 63 i.e. 70. I have tried the offset function but can't appear to get it to work, and would prefer a lookup function if possible. Thanks in advance, Chris __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
|
|||
|
|||
For the largest value less than or equal to the lookup value...
=LOOKUP(C16,B5:B13) For the smallest value greater than or equal to the lookup value.. =MIN(IF(B5:B13=C16,B5:B13)) ....confirmed with CONTROL+SHIFT+ENTER,not just ENTER. Hope this helps! In article , chrisabberton wrote: I'm trying to do what i think is quite a simple function. I have a data array (B5:B13) with the following values in it: 14 40 45 59 70 79 90 100 280 my reference cell is C16 and is currently set to 63. I would like cell C18 to display the closest match below 63 (in this example 59), i have acheived this with the forumula =LOOKUP(C16,B5:B13,B5:B13). But i want cell C19 to display the closest value above 63 i.e. 70. I have tried the offset function but can't appear to get it to work, and would prefer a lookup function if possible. Thanks in advance, Chris |
#5
|
|||
|
|||
Domenic wrote...
For the largest value less than or equal to the lookup value... =LOOKUP(C16,B5:B13) Since this would only work when B5:B13 were sorted in ascending order, For the smallest value greater than or equal to the lookup value.. =MIN(IF(B5:B13=C16,B5:B13)) .... Richard Buttrey's formula, =INDEX(B5:B13,MATCH(C16,B5:B13)+1) would be more efficient, and it wouldn't need array entry. |
#6
|
|||
|
|||
In article . com,
"Harlan Grove" wrote: Richard Buttrey's formula, =INDEX(B5:B13,MATCH(C16,B5:B13)+1) would be more efficient, and it wouldn't need array entry. But the formula would return #N/A if the lookup value was 10. Shouldn't the correct answer be 14? |
#7
|
|||
|
|||
Domenic wrote...
"Harlan Grove" wrote: Richard Buttrey's formula, =INDEX(B5:B13,MATCH(C16,B5:B13)+1) would be more efficient, and it wouldn't need array entry. But the formula would return #N/A if the lookup value was 10. Shouldn't the correct answer be 14? Boundary conditions. I'll grant that when C16 = 10, the smallest value in the list greater than C16 should be 14 rather than #N/A. However, if C16 were 300, what should the formula return? 0 (which your formula does) or #N/A (which provides symmetry with the LOOKUP call when C16 = 10)? If it should return #N/A, then perhaps it should resemble =IF(C16<B5,B5,INDEX(B5:B13,MATCH(C16,B5:B13)+1)) |
#8
|
|||
|
|||
Harlan Grove wrote...
.... . . . If it should return #N/A, then perhaps it should resemble =IF(C16<B5,B5,INDEX(B5:B13,MATCH(C16,B5:B13)+1) ) So maybe I should have made it return #N/A. =IF(C16<B5,B5,IF(C16=B13,#N/A,INDEX(B5:B13,MATCH(C16,B5:B12)+1))) |
#9
|
|||
|
|||
Yeah, I think the formula should return #N/A. In which case, your
formula would provide the correct result. Thanks Harlan! In article . com, "Harlan Grove" wrote: Boundary conditions. I'll grant that when C16 = 10, the smallest value in the list greater than C16 should be 14 rather than #N/A. However, if C16 were 300, what should the formula return? 0 (which your formula does) or #N/A (which provides symmetry with the LOOKUP call when C16 = 10)? If it should return #N/A, then perhaps it should resemble =IF(C16<B5,B5,INDEX(B5:B13,MATCH(C16,B5:B13)+1)) So maybe I should have made it return #N/A. =IF(C16<B5,B5,IF(C16=B13,#N/A,INDEX(B5:B13,MATCH(C16,B5:B12)+1))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |