the given number in cell B1 must be within this range.

You might want to set data validation on cell B1 to ensure the correct value
is entered.

Select cell B1
Goto the menu DataValidation
Allow: Custom
Formula: =AND(B1A1,B1<A7)
OK out

Formulas:

C1: =LOOKUP(B1,A1:A7,A1:A7)
C2: =IF(COUNTIF(A1:A7,B1),B1,"")
C3: =IF(B1=0,0,INDEX(A1:A7,MATCH(LOOKUP(B1,A1:A7),A1:A 7)+1))

Biff

"Eric" wrote in message
...
Thank everyone very mcuh for suggestions
I will set the 999999 as the highest number in the list, and set 0 as the
lowest number. Therefore the given number in cell B1 must be within this
range.
Thank everyone very much
Eric

"T. Valko" wrote:

What should happen if/when the lookup number is less than the lowest
number
in the list? What should happen if/when the lookup number is greater than
the highest number in the list? What if the lookup number equals the
lowest
number in the list? What if the lookup number equals the highest number
in
the list?

Biff

"Eric" wrote in message
...
Does anyone have any suggestions on how to determine the values from a
list?
such as following example

Under column A
There are some values listed from cell A1 to A7
10,20,30,40,50,60,70

In cell B1, there is a given value 35, I need to determine the upper
and
lower values based on the given one.
In cell C1, it returns 30 and in cell C3, it returns 40.

If the given value from B1 is 40, then
In cell C1, it returns 30, and in cell C2, it returns 40 and in cell
C3,
it
returns 50.

For the cell C1, it displays the lower value based on the given one.
For the cell C2, it displays the given value if it equals to the given
one,
else display nothing.
For the cell C3, it displays the upper value based on the given one.

Does anyone have any suggestions on how to determine the values from a
list?
Thank for any suggestions
Eric