Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Try this...
Thank you very much
Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
values not appearing in autofilter list | Excel Discussion (Misc queries) | |||
matching one value in a list of values | Excel Worksheet Functions | |||
find sum in list of of numbers | Excel Worksheet Functions | |||
Find in list function | New Users to Excel | |||
Selecting data from a list based on entered values | Excel Discussion (Misc queries) |