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

On Aug 19, 8:18*pm, "joeu2004" wrote:
"Pete_UK" wrote:
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 -----

On Aug 19, 7:04 pm, cardan wrote:



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.- Hide quoted text -

- Show quoted text -