Finding a Value Between Two Numbers and Returning Corresponding Value

[cardan]

I have a question I was hoping someone could help me with.

I have column of numbers in ascending order, of about 20 or so rows.
The numbers ascend sporadically ie, say column A1:A20 the numbers
start … 1, 5, 13, 20, 25…..
In the next column over (B1:B20), I have other numbers, that are
random (but more or less ascending) 3.5, 6, 7.5, 11.86, 20.76….

In Cell A25, I have an input number that is somewhere between the
numbers in Column A. For example 12. I am trying to write a formula
that will search the range in column A and will recognize the input of
12 is in between the 5 and 13 and then return the value in Column B
that is on the same row of the 13. (ie the max value between the 5 and
13).
I know there is a formula out there and I am trying to avoid VBA and
also trying to avoid an array function-if possible

I hope I have explained this correctly. Please let me know if I need
to clarify. Thank you in advance.

 

[Don Guillett]

I have a question I was hoping someone could help me with.

I have column of numbers in ascending order, of about 20 or so rows.
The numbers ascend sporadically ie, say column A1:A20  the numbers
start … 1, 5, 13, 20, 25…..
In the next column over (B1:B20), I have other numbers, that are
random (but more or less ascending) 3.5, 6, 7.5, 11.86, 20.76….

In Cell A25, I have an input number that is somewhere between the
numbers in Column A. For example 12.  I am trying to write a formula
that will search the range in column A and will recognize the input of
12 is in between the 5 and 13 and then return the value in Column B
that is on the same row of the 13. (ie the max value between the 5 and
13).
I know there is a formula out there and I am trying to avoid VBA and
also trying to avoid an array function-if possible

I hope I have explained this correctly. Please let me know if I need
to clarify. Thank you in advance.

This is a ARRAY formula that must be entered using ctrl+shift+enter vs
just using enter
=VLOOKUP(SMALL(IF($A$2:$A$8>=C1,$A$2:$A$8),ROWS($A$2:A2)),A2:B22,2,0)

 

[Pete_UK]

Try this in B25:

=INDEX(B$1:B$20,MATCH($A25,$A$1:$A$20)+1))

It can be copied across if you have other numbers in column C etc that
you want to return.

Hope this helps.

Pete

 

[joeu2004]

Try this in B25:
=INDEX(B$1:B$20,MATCH($A25,$A$1:$A$20)+1))

I have not read Carl's requirements in detail to know if this is or is not a
problem, but just an observation for consideration....

That formula will result in a #REF error if MATCH returns 20 -- that is, if
A25 is greater than or equal to A20.


----- previous message -----

 

[Pete_UK]

Hi Joe,

I thought about pointing that out, but the OP did say "... I have an
input number that is somewhere between the
numbers in Column A... ", so I took this to mean that the number in
A25 would be less than the number in A20. If not, then it could be
corrected with this:

=IF($A25>=$A$20,B$20,INDEX(B$1:B$20,MATCH($A25,$A$1:$A$20)+1)))

and to be fully bullet-proof if the number might be less than that in
A1:

=IF($A25<$A$1,"too small",IF($A25>=$A$20,"too large",INDEX(B$1:B
$20,MATCH($A25,$A$1:$A$20)+1))))

although the OP could think about using data validation on A25 to
force acceptable numbers.

Hope this helps.

Pete

 

[Don Guillett]

Try this in B25:

=INDEX(B$1:B$20,MATCH($A25,$A$1:$A$20)+1))

It can be copied across if you have other numbers in column C etc that
you want to return.

Hope this helps.

Pete






- Show quoted text -

If you ask for 12 with this formula you will get the higher figure.

 

[Ron Rosenfeld]

I have a question I was hoping someone could help me with.

I have column of numbers in ascending order, of about 20 or so rows.
The numbers ascend sporadically ie, say column A1:A20 the numbers
start … 1, 5, 13, 20, 25…..
In the next column over (B1:B20), I have other numbers, that are
random (but more or less ascending) 3.5, 6, 7.5, 11.86, 20.76….

In Cell A25, I have an input number that is somewhere between the
numbers in Column A. For example 12. I am trying to write a formula
that will search the range in column A and will recognize the input of
12 is in between the 5 and 13 and then return the value in Column B
that is on the same row of the 13. (ie the max value between the 5 and
13).
I know there is a formula out there and I am trying to avoid VBA and
also trying to avoid an array function-if possible

I hope I have explained this correctly. Please let me know if I need
to clarify. Thank you in advance.

