Hello:

I have a list of numbers:
10
15
200
300
500
650
775

I have a cell that returns numbers in between 10 adn 775 (example: 214). I
need that number to round to the next higher number in the list (in this
case: 300).

I have tried Rounding in several ways and it hasn't worked. Does this
require an array formula?

Thanks!

With
A1:A7 containing your posted list
and
B1: (a number to match)

If you want exact matches to use the matched item,
otherwise...use the next highest item:
C1: =INDEX(A1:A7,MATCH(1,FREQUENCY(B1,A1:A7),0))

If B1: 200
the formula returns 200

If B1: 214
the formula returns 300

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Huber57" wrote in message
...
Hello:

I have a list of numbers:
10
15
200
300
500
650
775

I have a cell that returns numbers in between 10 adn 775 (example: 214).
I
need that number to round to the next higher number in the list (in this
case: 300).

I have tried Rounding in several ways and it hasn't worked. Does this
require an array formula?

Thanks!

Hi,

With your list in A1 - A7 and your number in b1 try this

=INDEX(A1:A7,MATCH(MIN(IF(A1:A7-B1=0,A1:A7,FALSE)),IF(A1:A7-B1=0,A1:A7,FALSE),0))

Array entered with Ctrl+Shift+Enter

Mike

"Huber57" wrote:

Hello:

I have a list of numbers:
10
15
200
300
500
650
775

I have a cell that returns numbers in between 10 adn 775 (example: 214). I
need that number to round to the next higher number in the list (in this
case: 300).

I have tried Rounding in several ways and it hasn't worked. Does this
require an array formula?

Thanks!

One other thing...

If you want to exclude lookup values that
are below or above the range limits:

Some options, still using your posted example:
C1: =IF(AND(B1=10,B1<=775),INDEX(A1:A7,
MATCH(1,FREQUENCY(B1,A1:A7),0)),"n/a")

C1: =IF(AND(B1=MIN(A1:A7),B1<=MAX(A1:A7)),INDEX(A1:A7 ,
MATCH(1,FREQUENCY(B1,A1:A7),0)),"n/a")


--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"Ron Coderre" wrote in message
...
With
A1:A7 containing your posted list
and
B1: (a number to match)

If you want exact matches to use the matched item,
otherwise...use the next highest item:
C1: =INDEX(A1:A7,MATCH(1,FREQUENCY(B1,A1:A7),0))

If B1: 200
the formula returns 200

If B1: 214
the formula returns 300

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Huber57" wrote in message
...
Hello:

I have a list of numbers:
10
15
200
300
500
650
775

I have a cell that returns numbers in between 10 adn 775 (example: 214).
I
need that number to round to the next higher number in the list (in this
case: 300).

I have tried Rounding in several ways and it hasn't worked. Does this
require an array formula?

Thanks!

Mike and Ron,

Thanks to both!!

I appreciate it.


"Ron Coderre" wrote:

With
A1:A7 containing your posted list
and
B1: (a number to match)

If you want exact matches to use the matched item,
otherwise...use the next highest item:
C1: =INDEX(A1:A7,MATCH(1,FREQUENCY(B1,A1:A7),0))

If B1: 200
the formula returns 200

If B1: 214
the formula returns 300

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Huber57" wrote in message
...
Hello:

I have a list of numbers:
10
15
200
300
500
650
775

I have a cell that returns numbers in between 10 adn 775 (example: 214).
I
need that number to round to the next higher number in the list (in this
case: 300).

I have tried Rounding in several ways and it hasn't worked. Does this
require an array formula?

Thanks!