Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup Max and Min Values in a List
Hi Given a number I want to look up what number in a list is just greater and just less than my original number. Ex. Given number 55 look at the following list of values: 22 33 44 55 66 77 and in one cell return 44 and another 66. Seems easy but I can't get it. Thanks. -- jpx ------------------------------------------------------------------------ jpx's Profile: http://www.excelforum.com/member.php...fo&userid=1013 View this thread: http://www.excelforum.com/showthread...hreadid=398709 |
#2
|
|||
|
|||
????????????????? -- jpx ------------------------------------------------------------------------ jpx's Profile: http://www.excelforum.com/member.php...fo&userid=1013 View this thread: http://www.excelforum.com/showthread...hreadid=398709 |
#3
|
|||
|
|||
If the value will exist
=INDEX(A:A,MATCH(55,A:A,0)-1) or maybe even =INDEX(A:A,IF(ISNUMBER(MATCH(B1,A:A,0)),MATCH(B1,A :A,0)-1),MATCH(A:A,B1,-1)) if it may not exist -- HTH RP (remove nothere from the email address if mailing direct) "jpx" wrote in message ... Hi Given a number I want to look up what number in a list is just greater and just less than my original number. Ex. Given number 55 look at the following list of values: 22 33 44 55 66 77 and in one cell return 44 and another 66. Seems easy but I can't get it. Thanks. -- jpx ------------------------------------------------------------------------ jpx's Profile: http://www.excelforum.com/member.php...fo&userid=1013 View this thread: http://www.excelforum.com/showthread...hreadid=398709 |
#4
|
|||
|
|||
?????????????????? -- jpx ------------------------------------------------------------------------ jpx's Profile: http://www.excelforum.com/member.php...fo&userid=1013 View this thread: http://www.excelforum.com/showthread...hreadid=398709 |
#5
|
|||
|
|||
Assuming that A1:A6 contains your list, try... =MIN(IF(A1:A655,A1:A6)) and =MAX(IF(A1:A6<55,A1:A6)) Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! jpx Wrote: Lookup Max and Min Values in a List Given a number I want to look up what number in a list is just greater and just less than my original number. Ex. Given number 55 look at the following list of values: 22 33 44 55 66 77 and in one cell return 44 and another 66. Seems easy but I can't get it. Thanks. -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=398709 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|