The following is an array-formula, to be entered by holding down <CTRL><SHIFT> while hitting <ENTER>:

=INDEX($B$1:$B$20,MATCH(TRUE,$A$25<=$A$1:$A$20,0))

 

[Pete_UK]

Hi Don,

that's what the OP asked for.

Pete

 

[joeu2004]

I thought about pointing that out, but the OP did say
"... I have an input number that is somewhere between
the numbers in Column A... ", so I took this to mean
that the number in A25 would be less than the number
in A20.

Right. So when someone says "pick an integer between 1 and 10", you
honestly believe that only the numbers 2 through 9 are fair game? Yeah,
right.

News flash: English is an ambiguous language. That's why careful people
say "between this and that inclusively" or "between this and that
exclusively".

But most people are not that careful. In my experience, 98 times out of
100, when someone says "between x and y", they mean inclusively. YMMV.

and to be fully bullet-proof if the number might be less than that in A1:
=IF($A25<$A$1,"too small", [....]

The test for A25<A1 should not be necessary because "between" never includes
numbers less than the lower limit and more than the greater limit.

Of course, Carl might want to consider that if he has no control over data
in A25; for example, if he is providing this to others for their use.

But as you say: "although the OP could think about using data validation on
A25 to force acceptable numbers".

That still does not solve the ambiguity of the English word "between".


----- previous message -----

 

[Don Guillett]

Hi Don,

that's what the OP asked for.

Pete



- Show quoted text -

I MEANT (getting old) if the OP asked for 13 they would NOT get 13 but
would get 20

 

[cardan]

I MEANT (getting old) if the OP asked for 13 they would NOT get 13 but
would get 20

Hello All, Thank you for the quick responses. I apologize if I
started a heated debate on symantics and the use of the english
language. I didn't think too much about the exactness of my post. I
kinda of assumed I could add to a given solution with an equal
sign.

What I should clarify is this. For an example, if the input number is
5 and the range in Column A has the numbers, 1, 5, 13, etc... the
formula should return the number in the corresponding range in Column
B. (the number 6 in my original post.). If the input number is greater
than 5 or equal to 13, (keeping with my original example again), then
it should return the number in column B corresponding with the same
row as the 13. I hope I am explaining this correctly.
The Index Match, formula posted by Pete_UK works very well for finding
and returning the maximum, however if my input number is five, it
returns. the number corresponding to the 13. I would like it to
return the number corresponding to the 5. I realize this was due to
my ambuguity in my OP. I apologize again. Is there a way to get the
formula to say if the input number is equal to a number in column A,
return the number in column B in the same row, otherwise, if the input
number is in between two numbers, return the number in column B that
corresponds to the larger number in column A. I hope this is more
clear. Again, Thank you for your time.

 

[Pete_UK]

Bearing in mind the earlier points about choosing numbers outside your
range, you can try this one:

=IF($A25<$A$1,"too small",IF($A25>$A$20,"too
large",IF(ISNA(MATCH($A25,$A$1:$A$20,0)),INDEX(B$1:B$20,MATCH($A25,$A
$1:$A$20)+1),INDEX(B$1:B$20,MATCH($A25,$A$1:$A$20,0)))))

If there is an exact match this will return the corresponding value,
otherwise the next value, as long as the chosen number is within your
range.

Hope this helps.

Pete

 

[Ron Rosenfeld]

Hello All, Thank you for the quick responses. I apologize if I
started a heated debate on symantics and the use of the english
language. I didn't think too much about the exactness of my post. I
kinda of assumed I could add to a given solution with an equal
sign.

What I should clarify is this. For an example, if the input number is
5 and the range in Column A has the numbers, 1, 5, 13, etc... the
formula should return the number in the corresponding range in Column
B. (the number 6 in my original post.). If the input number is greater
than 5 or equal to 13, (keeping with my original example again), then
it should return the number in column B corresponding with the same
row as the 13. I hope I am explaining this correctly.
The Index Match, formula posted by Pete_UK works very well for finding
and returning the maximum, however if my input number is five, it
returns. the number corresponding to the 13. I would like it to
return the number corresponding to the 5. I realize this was due to
my ambuguity in my OP. I apologize again. Is there a way to get the
formula to say if the input number is equal to a number in column A,
return the number in column B in the same row, otherwise, if the input
number is in between two numbers, return the number in column B that
corresponds to the larger number in column A. I hope this is more
clear. Again, Thank you for your time.

The array formula I posted three days ago will do exactly that:

=INDEX($B$1:$B$20,MATCH(TRUE,$A$25<=$A$1:$A$20,0))

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.