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

?????????????????


--
jpx
------------------------------------------------------------------------
jpx's Profile: http://www.excelforum.com/member.php...fo&userid=1013
View this thread: http://www.excelforum.com/showthread...hreadid=398709

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

??????????????????


--
jpx
------------------------------------------------------------------------
jpx's Profile: http://www.excelforum.com/member.php...fo&userid=1013
View this thread: http://www.excelforum.com/showthread...hreadid=398709

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