Although the OP didn't define what to do in all cases (see my other reply)
the formula in C1 could fail and the formula in C3 could fail. If the lookup
number was 70 the formula in C3 will return a #REF! error.

In C2: =IF(ISNA(VLOOKUP(B1,A1:A7,1,0)),"",VLOOKUP(B1,A1:A 7,1,0))

Why do a double lookup?

=IF(COUNTIF(A1:A7,B1),B1,"")

Biff

"Teethless mama" wrote in message
...
In C1:
=IF(COUNTIF(A1:A7,B1),INDEX(A1:A7,MATCH(LOOKUP(B1, A1:A7),A1:A7)-1),LOOKUP(B1,A1:A7))

In C2: =IF(ISNA(VLOOKUP(B1,A1:A7,1,0)),"",VLOOKUP(B1,A1:A 7,1,0))

In C3: =INDEX(A1:A7,MATCH(LOOKUP(B1,A1:A7),A1:A7)+1)


"Eric" wrote:

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