Searching for low and high number

[Mortir]

I have the following example

A column with number of days:

days
3
7
31
61
91
181
271
365

Now i have a certain formula which returns to me a number of days -
for example 21. 21 belongs in between 7 and 31 in the days column. I
am looking for a formulas that would return this high and low number
of the interval in which my number belongs to:

- for 21, the solution should be 7 and 32,
- for 183, the solution should be 181 and 271
- and if the number equals one of the numbers in the column the
solution should be the exact number> if i have 31 the solution should
be 31

is there a way to do this. thanx for your help!

 

[Mortir]

sorry made a slight mistake above:
- for 21, the solution should be 7 and 31 not 32

 

[Mike H]

Hi,

I assume if there is an exact match you want that. with your lookup value in
D1

For the lower number

=LOOKUP(D1,A1:A8)

For the higher

=MIN(IF(A1:A8>=D1,A1:A8))

The second formula is an array.
'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

Mike

 

[John]

Hi Mortir
Did you try MAX and MIN eg: =MAX(A2:A3) same for MIN.
Regards
John

 

[Lars-Åke Aspelin]

I have the following example

A column with number of days:

days
3
7
31
61
91
181
271
365

Now i have a certain formula which returns to me a number of days -
for example 21. 21 belongs in between 7 and 31 in the days column. I
am looking for a formulas that would return this high and low number
of the interval in which my number belongs to:

- for 21, the solution should be 7 and 32,
- for 183, the solution should be 181 and 271
- and if the number equals one of the numbers in the column the
solution should be the exact number> if i have 31 the solution should
be 31

is there a way to do this. thanx for your help!

If your table of days are in A2:A9 and your number is in B1 you may
try this formula (all in one line):

=IF(ISERROR(VLOOKUP(B1,A2:A9,1,FALSE)),VLOOKUP(B1,A2:A9,1,TRUE)&" and
"&INDEX(A2:A9,MATCH(B1,A2:A9,1)+1),B1)

Change the range A2:A9 to fit your days table.

Hope this helps / Lars-Åke

 

[muddan madhu]

for low number
=INDEX(A1:A8,MATCH(B1,A1:A8,1),0)

for high number
=INDEX(A1:A8,MATCH(B1,A1:A8,1)+1,0)

 

[Mortir]

for low number
=INDEX(A1:A8,MATCH(B1,A1:A8,1),0)

for high number
=INDEX(A1:A8,MATCH(B1,A1:A8,1)+1,0)

tnx guys. realy appreciate your help!!!!

 

[Shane Devenshire]

Hi,

It would be nice to use the BETWEEN function, ha! but there isn't one in
Excel.

You can make a minute simplification to the previous suggestion
=MIN(IF(A1:A8>D1,A1:A8))
since by definition you don't want the min and max to be the same. This
assumes that your first formula is in D1

Of course these suggestions assume that the data is in Ascending order and
my simplification assumes that there are no duplicates.

If the numbers are not in order:
Use the following array to get the lower number and
=MAX(IF(A1:A8<=C1,A1:A8))
and
=MIN(IF(A1:A8>D1,A1:A8))
to get the upper number

These two formulas assume the number you want to check for is in C1 and the
first formula is entered in D1

These must be entered by pressing Shift+Ctrl+Enter

 

[Shane Devenshire]

Hi,

Take out my = sign in
=MAX(IF(A1:A8<=C1,A1:A8))

 

[Shane Devenshire]

And one more optional change, instead of reference the first formula in the
second one you could just use

=MIN(IF(A1:A8>C1,A1:A8))

Where C1 is the number you are comparing to.

 

[Mike H]

Shane

since by definition you don't want the min and max to be the same.

By definition that's precisely what the OP wanted

and if the number equals one of the numbers in the column the
solution should be the exact number> if i have 31 the solution should
be 31

Mike

 

[T. Valko]

for high number

=INDEX(A1:A8,MATCH(B1,A1:A8,1)+1,0)

If the lookup_value *is* the high number that will return an error.


--
Biff
Microsoft Excel MVP


for low number
=INDEX(A1:A8,MATCH(B1,A1:A8,1),0)

for high number
=INDEX(A1:A8,MATCH(B1,A1:A8,1)+1,0)

 

[Shane Devenshire]

Absolutely, I just wanted to make this point which might be useful if others
are looking for a slightly different answer or the OP misstated the goal.
For example, I don't believe the OP stated that the numbers had to be in
ascending order nor that there were no duplicates, nor how to handle
duplicated if they occured.

 

[Mike H]

Shane,

Ah I see, you mean a formula like mine which copes with duplicates and
random sorted numbers.

Mike