How to determine the values from a list?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
try this for a start
C1=VLOOKUP($B$1-10,$A$1:$A$7,1)
C2=IF(VLOOKUP($B$1,$A$1:$A$7,1,1)=B1,VLOOKUP($B$1,$A$1:$A$7,1,1),"")
C3=VLOOKUP($B$1+10,$A$1:$A$7,1)
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)
 
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:A7,1,0))

In C3: =INDEX(A1:A7,MATCH(LOOKUP(B1,A1:A7),A1:A7)+1)
 
Your formula in C1 is FAILED
If OP enter 35 in B1, the result of your formula returns 20, OP wants cell
C1 returns to 30

C1=VLOOKUP($B$1-10,$A$1:$A$7,1)
 
I put an extra zero in by mistake - try C1=VLOOKUP($B$1-1,$A$1:$A$7,1)
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)
 
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
 
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:A7,1,0))
Click to expand...

Why do a double lookup?

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

Biff
 
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
 
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 Data>Validation
Allow: Custom
Formula: =AND(B1>A1,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:A7)+1))

Biff
 
Back
Top