How to find the values from a list?
 

Does anyone know how to find following from a list? For example,
Colume A
[A1] 1
[A2] 5
[A3] 9
[A4] 12
[A5] 16
[A6] 28

In Colume B, when I input the value of 7 in cell B2, then 5 will display in
B1 and 9 will display in B2. If I input the value of 16 in cell B2, then 16
will be displayed in B1 and 28 will be displayed in B3.

Colume B
[B1] 5
[B2] 7 input value
[B3] 9

Colume B
[B1] 16
[B2] 16 input value
[B3] 28

Does anyone have any suggestion on how to code it in excel?
Thank for any suggestion
Eric

Try this...
 

Thank you for your suggestion

The formula for [B3]=LOOKUP(B2,$A$1:$A$6,$A$2:$A$7) does not able to display
the last value 27, if the input is equal the last value. Could you please
give me any suggestion on fixing this situation?

Colume B
[B1] 16
[B2] 27 input value
[B3] 0

On the other hands, I forget to describe this case

Colume A
[A1] 1
[A2] 5
[A3] 9
[A4] 12
[A5] 16
[A6] 28

In Colume B, when I input the value of 28 in cell B2, then 1 will display in
B1 and 28 will display in B2.

Colume B
[B1] 1
[B2] 28 input value
[B3] 28

Could you please give me any suggestion
Thank you very much
Eric

Try this...
 

[A7] 28
[B1] =IF(B2=A6,1,VLOOKUP(B2,$A$1:$A$6,1))
[B3] =LOOKUP(B2,$A$1:$A$6,$A$2:$A$7)

"Eric" wrote:

Thank you for your suggestion

The formula for [B3]=LOOKUP(B2,$A$1:$A$6,$A$2:$A$7) does not able to display
the last value 27, if the input is equal the last value. Could you please
give me any suggestion on fixing this situation?

Colume B
[B1] 16
[B2] 27 input value
[B3] 0

On the other hands, I forget to describe this case

Colume A
[A1] 1
[A2] 5
[A3] 9
[A4] 12
[A5] 16
[A6] 28

In Colume B, when I input the value of 28 in cell B2, then 1 will display in
B1 and 28 will display in B2.

Colume B
[B1] 1
[B2] 28 input value
[B3] 28

Could you please give me any suggestion
Thank you very much
Eric

Try this...
 

Thank you very much
Eric