looking up a number

  • Thread starter Thread starter Soccer boy
  • Start date Start date
S

Soccer boy

I am wanting to retreive data from an inputted list. What i want to do is
input a number and have the function give me the next highest and lowest
number.
Say for example

90
92.5
93.1
94.7
96.2
98.3

and i enter the number 95. the result should give me the values of 94.7 and
96.2. is this possible to do.
 
Hi,

For the lower match

=VLOOKUP(95,A1:A6,1,TRUE)

for the higher match the array formula

=MIN(IF(A1:A6>=95,A1:A6))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Both will return an exact match if there is one.



Mike
 
If your list idoes not start on row 1, it can work.

Let's say your list is from A2 - A7.

Let's say your entered value is in cell C2

To get 94.7, it's

=INDEX(A2:A7,MATCH(C2,A2:A7,1))

To get 96.2, its

=INDEX(A2:A7,MATCH(C2,A1:A6,1))

Notice the match is tricked into looking at the prior cell for evaluation...
 
Another set of alternatives with dara in A1:A6 and B1=95:

=LOOKUP(B1,A1:A6)
=LOOKUP(2,1/FREQUENCY(B1,A1:A6),A1:A6)
 
I am wanting to retreive data from an inputted list. What i want to do is
input a number and have the function give me the next highest and lowest
number.
Say for example

90
92.5
93.1
94.7
96.2
98.3

and i enter the number 95. the result should give me the values of 94.7 and
96.2. is this possible to do.
Click to expand...

What do you want if the number entered is exactly equal to one of the numbers
in the list?

What if the number entered is exactly equal to either the highest or lowest
numbers on the list?

This formula will return the same number for the lowest, if the number entered
is exactly equal to one in the list. It will return garbage for "next higher"
if the number entered is exactly equal to the highest number on the list.

There are no checks for number entered being out of range, but this is easily
added.

Next Lower: =OFFSET(ListOfNums,MATCH(Num,ListOfNums)-1,0,1)
Next Higher: =OFFSET(ListOfNums,MATCH(Num,ListOfNums),0,1)

--ron
 
Back
Top