How do I pick a number from a list in Excel?
 

I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.

Sort the data descending, then assuming the test value is in B1, use

=MATCH(B1+1,A:A,-1)

--
HTH

-------

Bob Phillips
"pugsly8422" wrote in message
...
I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that
is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.

One way,

with the numbers in A1:A10 and the inout cell in B1

=INDEX(A1:A10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10,"< ="&B1)+1),A1:A10,0))

Regards,

Peo Sjoblom

"pugsly8422" wrote:

I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.

Pugsly,

If your data is not always going to be integers then try:

=MIN(IF(A1:A99B1,A1:A99))

This is an array formula so enter ir with Control + Shift + Enter instead of
just Enter

HTH

Sandy
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"pugsly8422" wrote in message
...
I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that
is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.

Thank you for your help, this is exactly what I needed.

Thanks also for everyone else's help as well, I really appreciate your help
and time.


"Peo Sjoblom" wrote:

One way,

with the numbers in A1:A10 and the inout cell in B1

=INDEX(A1:A10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10,"< ="&B1)+1),A1:A10,0))

Regards,

Peo Sjoblom

"pugsly8422" wrote:

I have a list of 99 different numbers. In one cell a person can type in a
number, and after they do that I want it to pick the smallest number that is
larger than the one they listed. For example:

The list is 1,2,3,4,5

They enter 3

I want it to pick 4 since that is the smallest number that is larger than
the one they entered.

Thanks for any assistance.